InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:15:24 GMT20http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxNice Article, Andy... thanks. You know the process you describe is the process you should go through to scope any project... not just DW! Look forward to reading more. Hey why not suggest that we can subscribe to any artile you write? The search on the site leaves a lot to be desired, in part becouse there is so much out here!Tue, 26 Feb 2013 11:40:04 GMTdwilliscphttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxGood Day Andy,Must say I think I like your work, simple and methodical!!! Version 0 is a very critical step that many Developers/Managers tend to ignore, they dive staight into the deep-end without the critical work of preparation. I'm glad you highlight it's importance.It seems to me that you will be using the Kimball Methodology, as you mention "Data warehouses simply do not lend themselves to waterfall methodologies; they’re better suited for iterative development", to develop your DW System. Is this the case?!Looking forward to your next Piece of Art!!! A keen follower of your work on the Stairway to Integration Services!!!Regards,Aquila HaniseBI DeveloperSouth AfricaTue, 12 Apr 2011 05:26:51 GMTMr Quillzhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspx[quote][b]David.Poole (4/11/2011)[/b][hr]The intent of Version 0 is to show what can be achieved. A taster with a promise of jam tomorrow. My worry is that executive thinking is "if they developed this with in no time and no resource then I can get a lot for very little outlay".As in an earlier post there has to be careful management of expectations.I've had a proof-of-concept backfire on me spectacularly where most of the people involved agreed that the POC had failed in almost every tangible way but mission critical functionality had found its way into the POC so it went ahead anyway.I am also extremely wary of Excel. I think I've seen it described as a Spreadmart! On one hand the business get great utility and early pay back. On the other hand they've driven a very large truck down a very tight cul-de-sac.[/quote]Excel is a fine business intelligence tool, so long as it's used for reporting or pivoting data from the Data Warehouse. If your team includes a business analyst who is also sharp with Excel, then the database developers can focus on the heavy lifting of creating the ETL processes and data model for the warehouse while the BA stays focussed on the client and handles all of their 1001 requests.Mon, 11 Apr 2011 15:42:47 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxThe intent of Version 0 is to show what can be achieved. A taster with a promise of jam tomorrow. My worry is that executive thinking is "if they developed this with in no time and no resource then I can get a lot for very little outlay".As in an earlier post there has to be careful management of expectations.I've had a proof-of-concept backfire on me spectacularly where most of the people involved agreed that the POC had failed in almost every tangible way but mission critical functionality had found its way into the POC so it went ahead anyway.I am also extremely wary of Excel. I think I've seen it described as a Spreadmart! On one hand the business get great utility and early pay back. On the other hand they've driven a very large truck down a very tight cul-de-sac.Mon, 11 Apr 2011 15:27:46 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxNice article, thanks Andy. I can't wait to see the rest of the series.Sun, 10 Apr 2011 23:42:25 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxI'd like to add one point to your great article. DW ROI is not proportionate to the development time invested. Some business areas can provide high ROI, with a short development time. Having some idea of how the business operates, I like to approach these high ROI areas first - the outcome leads to a DW selling point to the wider company.Also, for goodness sake, if the company decides to purchase a logical model like the IBM retail model, it's important that they understand that it isn't a functional DW. I'm not sure how IBM sell some of their models, but at least one company I've worked with has had the expectation that the retail model is a bolt-on solution.Sun, 10 Apr 2011 19:50:58 GMTscottm30http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxThank you firstly for finally writing this DW article. I believe that you must include in version 0 the slow methods of flattening the data in a preprocessed way and one or two aggregated methods of looking at the report data, (analysis reports). Although the stake holder doesn't explicitly ask for the additional methods it assists you in understanding the data in ways that you would find interesting. You essentially become even more engaged. While creating the aggregates you will invariably create faster, more efficient and elegant way of flattening and pre-processing.As a programmer, the scripts I generate initially use many procedural lines and use many temp tables. While debugging I can use the temp tables to see how the data is being massaged. Later this gets condensed, some become CTE's. Lately I have also moved the filters to CASE statements as columns with 1/0 results. The advantages for both methods is that I can rerun the scripts repeatedly, daily etc. The CASE test columns allow me to see the results as a pie of sorts, the filtered and the unfiltered, quickly.Version 1 has the above scripts morphed as an sp, params are refined, and the proc is Compared to a SSIS/ETL. Also Version 1 underlying base tables are expanded, to include aggregate/calc columns, in an effort to solve or produce additional reports that you have now gathered are needed. Then wash rinse and repeat.Sun, 10 Apr 2011 14:28:21 GMTSean Josiah-454849http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxGreat. Next?Fri, 08 Apr 2011 07:05:35 GMThavilesrhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxWe are finding that PowerPivot is an excellent tool for performing this 'discovery phase'. Not only does it unearth the data sources its also gives you a heads-up on data quality and potential master data management issues which tend to throw a spanner in many BI projects. As a BA it allows you to get a far better understanding of the business process and how the data is used to support it, and it allows you to literally sit and design reports with the business users and get their buy in - user adoption is key to maximizing ROI. It is often said that business users don't really know what they want and that with BI projects requirements change very rapidly post-deployment once users actually get their hands on the data and the tools to analyse it. PowerPivot allow us to deliver useful results and built a good rapport with the stakeholders very quickly.Thu, 07 Apr 2011 23:52:32 GMTpeter.schmidt 16238http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxI think we're in agreement. If you need to convince executive management of the need for a data warehouse, "Version 0" would serve a vital purpose. There's a right way and wrong way though. The right way, if multiple business subject areas are included in that report, is to scale down the ultimate report to a small time range or a small subset from one or two data sources. And build the dimensional model correctly. The wrong way is to construct a spreadsheet model and the nightware that that ETL will become. Come in after the wrong way was done and was warned never to ever say "data warehouse" to executive management if I wanted to stay employed. If the second approach is used, the result is likely to become worse than if no attempt was ever attempted. In other words, a solid architecture is essential. If you swerve from it, your chances of success are greatly diminished. Looking forward to the next article.Thu, 07 Apr 2011 22:07:22 GMTMartin Masonhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxMost of the responses here are about what I believe Andy would see as the Version 1. But he talks about the step BEFORE diving in too deep into this. His approach is an exercise about getting to know the business. And using a single report as a guide/goal to help focusing the people you talk to. This one report will probably be thrown away when version 1 comes along which includes the whole subject area and allows for obtaining the same report again (but implemented in a more solid way).The big thing for version 0 is to get to know the environment in which the data warehouse is going to operate (where are the original data-sources, how reliable is the data-input, who is fudging data on the way to the top, who will be obstructing any change, who will sponsor the project, etc, etc). Delivering the actual version 0 report is just a bonus to show your merits to management, but the real deliverable will be YOUR knowledge about the business inner workings, which will increase your chances of success for version 1 many-fold.Thu, 07 Apr 2011 17:08:50 GMTmar10br0http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspx[quote][b]Eric M Russell (4/7/2011)[/b][hr]The initital build of the datawarehouse typically stems from a request coming from uppermost management for an enterprise level report or dashboard that shows them X, Y, Z and the IT department just sort of figures out at some point (hopefully before the first deployment) that a datawarehouse of some type would be essential.Not unless they bring in an outside consultant, or at least one person on the project is up to speed on database architecture, would the initial phase of development ever involve a discussion of Kimball, Inmon, OLAP cubes, or even data replication.[/quote]Hi Eric, I concur. I've seen customers purchase a reporting solution - usually an expensive one at that - and believe they have purchased a business intelligence solution. My guess is they spoke with a salesperson first, and then no one else after. If a business wants to implement a successful data warehouse, I believe they should speak to Data Warehouse or Business Intelligence Architect first. A good BI/DW architect is going to meet with the business people [i]and[/i] the IT Department.Magarity and Martin: I mentioned Craig Utley in the article and his deference to "boiling the ocean." I agree with him and resist any attempts to increase the scope of Version 0. I've seen the larger projects fail many times. This Version 0 approach hasn't failed yet. :{&gt;Thu, 07 Apr 2011 17:02:39 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspx[quote][b]Martin Mason (4/7/2011)[/b][hr][quote][b]magarity kerns (4/7/2011)[/b][hr]I think the first assertion needs a slight modification; a first version DW should produce one reporting subject area, not just one report. From there, it's a question of how many reports are needed, which may be only one but could be several.[/quote]I totally agree with you. Start with one subject area, not one report. If you begin with one subject area and use the Kimball four step process, (1) identify the business process, (2) explicitly state the grain or level of detail required, (3) identify the dimensions or business actors acting in that process, and (4) identify the metrics used in the analysis of that business process, you're well on your way to success. Plus, your building the blocks for additional subject areas to be integrated by sharing conformed dimensions. If you're guided by a report, you're much more likely to build a data silo that will be much more difficult to extend and maintain.[/quote]The problem is that in most cases the business doesn't start out by saying [i]"we need a datawarehouse, so let's put our heads together and figure out the best way to go about doing that"[/i] The initital build of the datawarehouse typically stems from a request coming from uppermost management for an enterprise level report or dashboard that shows them X, Y, Z and the IT department just sort of figures out at some point (hopefully before the first deployment) that a datawarehouse of some type would be essential.Not unless they bring in an outside consultant, or at least one person on the project is up to speed on database architecture, would the initial phase of development ever involve a discussion of Kimball, Inmon, OLAP cubes, or even data replication.Thu, 07 Apr 2011 12:41:00 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspx[quote][b]magarity kerns (4/7/2011)[/b][hr]I think the first assertion needs a slight modification; a first version DW should produce one reporting subject area, not just one report. From there, it's a question of how many reports are needed, which may be only one but could be several.[/quote]I totally agree with you. Start with one subject area, not one report. If you begin with one subject area and use the Kimball four step process, (1) identify the business process, (2) explicitly state the grain or level of detail required, (3) identify the dimensions or business actors acting in that process, and (4) identify the metrics used in the analysis of that business process, you're well on your way to success. Plus, your building the blocks for additional subject areas to be integrated by sharing conformed dimensions. If you're guided by a report, you're much more likely to build a data silo that will be much more difficult to extend and maintain.Thu, 07 Apr 2011 12:14:55 GMTMartin Masonhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxI think the first assertion needs a slight modification; a first version DW should produce one reporting subject area, not just one report. From there, it's a question of how many reports are needed, which may be only one but could be several.So the question to the CxO becomes 'About what subject in our business do you need better information?' Typical answers include 'sales', 'inventory', 'customers'. Then we can ask what in particular about that subject is needed (sales across systems, inventory across systems, customers across time) as a genesis for some potential reports (plural) from that starter DW.The single report question can set wild expectations and backfire into a monster project request that has to be scaled back, which is why I'm wary of it. Asking up front for potentially anything sets a high first expectation that may need dramatic scaling down. Let's say the answer from CxO is "I really need a combined sales report from systems A, B, C, D, E and F". CxO smiles, having an instant vision of such a report. Good luck bashing that out in a few weeks, so right off the bat DW person has to tell CxO we can only do systems A and B to start. CxO frowns at the delay.Now imagine asking about what subject first. CxO says, "I need better insight into total sales because I have to look at 5 reports in different formats." CxO frowns at thinking of the hassle. DW person: "I can make a version 1 DW for you in a month that has a unified sales report from systems A and B and we can add the other systems every month or two after that depending on their complexity." CxO smiles.Meanwhile, a subject area DW should be able to immediately serve other needs. If the one report method is strictly followed then some small incremental data Y that is involved in the subject area in general but not needed for the report would be excluded. Then when VP1 hears about CxO's upcoming new report and says, "I need exactly that with Y added to it" DW person can either say 'we can put that in the report writers' queue because the data is already on the way' (VP and CxO smile) or 'I'll have to see about adding that in" (VP frowns at the delay).The subject area method can take 10% to 20% more time but the ability to provide N reports instead of 1 provides N*100% more worth and visibility.Thu, 07 Apr 2011 11:53:09 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxThanks, 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!DanThu, 07 Apr 2011 11:37:39 GMTdan-16162http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxThere 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.Thu, 07 Apr 2011 10:05:29 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxThis is a great method to get started, but make sure you are answering a [i]relevant[/i] 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.Thu, 07 Apr 2011 09:56:55 GMTJoshua M Perryhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxInteresting 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!Thu, 07 Apr 2011 08:23:07 GMTSQLPhilhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxThis 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!Thu, 07 Apr 2011 08:16:58 GMTjmonk 57451http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxGreat 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.Thu, 07 Apr 2011 07:01:31 GMTMartin Masonhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxThanks 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.Thu, 07 Apr 2011 06:52:52 GMTGopi Shttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxAndy, 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.Thu, 07 Apr 2011 05:03:49 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxAn interesting article for a Data Warehouse newb. I'm very much looking forward to reading the follow up articles in this series. Thanks. :-)Thu, 07 Apr 2011 01:59:39 GMTChris Houghtonhttp://www.sqlservercentral.com/Forums/Topic1089698-208-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Data+Warehouse/72757/">Data Warehouse Development: Version 0</A>[/B]Thu, 07 Apr 2011 00:01:45 GMTAndy Leonard