Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Where to put a Type 2 SCD? Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:06 PM
Points: 193, Visits: 514
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?
Post #1347953
Posted Tuesday, August 21, 2012 9:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:23 AM
Points: 1,135, Visits: 1,149
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
Post #1348158
Posted Tuesday, August 21, 2012 10:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 6:06 PM
Points: 286, Visits: 570
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?
Post #1348179
Posted Wednesday, August 22, 2012 9:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1348481
Posted Thursday, August 23, 2012 2:46 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:28 AM
Points: 788, Visits: 1,915
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.



Post #1349358
Posted Thursday, August 23, 2012 3:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:06 PM
Points: 193, Visits: 514
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.
Post #1349378
Posted Wednesday, August 29, 2012 1:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:46 AM
Points: 132, Visits: 556
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.
Post #1351879
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse