Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help needed for creating the star schema Expand / Collapse
Author
Message
Posted Wednesday, June 5, 2013 2:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:49 PM
Points: 90, Visits: 388
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
Post #1460093
Posted Wednesday, June 5, 2013 11:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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
Post #1460353
Posted Monday, June 10, 2013 8:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1461591
Posted Tuesday, June 11, 2013 7:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:05 AM
Points: 92, Visits: 64
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
Post #1462075
Posted Tuesday, June 11, 2013 3:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1462402
Posted Tuesday, June 11, 2013 4:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:05 AM
Points: 92, Visits: 64
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.
Post #1462410
Posted Wednesday, June 12, 2013 7:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1462613
Posted Wednesday, June 12, 2013 7:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:05 AM
Points: 92, Visits: 64


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.
Post #1462633
Posted Wednesday, June 12, 2013 8:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1462653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse