Capturing the Reason For Change for Type 2 changes

Question

Capturing the Reason For Change for Type 2 changes

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

EricEyster (3/3/2014)
... but the Kimball design assumes your users are getting data from the relational engine.

I disagree with that statement. Sure, the relational data warehouse containing the dimension and fact tables is what the multidimensonal Measures and Dimensions (i.e. Cubes)are built on, but users query the data stored in Cubes directly from SSAS whether that be via Excel (pivot tables), PowerView/PowerPivot, MDX queries, SSRS or third party products.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 2

EricEyster (3/3/2014)
--------------------------------------------------------------------------------
... but the Kimball design assumes your users are getting data from the relational engine.
I disagree with that statement. Sure, the relational data warehouse containing the dimension and fact tables is what the multidimensonal Measures and Dimensions (i.e. Cubes)are built on, but users query the data stored in Cubes directly from SSAS whether that be via Excel (pivot tables), PowerView/PowerPivot, MDX queries, SSRS or third party products.

I agree with the disagreement. The statement is misleading because the USERS aren't getting the data from the relational engine. SSAS is getting data from the relational engine when it processes. The users then get it from SSAS through some front end.

sneumersky SSCertifiable Points: 7667 More actions · Answer 3

Whatever you folks do, don't forget to set the correct attribute relationship types (rigid for type 2 dimension columns and flexible for type 1 and type 3 columns). 😀

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Lempster (3/3/2014)
Jeff Moden (2/28/2014)
Put an index on the "Current-Flag" and watch your GUI's timeout when they try to do an INSERT because of the massive extent splits that will occur. 😉 I recommend just doing the dates correctly.
I'm talking about a Data Warehouse so there aren't going to be any GUIs to timeout, certainly not any doing inserts. There will of course be inserts on a daily basis due to the ETL process. The relational tables in the Data Warehouse will have multidimensional cubes built on them and it will be the cubes that are queried by end users, not the relational tables directly.
I will of course undertake extensive testing, but at this point I'm inclined to follow Kimball best practice.
Regards
Lempster

My incliniation is that it doesn't matter where the inserts are coming from. A leading or singular column in an index with such low selectivity is going to cause massive extent splitting that will slow any process down.

Although I'm also inclined to go with what experts say, it does cause me concern when the best practices of one expert or group of experts is contrary to the best practices of another. Testing would be a good thing here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sneumersky SSCertifiable Points: 7667 More actions · Answer 5

Put an index on the "Current-Flag" and watch your GUI's timeout when they try to do an INSERT because of the massive extent splits that will occur.

For some reason, I am drawing a massive blank about why this would happen (no coffee) . Is this because it is a non clustered index on a low cardinality column?

aaron.reese SSChampion Points: 13498 More actions · Answer 6

@ronkyle,

For the standard western collations, NULL appears at the top of the sort values so when you end date the record it will change it's place in the index with the consequential overhead of re-paging and page splits/sparsely populated index pages. If you put a default date of the end of all time, when you update it, its place in the index is not changed

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 7

@Aaron.reese

I will have to reconsider how I do that. Thanks for the information.