April 15, 2015 at 9:37 am
Hello,
I'm SQL server developer with experience in OLTP database design and development. Now I've been tasked with datamart design. I need some kind of guideline on where to start? How to gather and document requirements, how to design start-schema database etc.
Thanks in advance.
April 16, 2015 at 2:25 am
I recommend getting hold of a couple of books: Star Schema The Complete Reference by Christopher Adamson and The Microsoft Data Warehouse Toolkit by Joy Munday and Warren Thornthwaite.
Before you start doing any design you need to engage with the business and gather requirements. I also suggest that you need a team of people - very hard to do it all by yourself
Regards
Lempster
April 16, 2015 at 3:06 am
In addition to Lempster's suggestions, I'd also recommend Ralph Kimball's original paper http://www.kimballgroup.com/1997/08/a-dimensional-modeling-manifesto/ an excellent introduction to the star schema concept. There's a huge amount of information on the subject in various areas of that website (plus some possible overkill - I believe there are now enough types of Slowly Changing Dimension to overflow an INT).
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 16, 2015 at 8:49 am
Thank you Lempster and Andrew. Really appreciate your help.
I'll start with the books you suggested. I was also looking at the book "The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling". Is this a good one as well? I'll also go through the Kimball's papers.
We do have a requirements analyst but the problem is still the same that he never gathered requirements for datamart and he is talking with Marketing/business analyst person. What other roles would you suggest to begin with?
April 17, 2015 at 2:41 am
mayurkb (4/16/2015)
What other roles would you suggest to begin with?
If you read the second book I listed, it talks about 20 different roles, which to my mind is overkill (and rarely will one find a company where all those roles exist). My personal opinion is that the following roles are key:
Project Manager - responsible for day-to-day management of the project and able to call on other resources as and when required.
Business Sponsor / Business Lead - someone from the business community who works closely with the Project Manager. Business buy-in is crucial for a successful outcome when delivering a Data Warehouse project.
Business Analyst (BA) - gathers Business requirements and helps to translate them to technical requirements. Is the link between the Business and IT.
BI Developer - develops the dimensional model and the ETL processes (scripts, SSIS packages).
DBA - responsible for the physical implementation of the logival design and also for ongoing maintenance and performance tuning. Works closely with the BI Developer. In small teams, the roles of DBA and BI Developer are often performed by the same person.
Regards
Lempster
April 17, 2015 at 5:53 am
Business buy-in is crucial for a successful outcome when delivering a Data Warehouse project.
^ THIS. Every step and everything you do, you and the users should be able to answer the question "What Business Question/Need Are We Addressing?" straight away. And with outcomes, not buzzwords. Make sure you're talking with the users throughout the process.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 17, 2015 at 6:58 am
Thank you again and understood.
Does everything that apply to data warehouse also apply to datamart? We are not really IT and not making a datamart for my company. We make healthcare software and want to develop and ship a datamart for the suite of products that we develop.
April 21, 2015 at 10:37 am
mayurkb (4/17/2015)
Thank you again and understood.Does everything that apply to data warehouse also apply to datamart? We are not really IT and not making a datamart for my company. We make healthcare software and want to develop and ship a datamart for the suite of products that we develop.
I'm not sure I follow what it means to "ship" a datamart. Perhaps you simply mean that your software package needs a portable database to contain reference data or from which the client will perform analytical reporting.
The design of any database, especially a datamart or data warehouse, should be purpose driven. First determine what the end user needs to get out of it, which then leads to a determination of what needs to go into it, which then leads to a specific conceptual and technical implementation. A datamart could be relational database, an OLAP cube, or even an Excel sheet.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply