SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help needed for creating the star schema


Help needed for creating the star schema

Author
Message
sram24_mca
sram24_mca
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 Visits: 508
Hi,
We are planning to data warehouse the 2 different projects , to implement this we have to create the star schema from the 2 project database. Can anyone help us which approach is best to achieve this goal?

Regards.
Ram
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7942 Visits: 2629
Check these out
http://www.amazon.com/The-Data-Warehouse-Toolkit-ebook/dp/B006BBLM0Y/ref=sr_1_2?ie=UTF8&qid=1370454024&sr=8-2&keywords=kimball+dimensional+model

http://www.amazon.com/Star-Schema-Complete-Reference-ebook/dp/B003Y8YWAE/ref=sr_1_1?ie=UTF8&qid=1370454024&sr=8-1&keywords=kimball+dimensional+model

http://www.amazon.com/Agile-Data-Warehouse-Design-Collaborative/dp/0956817203/ref=sr_1_4?ie=UTF8&qid=1370454024&sr=8-4&keywords=kimball+dimensional+model
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14069 Visits: 4639
sram24_mca (6/5/2013)
We are planning to data warehouse the 2 different projects , to implement this we have to create the star schema from the 2 project database. Can anyone help us which approach is best to achieve this goal?


I assume that "two different projects" mean "two different data domains" therefore your dataware house would look like two datamarts, one for each data domain.

Absolutely impossible for us to model your star schemas but, you should be able to identify your FACTtual and DIMensional tables for each datamart, also which DIMensions can be shared by both like the ever present DIM_DATE dimension table.

Hope this helps.

_____________________________________
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.
Barry McConnell
Barry McConnell
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 294
Unless you are stuck doing a star schema might I suggest you look at Data Vault as a better design for a data warehouse? http://www.amazon.com/Modeling-Agile-Warehouse-Vault-Volume/dp/061572308X

There are no facts, only interpretations.
Friedrich Nietzsche
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14069 Visits: 4639
barry.mcconnell (6/11/2013)
Unless you are stuck doing a star schema might I suggest you look at Data Vault as a better design for a data warehouse? http://www.amazon.com/Modeling-Agile-Warehouse-Vault-Volume/dp/061572308X


I do not want to start a war here but even if Data Vault is kind of interesting, to me, Data Vault manages to put together a mix of Dimensional (Kimball) and Relational (Inmon) approaches for Data Warehousing that make the resulting schema less than elegant and a nightmare to query.

In short, I would encourage poster to approach the issue at hand armed with a standard modeling methodology, a proven one, like Dimensional modeling.

_____________________________________
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.
Barry McConnell
Barry McConnell
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 294
Data vault is not a mix of the two models but a formal methodology in its own right that has more than a decade of design and testing behind it. The link I provided is one of the better explanations for anyone interested in learning more. There really is a reason Bill Inmon calls it the best model for designing a data warehouse.

There are no facts, only interpretations.
Friedrich Nietzsche
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14069 Visits: 4639
barry.mcconnell (6/11/2013)
Data vault is not a mix of the two models but a formal methodology in its own right that has more than a decade of design and testing behind it. The link I provided is one of the better explanations for anyone interested in learning more. There really is a reason Bill Inmon calls it the best model for designing a data warehouse.


Okay, I knew this was going to happen.

Firstly, when writting a quote it is good manners to write the complete quote which reads "The Data Vault is the optimal choice for modeling the EDW in the DW 2.0 framework.”

Secondly, "DW 2.0 framework" is a book authored by three different people, including Inmon - nobody by them can tell who actually come out with such statement.

Last but not least, it means exactly what it means... one of the authors of DW 2.0 framework thinks that Data Vault is the optimal choice for their particular vision of EDW.

Out of topic. Is it a fair statement to say that most Data Vault EDW's end up with a dimensional modeled set of datamarts on top of it? Yes, innit?

_____________________________________
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.
Barry McConnell
Barry McConnell
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 294


Okay, I knew this was going to happen.

Firstly, when writting a quote it is good manners to write the complete quote which reads "The Data Vault is the optimal choice for modeling the EDW in the DW 2.0 framework.”

Secondly, "DW 2.0 framework" is a book authored by three different people, including Inmon - nobody by them can tell who actually come out with such statement.

Last but not least, it means exactly what it means... one of the authors of DW 2.0 framework thinks that Data Vault is the optimal choice for their particular vision of EDW.

Out of topic. Is it a fair statement to say that most Data Vault EDW's end up with a dimensional modeled set of datamarts on top of it? Yes, innit?


The only thing that "happened" is I disagreed with your characterization of Data Vault as something less than Kimball's approach. There are well known problems with Ralph's methods (as well as Bill's). The Data Vault was designed specifically as a methodology for data warehouses and has a sound theoretical foundation as well as practical application. Clearly you are not well versed in the methodology so I provided a good reference.

Had I meant to quote someone I would have. I was summarizing what I know to be Bill's position based on not only that one book but his multiple other writings and conversations on the topic.

Your final point is actually not relevant. Data marts based on dimensional modeling are certainly a good reporting choice for performance reasons, though certainly not the only choice. However, data marts do not a data warehouse make, regardless of religious fervor.

My only point to the original poster was that there are more options out there than slavish devotion to one style of modeling. It is certainly possible to pound in a screw with a wrench, but that doesn't make it good engineering.

There are no facts, only interpretations.
Friedrich Nietzsche
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14069 Visits: 4639
Doze Barry, moving the discusion to a personal level, making assumptions about what other people may know or not and putting on other people's mouth words they have never say... it is not a good way to argue a case.
That is the point where I say "have a good day" and, just walk away.
Have a good day.

_____________________________________
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search