How to implement incremental load in fact tables.

Hi all,

i have a fact table which loads through package,when

i m trying to load this table by running the package,i m truncating the

fact table and loading the fresh data,instaed of this without

truncating the fact table i have to implement the incremental logic in

this.

For this i can use SCD or Conditional split,but problem

here is i have many source tables to load this fact table,so its

very difficult to trace the changes in different source tables.

can any one help me out in this?You should stage all of your source tables along with the current load date. That way, when you select from your source staging tables, you can retrieve the data based on that date field, thereby only picking up the new/changed records.

How to ignore a dimension in MDX?

So my fact table looks like this:

SurveyID ( --> Survey Dimesion)
GroupID( --> Group Dimension)
StatusID ( --> Status Dimension ... e.g. Complete, InProgress, New)
GroupCount (the actual measure)

How would I create a calculated member that would return the "overall" count for a specific group in a survey (e.g. ignore Status dimension) regardless if the user is slicing the data by Status or not?

Thanks again - wgpubs

Try this ("ignoring" the status dimension - your exact naming may differ):

CREATE MEMBER CURRENTCUBE.MyCount AS

'([Status].[StatusID].[All StatusID],[Measures].[GroupCount])';

|||tried this ... but if I include the ResponseStatus dimension in my slicer it still filters on ResponseStatus.

In my slicer I got survey, group and response status ... the kind of report I'm trying to generate in SSRS needs to looks something like this:

Survey Group Count OverallCount

... where Count should use the ResponseStatus dimension (so that users can filter out certain statuses and what not thru a RS parameter) while OverallCount should not.|||My suggestion should work, if I understand you correctly. Please post your exact MDX query.|||Yah I thought it would too ... but no go. here is the query:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Overall Group Count]
AS ([Response Status].[Response Status].[All], [Measures].[Group Count]),
VISIBLE = 1 ;

How to Identify an Array

I have seen several examples explaining the fact that a table
containing a field for each day of the week is for the most part an
array. An specific example is where data representing worked hours is
stored in a table.

CREATE TABLE [hoursWorked] (
[id] [int] NOT NULL ,
[location_id] [tinyint] NOT NULL,
[sunday] [int] NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL
)

I had to work with a table with a similar structure about 7 years ago
and I remember that writing code against the table was pretty close to
Hell on earth.

I am now looking at a table that is similar in nature - but different.

CREATE TABLE [blah] (
[concat_1_id] [int] NOT NULL ,
[concat_2_id] [int] NOT NULL ,
[code_1] [varchar] (30) NOT NULL ,
[code_2] [varchar] (20) NULL ,
[code_3] [varchar] (20) NULL ,
[some_flg] [char] (1) NOT NULL
) ON [PRIMARY]

The value for code_2 and code_3 will be dependently null and they will
represent similar data in both records (i.e. the value "abc" can exist
in both fields) . For example if code_2 contains data then code_3 will
probably not contain data.

I do not think that this is an array. But with so many rows where
code_2 and code_3 will be NULL something just does not feel right.

I will appreciate your input.rm wrote:

Quote:

Originally Posted by

I have seen several examples explaining the fact that a table
containing a field for each day of the week is for the most part an
array. An specific example is where data representing worked hours is
stored in a table.
>
CREATE TABLE [hoursWorked] (
[id] [int] NOT NULL ,
[location_id] [tinyint] NOT NULL,
[sunday] [int] NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL
)
>
I had to work with a table with a similar structure about 7 years ago
and I remember that writing code against the table was pretty close to
Hell on earth.
>
I am now looking at a table that is similar in nature - but different.
>
CREATE TABLE [blah] (
[concat_1_id] [int] NOT NULL ,
[concat_2_id] [int] NOT NULL ,
[code_1] [varchar] (30) NOT NULL ,
[code_2] [varchar] (20) NULL ,
[code_3] [varchar] (20) NULL ,
[some_flg] [char] (1) NOT NULL
) ON [PRIMARY]
>
The value for code_2 and code_3 will be dependently null and they will
represent similar data in both records (i.e. the value "abc" can exist
in both fields) . For example if code_2 contains data then code_3 will
probably not contain data.
>
I do not think that this is an array. But with so many rows where
code_2 and code_3 will be NULL something just does not feel right.
>
I will appreciate your input.

A table is not an array. It is a relation. Unlike arrays, relations are
not addressable by an index structure but only by the values they
contain. A relation with N attributes is N-dimensional but that doesn't
make it an N-dimensional array.

I have only your column names to go on. Your HoursWorked structure is
surely very impractical, not least because of the difficulty of
aggregating data across multiple days. The second case is trickier to
interpret. At the very least it seems probable that it isn't normalized
appropriately because of what you have said about the dependencies.
Think Fifth Normal Form and satisfy yourself about the appropriateness
of the design. Design by newsgroup is really not much more than
guesswork.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--