Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts

Thursday, March 22, 2012

Delay Insert

I am parsing a file where along the flow I use a conditional split. One path of the split is the primary table (with IDENTITY) values. The rest of the paths have a FOREIGN KEY to the primary table.

It seems that SSIS is trying to insert the rows at the same time (which makes sense) but this is causing a problem with the secondary tables and their FK constraint since the primary table is not yet written.

Is there a way to delay the secondary tables until the primary table is done?

(I guess one way is to run through the file twice... once for the primary table and another for the rest but that seems wasteful to me...)

Thanks.

There is no way to delay paths inside a data flow, or set any precedence. One option I like is to stage the "secondary" data in a raw file. This is very efficient compared to most source and destination combinations. In your current Data Flow write the secondary data to a raw file then add another Data Flow task, with a raw file source and the your final destination.

Saturday, February 25, 2012

DefaultMember of Time Dimension (Analysis Services 2005)

I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.

I have the default member of the Date ID attribute set to:

Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)

This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)

If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.

I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.

has anyone else experienced this? am I missing something?

Thanks,

Jason

Hi jsaido,

The behaviour of your solution is right, but unfortunatly is not right for you.

It's named autoexist.

Why you don't use Named sets?

For example:

CREATE SET CURRENTCUBE.[Current Date]

AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);

CREATE SET CURRENTCUBE.[Current Montht]

AS EXISTS([Date].[Month].[Month].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Quarter]

AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Year]

AS EXISTS([Date].[Year].[Year].members, [Current Date]);

|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.

DefaultMember of Time Dimension (Analysis Services 2005)

I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.

I have the default member of the Date ID attribute set to:

Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)

This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)

If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.

I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.

has anyone else experienced this? am I missing something?

Thanks,

Jason

Hi jsaido,

The behaviour of your solution is right, but unfortunatly is not right for you.

It's named autoexist.

Why you don't use Named sets?

For example:

CREATE SET CURRENTCUBE.[Current Date]

AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);

CREATE SET CURRENTCUBE.[Current Montht]

AS EXISTS([Date].[Month].[Month].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Quarter]

AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Year]

AS EXISTS([Date].[Year].[Year].members, [Current Date]);

|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.