Results 1 to 5 of 5

Thread: database for data warehouse

  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Question database for data warehouse

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  3. #3
    Join Date
    Dec 2004
    Posts
    2
    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

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  5. #5
    Join Date
    Dec 2003
    Posts
    140
    Originally posted by MAK
    ....
    Very few companies have a staging database (ETLed) database and then a data warehouse.
    Hi MAK,

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •