Printable View
I am going to use Microsoft SQL Server to develop my data warehouse, but one thing makes me confused. Since Analysis Service can create a Star schema database, do I have to pre-set up a Star schema database for ETLed data? Basically, I am wondering what's the relationship between an ETLed database and the one created through the Analysis Services.
Can any one give me an explanation from the implementation perspective?
Many thanks!
ETLed database is Data Warehouse. So you ETLed database should be designed in such a way that it can be star schema.
Analysis service make use of the data warehouse and build cubes.
Very few companies have a staging database (ETLed) database and then a data warehouse.
Mak, thanks for the explanation.
To further clear my confusion, I would like to ask one more question:
If I only use Analysis Services for OLAP, since the tool can build up a cube around a start schema, do I have to create my data warehouse database under a start schema? Say, the data warehouse just contains a sort of de-normanized database, but it's up to the user of AS tool to create his own start schema. Is this acceptable?
Thanks again
Yes. your datawarehouse should be denormalized. [FACT Table and dimension tables]. Try to create star schema as much as possible. If you have snow flake schema, analysis service needs to join all the tables in order to create cube.
Depending on the type of OLAP (MOLAP,ROLAP,HOLAP) you choose analysis service create cubes either as physical file, database tables or both.
Hi MAK,Quote:
Originally posted by MAK
....
Very few companies have a staging database (ETLed) database and then a data warehouse.
Came across your quoted reply and wanted to request if based on your experience you'd kindly share why thisis the case. Though I am in the early stages and am learning but I have a FEELING that i too fall under this category i.e. not having a separate staging area (database). Maybe this is becuase my concepts are not clear. I just thought that what staging area is all about is that you bring in all the data from your OLTP (or whatever) into a separate database, then do the cleaning up etc. and creating views in this data base, and then use the results of this database (which I think is the staging area) for the next step i.e. creating the dimensions and the cubes....
A related question is that what I am doing is that I have defined nice andcleanviews in my OLTP which provide me with the required data. I just bring the data into my database (which I have beenreferring to as my datamart ratherthan a staging area) andam doingthe next steps from there...
Assuming that my questions are clear, can you kindly comment on my approach and give me a feeler regarding if i need to take a different route or clear up my understanding....
Will really appreciate your reply and happy holidays to everyone.
Regards.