Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Where to put a Type 2 SCD?


Where to put a Type 2 SCD?

Author
Message
imani_technology
imani_technology
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 777
This is actually a follow up to an earlier question about a Type 2 SCD.

Anyway, we have decided on the Type 2 SCD for the suggested sales price. Now for the next question: where to put it? Here are the options:
1. The MS Dynamics AX transactional system where we currently hold our sales prices.
2. A SQL Server relational "interim" DB that is organized into a star schema that will "feed" an SSAS cube.
3. The SSAS database.

I am considering option #3, but other members of the team think option #1 is more prudent. Which way to go?
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1332 Visits: 1399
I'd say your dimension goes in your data warehouse/data mart. I'm not sure from your description if that is #2 or #3.

--

#1) I'm assuming that your transactional system (AX) has it's data stored in a normalized (3NF) database with some type of history records showing the time frames/spans for then the suggested price applies. This would feed your dimension, but I wouldn't put it here. Why would you put a denormalized dimension in your transaction system?

#2) By "interim" database, do you mean this is your staging database? If so, the data from the source system would pass through here, but not your ultimate destination for the dimension.

#3) If this is your star schema dw/datamart that your SSAS cubes data source views point to to build your dimension, then this is where I'd put the dimension.

HTH,
Rob
MissTippsInOz
MissTippsInOz
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 597
In basic terms a star-schema refers to the arrangement of multiple dimensions around a central fact. So, when talking about where to locate the dimension, the natural place for this to go would be in your #2 'interim' database which is where your star-schema exists. The 'slowly-changing' element of this dimension will occur as you incrementally load your warehouse (probably #2 in your environment).

The 'SSAS database' is actually your cube which will have been built from the data in your #2 and will rely on the 'SalesPrice' dimension already existing.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3097 Visits: 4639
imani_technology (8/21/2012)
...we have decided on the Type 2 SCD for the suggested sales price. Now for the next question: where to put it?


How about in your DIMENSION table? in your star-schema data warehouse?

'member, SCD stands for "Slowly Changing DIMENSION" :-)

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
RonKyle
RonKyle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1292 Visits: 3344
How about in your DIMENSION table? in your star-schema data warehouse?


Agree completely. If you're not sure where to put it, you need to read up on warehouse design. Since you're using SSAS, I recommend Ralph Kimball. It's important to get this right, because if you don't and the data is not useable, you will have wasted a lot of time.



imani_technology
imani_technology
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 777
I agree about putting the data in dimension table. The problem was in convincing co-workers. For some reason, someone wanted the data in the Dynamics AX system.
richykong
richykong
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 621
From what I can remember, Dynamics AX is not made to support type 2 dimensional data. For a proper SCD Type 2 dimensions, you need to maintain something like a StartDate, EndDate with a unique SurrogateKey and some kind of business key that allows you to tie two or more records in the dimension together.

You could theoretically build this functionality in AX and use some X++ to make sure the table behaves like a SCD Type 2, but I can't see much value of having the OLTP system maintain all history of changes purely to support building a cube directly on top of the OLTP tables.

The argument I've heard most from people about building everything in AX was mainly due to their lack of understanding of the difference between an OLTP and OLAP system. Another fighting piece for AX was around how it is a Microsoft product and it has pre-built functionality so it's cheaper to implement (no separate data warehouse development). This holds merit if you are building a simple cube off of a relatively small system. If you have large sales/orders/items tables and have complex dimensions that require change tracking, even MS will tell you you're probably better off creating a custom data warehouse.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search