imani_technology (8/21/2012)
I have to modify the design of a data mart I "inherited." Currently the big picture is this:ETL Staging DB (EDI, Excel, and miscellaneous text files are loaded here) -> Second Staging DB (incorporates ETL Staging DB and some MS Dynamics AX data) -> SSAS Cube
I realize SSAS 2008 R2 (the version we use) does not require a relational star-schema source. However, wouldn't it be a "best practice" to replace that second staging DB (which does not use a star schema) with an interim DB that does use a star schema?
In my mind there are several layers on a DSS, they are:
1- The OLTP source system(s) <= The systems that support the operation of the business.
2- The Staging area <= Where you put whatever you Extract from OLTP and eventually Transform it.
3- The Datamart <= A dimensional (star-schema) structure where you Load the data sourced from OLTP
4- The Delivery layer <= Structure from where you deliver data to the business.
Cubes are a good example of a Delivery Layer but in some cases you will find user queries hitting the Datamart tables directy - in which case you have either a mix or just datamart based reporting.
In this particular case, adding a star-schema datamart in the middle of your already working system sounds like a project on itsefl - be sure you don't shoot your own foot in the process of making the system structure "cuter".
Any particular reason to do it? bad performance or so? How you planning to sell the project to the business?
_____________________________________
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.