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


Data Warehouse Development: Version 0


Data Warehouse Development: Version 0

Author
Message
Andy Leonard
Andy Leonard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 1095
Comments posted to this topic are about the item Data Warehouse Development: Version 0

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Chris Houghton
Chris Houghton
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 1697
An interesting article for a Data Warehouse newb. I'm very much looking forward to reading the follow up articles in this series. Thanks. :-)
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 3232
Andy,

An intersting article and of great interest having being a DW developer for the last 8/9 years, and I would always describe version 0 as a Proof of Concept that shows the CxO's what they can get if they invest in the development of a DW.

However in my experience most CxO's dont really know what a data warehouse is, let alone what they want from one, execpt the obligatory 'every thing we currently have, and more'. They also have a tendancy to think that there is nothing wrong with thier data, and so when you produce a single report that doesnt match expectations, its your problem.

Even when you explain that the data is correct, and its different because Annie in accounting manually combines Figures X and Y in a spread sheet so that it looks better.

Often the biggest issue I come across is that there is a disconnect between the requirements of actual users and the CxO's and other senior managers. Who are generally only interested in pretty graphs, and the visualisation part, were as those lower down the chain (departmental heads, analysts etc) are interested in being able to dive around the data and look at the anomolies.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Gopi S
Gopi S
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 325
Thanks for posting this article. I am new to DW project and in learning stage. I think the series of this article takes me in the right path.

Please suggest if any of you have posted anyother DW article to start with.
Martin Mason
Martin Mason
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 114
Great article. But I'd take it one step further. While automating a manually produced report that takes days if not weeks to produce might be the ultimate goal, a report could consist of information captured in multiple business processes from multiple systems. The danger of letting a report or a small set of reports guide your requirements is that you run the risk of creating a series of spreadsheet marts that each contain data that cannot be easily integrated. While gathering requirements, data sources need to be classified into two categories, business actor (dimensions) or business process (facts). And the Kimball data bus matrix (or SSAS cube editor Dimension Usage tab) needs to be the final product of the requirement gathering stage. From those results, the data warehouse can be constructed iteratively and incrementally, one source, one business actor, one business process at a time. And as the business changes, so will the data bus matrix.
jmonk 57451
jmonk 57451
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 70
This is an excellent example of pressing the right buttons. You bring up a fantastic point about the 'environment' of relatioinships and getting to know WHO and WHERE to start. By starting at the 'C' level, you maximize your ability to obtain management buy in. Decision makers will push the project on the business side. To add to your intro article, it is important to understand the business entities and focus on one at a time. Break your staff up so each is point for a different business unit. This helps build rapport with users by putting a familiar face to the data warehouse. Then cross train your developers to insure they can support one another. Finally, request subject matter experts from the business units. This allows your point developer to be part of a cross functional team and insures an actionable end product from the warehouse. Great intro article!
SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 740
Interesting article Andy, thanks! Interesting in the sense that the organisation I work for is currently looking at developing a BI solution, and the approach that's being taken by those leading the development is contrary to the approach I think should be taken (which I think is more in line with your recommendations).

Their approach seems to be to take what's currently in place, and simply use SSIS to transfer the data from its various sources in to a data mart of some description (the schema of which is more or less being defined by the customer).

I'm trying to encourage them to look at this from the end user perspective, and try and establish what it is the user wants to see at the end of this process in their reports and what they want to be able to query. Then those with the experience and knowledge of data warehouse solutions can model the BI solution around that. Unfortunately, the concept of using SSAS or SSRS to support the overall requirement appears to be lost on them.

Someone build me another brick wall to hit my head against please - I've already knocked down the last one!
Joshua M Perry
Joshua M Perry
Mr or Mrs. 500
Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)

Group: General Forum Members
Points: 578 Visits: 550
This is a great method to get started, but make sure you are answering a relevant question. It may not always be "What do you use to measure how your business is doing?". If you're working with a CIO and the CIO wants to improve change management processes, the question might be something like "What runs where?". In that case, you would dig through the spreadsheets to find which applications run on which servers and so on. Being able to easily answer a relevant question is what makes the DW valuable to management.

Joshua Perry
http://www.greenarrow.net
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12489 Visits: 10692
There are a series of canned questions that should always be asked upfront, and these should be documented in the requirements. However, I also find it essential to ask the business to provide a mockup of the end product. Let them actually draw a picuture (preferably in Excel with real numbers that add up) of what the report or dashboard screen fed from the datawarehouse should look like. This Excel sheet is something that should be passed around to all the stake holders and end users so they have an opportunity to input. As a reality check, compare that mockup back to the original requirements document and you'll be amazed how many critical holes it fills, especially if the requirements were written by a 3rd party or were pre-written before you were hired on the project.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
dan-16162
dan-16162
SSC Eights!
SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)

Group: General Forum Members
Points: 857 Visits: 95
Thanks, Andy!

This is a topic that has come to the forefront for me lately, specifically, we have an operational database, and we want to get to the point of being able to use cubes. The missing piece for us is a data warehouse.

I already have a degree of familiarity with development methodologies, SQL Server BI stack, our institutional culture with regard to data, etc. I really like your approach, and look forward to the rest of this series.

I am specifically interested on any tips for getting our operational data into the needed fact/dimension format.

Thanks!
Dan



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