August 21, 2012 at 11:00 am
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?
August 21, 2012 at 9:23 pm
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
August 21, 2012 at 10:48 pm
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.
August 22, 2012 at 9:04 am
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.August 23, 2012 at 2:46 pm
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.
August 23, 2012 at 3:47 pm
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.
August 29, 2012 at 1:26 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply