tag:blogger.com,1999:blog-27076135556230476722020-02-28T20:20:12.772-05:00Chris Adamson’s BlogInformation for BI & Analytics ProfessionalsChris Adamsonhttp://www.blogger.com/profile/14386002073556773411noreply@blogger.comBlogger113125tag:blogger.com,1999:blog-2707613555623047672.post-159177389539269112017-09-25T11:28:00.000-04:002017-09-26T07:41:11.161-04:00Dimensional Models: Now More Than Ever<i>Do new technologies and methods render the dimensional model obsolete?</i><br /><i><br /></i><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://4.bp.blogspot.com/--s_jEy9-hvI/TZHHkfSf0FI/AAAAAAAAAcA/SleXfOtuZ7wRyQUyp7C6YtI-GNEOp2dxACPcBGAYYCw/s1600/4579775685_b8c9c16288_m.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="240" data-original-width="240" src="https://4.bp.blogspot.com/--s_jEy9-hvI/TZHHkfSf0FI/AAAAAAAAAcA/SleXfOtuZ7wRyQUyp7C6YtI-GNEOp2dxACPcBGAYYCw/s1600/4579775685_b8c9c16288_m.jpg" /></a></div><br />The top question from readers of this blog continues to be: "Is the dimensional model still relevant?"<br /><br />It is easy to understand why people ask this question:<br /><br /><ul><li>Our <b>BI programs</b> have expanded beyond data warehousing to include performance management, analytics, and governance functions. </li><li><b>Methods</b> have evolved and streamlined, thanks to the application of agile principles. </li><li><b>Technologies</b> have advanced to include NoSQL solutions, schema-less paradigms, and virtualization options. </li></ul><br />In a recent article for TDWI’s <i>Upside</i>, I discuss these changes to our data management processes, and their impacts on the dimensional model.<br /><br />The conclusion: <i>these pressures actually increase the importance of the dimensional model.</i><br /><br />Here are a few points made in the article:<br /><blockquote class="tr_bq"><ul><li>Although NoSQL technologies are contributing to the evolution of data management platforms, they are not rendering relational storage extinct. It is still necessary to track key business metrics over time, and on this front relational storage reigns. In part, this explains why several big data initiatives seek to support relational processing on top of platforms such as Hadoop. Nonrelational technology is evolving to support relational; the future still contains stars.</li></ul><ul><li>[A dimensional view of data] grows in importance as the underlying storage of data assets grows in complexity. The dimensional model is the business's entry point into the sprawling repositories of available data and the focal point that makes sense of it all.</li></ul><ul><li>The dimensional model of a business process provides a representation of information needs that simultaneously drives the traditional facts and dimensions of a data mart, the key performance indicators of performance dashboards, the variables of analytics models, and the reference data managed by governance and MDM. In this light, the dimensional model becomes the nexus of a holistic approach managing BI, analytics, and governance programs.</li></ul></blockquote><br />As we move to treat information as a business asset, the dimensional model has become a critical success factor. Yet many organizations are spread so thin that this critical skill is often missing. Be sure that doesn’t apply to your business!<br /><br />For the full discussion, check out the article: <a href="https://tdwi.org/articles/2017/04/12/dimensional-models-in-the-big-data-era.aspx">Dimensional Models in the Big Data Era</a>. (Chris Adamson, April 12, 2017, TDWI’s Upside.)<br /><br /><br /><br /><b>Learn More</b><br /><br />Join Chris for three days of dimension modeling education in New York next month!<br /><ul><li><a href="https://tdwi.org/events/seminars/new-york/home.aspx">TDWI New York Seminar</a>, October 23-25. Earn a <a href="https://tdwi.org/events/seminars/new-york/information/dimensional-data-modeling-certificate-program.aspx">certificate</a> and 24 CPE credits. Check out the sidebar of this blog for additional dates. You can also bring my courses on site.</li></ul>Read the full article on Upside:<br /><div><ul><li><a href="https://tdwi.org/articles/2017/04/12/dimensional-models-in-the-big-data-era.aspx">Dimensional Models in the Big Data Era</a>. (Chris Adamson, April 12, 2017, TDWI’s Upside.)</li></ul><div>Read Chris's book:</div><div><ul><li><a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320">Star Schema: The Complete Reference </a>(McGraw-Hill, 2010)</li></ul></div></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-71707927122260561852017-06-05T12:26:00.001-04:002017-06-05T12:35:51.260-04:00In Praise of the Whiteboard<i>Modeling tools are great, but early stage modeling activities are best conducted on a whiteboard. It is better for collaborative development, and handles rapid change more effectively. And there are inexpensive solutions if one is not readily available.</i><br /><br /><div>When I lead seminars that cover modeling techniques, this question always comes up: “What’s the best modeling tool?” </div><div><br /></div><div>My answer: Start modeling activities on a whiteboard. Break out the software later, one the model has stabilized.<br /><br />Using a whiteboard, you will get to a better solution, faster. </div><div><br /></div><div>I find this to be true across the spectrum of BI project types and modeling techniques. Examples include:</div><div><ul><li>Dimensional models (OLAP projects)</li><li>Strategy maps (Performance Management projects)</li><li>Influence Diagrams (Business Analytics projects)</li><li>Causal loop diagrams (Business Analytics projects)</li></ul>There are two reasons a whiteboard works best for this kind of work: it supports collaboration, and it is better suited to the rapid changes common in early stage modeling. In short, a whiteboard is inherently agile.</div><div><br /></div><div><b>Collaboration</b></div><div><br /></div><div><div>The best models are produced by small groups, not individuals. Collaboration generates useful and creative ideas which reach beyond what a seasoned modeler can do alone. </div><div><br /></div><div>Each of the techniques listed above <b>requires</b> collaboration between business and technical personnel. And within either of these realms, a diversity of perspectives always produces better results. Brainstorming is the name of the game.</div></div><div><br /></div><div>Use of a modeling tool quashes the creativity and spontaneity of brainstorming sessions. You may have experienced this yourself.</div><div><br /></div><div>Imagine five people in a room, one person’s laptop connected to a projector. Four people call out ideas, but the facilitator with the laptop can only respond to one at a time. The session becomes frustrating to all participants, no matter how good the facilitator is. The team loses ideas, and participants lose enthusiasm.</div><div><br /></div><div>Now imagine the same five people in front of a whiteboard, each holding a pen. Everyone is able to get their ideas onto the board. While this may seem like anarchy, it helps ensure that no ideas are lost and it keeps everyone engaged. The result is always a better model, developed faster.</div><div><br /></div><div><b>Rapid change</b></div><div><br /></div><div>The other reason to start on a white board is practical: it is easy to erase, change, redraw. And you will be doing a lot of these things if you are collaborating.</div><div><br /></div><div>Imagine a group is sketching out a model, and decides to make a major change. Perhaps one fact table is to be split into two. Or a single input parameter is to be decomposed into four. If a modeling tool is in use, making the change will will require deletion of elements, addition of new elements, and perhaps a few dialog boxes, check boxes, and warning messages. The tool gets in the way of the creative process.</div><div><br /></div><div>Now imagine a whiteboard is in use. A couple of boxes are drawn, some lines erased, some new lines added. The free flow of ideas continues, uninterrupted. Once again, this is what you want.<br /><br />Unimpeded collaboration produces better results, faster.</div><div><br /></div><div><b>No white board? No problem.</b></div><div><br /></div><div>Don’t let the lack of a whiteboard in your cube or project room stop you.</div><div><br /></div><div>There are many brands of inexpensive whiteboard sheets that cling to the wall. These have the additional benefit of being easy to relocate if you are forced to move to another room. Here is <a href="http://amzn.to/2rMMLct">one example</a>, available on Amazon.com:</div><div><br /><div style="text-align: center;"><a href="https://www.amazon.com/gp/product/B00RPOJGS6/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=B00RPOJGS6&linkCode=as2&tag=oakton-20&linkId=c913b2741a82571f4b4c9bff925c5639" target="_blank"><img border="0" src="//ws-na.amazon-adsystem.com/widgets/q?_encoding=UTF8&MarketPlace=US&ASIN=B00RPOJGS6&ServiceVersion=20070822&ID=AsinImage&WS=1&Format=_SL250_&tag=oakton-20" /></a><img alt="" border="0" height="1" src="//ir-na.amazon-adsystem.com/e/ir?t=oakton-20&l=am2&o=1&a=B00RPOJGS6" style="border: none !important; margin: 0px !important;" width="1" /></div><br /></div><div><br /></div><div>There are also several kinds of whiteboard-style notebooks. These are useful if you are working alone or in a group of two. They provide the same benefit of being able to collaborate and quickly change your minds, but in a smaller format. The one that I carry is called <a href="http://amzn.to/2sxc12y">Wipebook</a>:</div><br /><div style="text-align: center;"><a href="https://www.amazon.com/gp/product/B01HAJBJEY/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=B01HAJBJEY&linkCode=as2&tag=oakton-20&linkId=e15e6dc5fb0bd26f398631ab3bd29520" target="_blank"><img border="0" src="//ws-na.amazon-adsystem.com/widgets/q?_encoding=UTF8&MarketPlace=US&ASIN=B01HAJBJEY&ServiceVersion=20070822&ID=AsinImage&WS=1&Format=_SL250_&tag=oakton-20" /></a><img alt="" border="0" height="1" src="//ir-na.amazon-adsystem.com/e/ir?t=oakton-20&l=am2&o=1&a=B01HAJBJEY" style="border: none !important; margin: 0px !important;" width="1" /></div><br />I learned about these and similar solutions from clients and students in my seminars.<br /><br /><div><b>…and then the tool</b></div><div><br /></div><div>All this is not to say that modeling tools are bad. To the contrary, they are essential. Once the ideas have been firmed up, a modeling tool is the next step.<br /><br />Modeling tools allow you to gets ideas into a form that can be reviewed and revised. They support division of labor for doing required “grunt work” — such as filling in business definitions, technical characteristics, and other metadata. And they produce useful documentation for developers, maintainers, and consumers of your solutions.<br /><br />But when you’re getting started, use a whiteboard!</div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-54147917079502471742017-03-20T10:22:00.000-04:002017-03-20T16:39:27.289-04:00Tapping Into Non-Relational Data<i>Modern BI and Analytics programs use non-reatlional data management for six key functions. You should be cognizant of these functions when you add non-relational technology to your data architecture.</i><br /><i><br /></i><i><br /></i><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-HisCqg7hyCA/WM_hDaFin3I/AAAAAAAAAjE/tyaTsAkwATInSnXaVdqczwvE-Hi2eCvGwCLcB/s1600/Tapping%2BIn%2B-%2BBlog.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="" border="0" height="277" src="https://1.bp.blogspot.com/-HisCqg7hyCA/WM_hDaFin3I/AAAAAAAAAjE/tyaTsAkwATInSnXaVdqczwvE-Hi2eCvGwCLcB/s400/Tapping%2BIn%2B-%2BBlog.jpg" title="Tapping Into Non-relational Data" width="400" /></a></div><br /><br /><b>Kinds of Non-Relational Storage</b><br /><b><br /></b>Most IT professionals are familiar with the RDBMS. Relational databases store data in tables that are defined in advance. The definition specifies the columns that comprise the table, their data types, and so forth. The design is referred to as a <i>data model</i> or <i>schema</i>.<br /><br />Relational storage is immensely useful, but it is not the only game in town. There are several alternative types of data storage including:<br /><ul><li><b>Key-value</b> <b>stores</b> store data in associative arrays comprised of sort keys and associated data values. These flexible data structures can be distributed across nodes of commodity hardware, and are manipulated using distributed processing algorithms (map-reduce). <a href="https://hadoop.apache.org/">Hadoop</a> functions as a key-value store.</li></ul><ul><li><b>Document stores</b> track collections of documents that have self-defining structure, often represented in XML or JSON formats. A document store may be built on top of a key-value store. <a href="https://www.mongodb.com/">MongoDB</a> is a document store.</li></ul><ul><li><b>Graph databases</b> store the connections between things as explicit data structures (similar to pointers). These are stored separately from the things they connect. This contrasts with the RDBMS approach, where relationship information is stored within the things being associated (keys within tables). <a href="https://neo4j.com/">Neo4j</a> is a graph database. </li></ul><b>Reasons for Non-Relational Storage</b><br /><br />In the age of big data, organizations are tapping into these forms of storage for a variety of reasons. Here are just a few:<br /><ul><li>No model: not requiring a predefined schema enables storing new data that has yet to be explored and modeled.</li><li>Low cost: the cost of data management can be significantly lower than RDBMS storage.</li><li>Better fit: some business use cases are a natural fit to alternative paradigms.</li></ul>But don’t simply add a box to your data architecture for non-relational data. You must plan for specific usage paradigms, and make sure your architecture and processes support them.<br /><br /><b>Uses for Non-Relational Storage</b><br /><br />There are six major use cases for non-relational data in modern data architectures:<br /><ul><li><b>Capture</b> <i>Non-relational storage facilitates intake of raw data. </i>New data sets are captured in non-relational data stores, where they become “raw material” for various uses. A non-relational data store such as Hadoop can be used to capture data without having to model it first. This is often referred to as a <i>data lake. </i>I prefer to call it a <i>landing zone</i>.</li></ul><ul><li><b>Explore</b> <i>Non-relational storage facilitates exploration and discovery. </i>Exploration is the search for value in new data sets. Exploration applies analytic methods to captured data, often combining it with existing enterprise data. The goal is to find value in the data, and to identify things that will be worth tracking on a regular basis. </li></ul><ul><li><b>Archive</b> <i>Non-relational storage serves as an archive. </i>Data for which immediate value has not been identified is moved to an archive. From here it can be fetched for future use. Archiving data helps ensure the data lake does not become the fabled “data swamp.” An alternative to archiving unused data is simply to purge it.</li></ul><ul><li><b>Deploy</b> <i>Non-relational storage supports production analytics. </i>When value is found in data, it is transitioned to a production environment and processes are automated to keep it up to date. Deployments range from simple reports to complex analytic models.</li></ul><ul><li><b>Augment </b> <i>Non-relational storage serves as a staging area for the data warehouse</i>. In many cases, the insights gained from exploration prove valuable enough to track on a regular basis. Augmentation is the process of adding elements to a relational data warehouse that come from non-relational sources or analytic processes. A credit score, for example, might be incorporated into a customer dimension.</li></ul><ul><li><b>Extend</b>: <i>Non-relational storage expands what can be maintained in the data warehouse. </i>Sometimes there is lasting value in non-relational data, but is not appropriate to migrate it to relational storage. In such cases, the non-relational data is moved to a non-relational extension of the data warehouse. Applications can link relational and non-relational data. For example, non-relational XML documents may made available for “drill down” from a dimensional cube.</li></ul>In addition to these six primary use cases, non relational platforms may serve several utility functions. These include staging, data standardization, cleansing, and so forth.<br /><br /><b>Learn More</b><br /><br />Join me for my course <b>Data Modeling in the Age of Big Data</b>, offered exclusively through TDWI. At the time of this writing, it is offered next at <a href="https://tdwi.org/Events/Conferences/Chicago/Sessions/Thursday/Data-Modeling-in-the-Age-of-Big-Data.aspx">TDWI Chicago</a> on May 11, 2017. You can also bring this course to your site through TDWI Onsite Education. For more information <a href="http://chrisadamson.com/contact-chris-adamson/">contact me</a>.<br /><br /><br /><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-13696281501722643672017-03-19T10:58:00.000-04:002017-03-20T11:20:51.638-04:00Data Alone Does Not Change People’s Minds<i>On NPR’s Hidden Brain podcast, cognitive neuroscientist Tali Sharot discusses the role of data in changing people’s behavior.</i><br /><br /><b>From Data to Action</b><br /><br />The goal of analytics is to have a positive impact on the performance of your organization. To have an impact, you usually need to convince people to change their behavior. <br /><br />This is required whether you want to convince a CEO to adopt a new strategy, a manager to allocate resources differently, or a knowledge worker to change their processes.<br /><br />That’s why data visualization and data storytelling have become key skill sets for modern analytics professionals.<br /><br /><iframe frameborder="0" height="290" scrolling="no" src="https://www.npr.org/player/embed/519234721/520046420" title="NPR embedded audio player" width="100%"></iframe><br /><b>Data is Not Enough</b><br /><br />How do you convince people to change their behaviors? Many analysts fall into the trap of letting the data speak for itself.<br /><br />On a recent episode of NPR’s Hidden Brain podcast, cognitive neuroscientists Tali Sharot explains that data alone won’t do the job. (The podcast is embedded above.)<br /><br />Most people are familiar with the concept of confirmation bias, where we tend to accept data that supports our existing opinions. Sharot suggests there are ways to override this kind of bias.<br /><br />Some key takeaways:<br /><ul><li>People evaluate new information based on what they already believe</li><li>Strongly held false beliefs are difficult to change with data</li><li>Fear tends to lead to inaction, rather than action</li><li>Positive feedback or hope is a powerful motivator if you want to change peoples actions</li></ul><div>This is a fascinating listen for anyone interested in telling stories with data. Not only does it offer suggestions on how to change people’s behavior, it also illustrates the power of tracking results and making them available to people.</div><div><br /></div><div>I’ve pre-ordered Sharot’s upcoming book, <a href="http://amzn.to/2n0jOVi">The Influential Mind</a>. You should too!</div><div><br /></div><div><b>Recommended Podcast Apps</b></div><div><br /></div><div>I have received a lot of positive feedback from people who enjoy listening to the podcasts I mention on this blog. Several people have asked me how to listen to podcasts.</div><div><br /></div><div>You can, of course, simply click on the play button in the posts. But you can also subscribe to podcasts using a smartphone app. This lets you listen on the go, and also notifies you when new episodes are available.</div><div><br /></div><div>Here are two apps I recommend if you use an iOS device:</div><div><ul><li><a href="https://itunes.apple.com/us/app/castro-podcast-player/id1080840241?mt=8&at=1000lqg2">Castro Podcast Player</a> is perfect if you are new to podcasts, or if you subscribe to a handful of podcasts.</li><li><a href="https://itunes.apple.com/us/app/overcast-podcast-player/id888422857?mt=8&at=1000lqg2">Overcast: Podcast Player</a> is good for people who subscribe to a large number of podcasts. It is more complex, but allows you to set up multiple playlists and priorities.</li></ul></div><div><b>Further Reading</b></div><div><b><br /></b></div><div><ul><li><a href="http://blog.chrisadamson.com/2016/09/read-or-listen-to-discussions-of.html">Read (or Listen to) Discussions of Analytic Models</a> (9/28/2016)</li><li><a href="http://blog.chrisadamson.com/2015/12/what-hollywood-can-teach-analytics.html">What Hollywood Can Teach Analytics Professionals: How to Tell Stories</a> (12/23/15)</li></ul><div><br /></div></div><div><br /></div><div><br /></div><div>Source: <a href="http://www.npr.org/2017/03/13/519661419/when-it-comes-to-politics-and-fake-news-facts-arent-enough">NPR’s hidden brain podcast for March 13, 2017</a></div><div><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-5823667644513892852017-01-25T09:51:00.000-05:002017-01-25T09:54:34.621-05:00Avoid the Unintended Consequences of Analytic Models<i>Cathy O’Neil’s <a href="https://www.amazon.com/gp/product/0553418815/ref=as_li_tl?ie=UTF8&tag=oaktonsoftwar-20&camp=1789&creative=9325&linkCode=as2&creativeASIN=0553418815&linkId=b8ae5b29019a1f0dfb2120abb1a56275">Weapons of Math Destruction</a> is a must-read for analytics professionals and data scientists.</i><br /><br />In a world where it is acceptable for people to say, “I’m not good at math,” it’s tempting to lean on analytic models as the arbiters of truth.<br /><br />But like anything else, analytic models can be done poorly. And sometimes, you must look outside your organization to spot the damages.<br /><br /><b>The Nature of Analytic Insights</b><br /><br />Traditional OLAP focuses on the objective aspects of business information. “The person who placed this $40 order is 39 years old and lives in Helena Montana.” No argument there.<br /><br />But analytics go beyond simple <i>descriptive</i> assertions. Analytic insights are derived from mathematical models that make <i>inferences or predictions</i>, often using statistics and data mining.<sup>1</sup><br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://www.amazon.com/gp/product/0553418815/ref=as_li_tl?ie=UTF8&tag=oaktonsoftwar-20&camp=1789&creative=9325&linkCode=as2&creativeASIN=0553418815&linkId=b8ae5b29019a1f0dfb2120abb1a56275" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="320" src="https://3.bp.blogspot.com/-_3ZYKfVcQww/WIih7NXPx7I/AAAAAAAAAh4/h5zY_kQK3es489YVr8YZUo_XRBSXXKDmQCEw/s320/WeaponsMathDestruction.jpg" width="214" /></a></div>This brings in the messy world of probability. The result is a different kind of insight: “This person is likely to default on their payment.” How likely? What degree of certainty is needed to turn them away?<br /><br />When you make a decision based on analytics, <i>you are playing the odds at best</i>. But what if the underlying model is flawed?<br /><br />Several things can go wrong with the model itself:<br /><ul><li>It is a poor fit to the business situation</li><li>It is based on inappropriate proxy metrics</li><li>It uses training data that reinforces past errors or injustices</li><li>It is so complex that it is not understood by those who use it to make decisions.</li></ul>And here is the worst news: whether or not you manage to avoid these pitfalls, a model can seem to be “working” for one area of your business, while causing damage elsewhere.<br /><br />The first step in learning to avoid these problems is knowing what to look for.<br /><br /><b>Shining a Light on Hidden Damages</b><br /><br />In <a href="https://www.amazon.com/gp/product/0553418815/ref=as_li_tl?ie=UTF8&tag=oaktonsoftwar-20&camp=1789&creative=9325&linkCode=as2&creativeASIN=0553418815&linkId=b8ae5b29019a1f0dfb2120abb1a56275">Weapons of Math Destruction</a>, Cathy O’Neil teaches you to identify a class of models that do serious harm. This harm might otherwise go un-noticed, since the negative impacts are often felt outside the organization.<sup>2</sup> She calls these models “weapons of math destruction.”<br /><br />O’Neil defines a WMD as a model with three characteristics:<br /><ul><li><b>Opacity</b> – the workings of the model are not accessible to those it impacts</li><li><b>Scale</b> – the model has the potential to impact large numbers of people</li><li><b>Damage</b> – the model is used to make decisions that may negatively impact individuals</li></ul>The book explores models that have all three of these characteristics. It exposes their hidden effects on familiar areas of everyday life – choosing a college, getting a job, or securing a loan. It also explores their effects on parts of our culture that might <i>not</i> be familiar to the reader, such as sentencing in the criminal justice system.<br /><br /><b>Misaligned Incentives</b><br /><br />O’Neil’s book is not a blanket indictment of analytics. She points out that analytic models can have wide ranging benefits. This occurs when everyone’s best interests line up.<br /><br />For example, as Amazon’s recommendation engine improves, both Amazon and their customers benefit. In this case, the internal incentive to improve lines up with the external benefits. <br /><br />WMD’s occur when these interests conflict. O’Neil finds this to be the case for models that screen job applications. If these models reduce the number of résumés that HR staff must consider, they are deemed “good enough” to use. They may also exclude valid candidates from consideration, but there is not an internal incentive to improve them. The fact that they harm outside parties may even go unnoticed.<br /><br /><b>Untangling Impact from Intent</b><br /><br />WMD’s can seem insidious, but they are often born of good intentions. O’Neil shows that it is important to distinguish between the business objective and the model itself. It’s possible to have the best of intentions, but produce a model that generates untold damage.<br /><br />The hand-screening of job applications, for example, has been shown to be inherently biased. Who would argue against “solving” this problem by replacing the manual screening with an objective model? <br /><br />This may be a noble intention, but O’Neil shows that it fails miserably when the model internalizes the very same biases. Couple that with misaligned incentives for improvement, and the WMD fuels a vicious cycle that can have the precisely the opposite of the intended effect.<br /><br /><b>Learning to Spot Analytic Pitfalls</b><br /><br />The first step to avoiding analytics gone awry is to learn what to look for.<br /><br />“Data scientists all too often lose sight of the folks at the receiving end of the transaction,” O’Neill writes in the introduction. This book is the vaccine that helps prevent that mistake.<br /><br />If you work in the field of analytics, <a href="https://www.amazon.com/gp/product/0553418815/ref=as_li_tl?ie=UTF8&tag=oaktonsoftwar-20&camp=1789&creative=9325&linkCode=as2&creativeASIN=0553418815&linkId=b8ae5b29019a1f0dfb2120abb1a56275">Weapons of Math Destruction</a> is an essential read.<br /><br /><br /><i>Notes:</i><br /><br />1. OLAP and Analytics are two of the key service areas of a modern BI program. To learn more about what distinguishes them, see <a href="http://blog.chrisadamson.com/2015/02/the-three-pillars-of-modern-bi_9.html">The Three Pillars of Modern BI</a> (Feb 9, 2005).<br /><br />2. But not always. For example, some of the models explored in the book have negative impacts on employees.<br /><br /><br /><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-104885228844065802016-12-02T12:27:00.001-05:002016-12-02T12:38:12.802-05:00Is Your Team Losing the Spirit of the Agile Manifesto?<i>As the adoption of agile BI techniques spread, it is easy to become wrapped up in methodology and lose site of the agile spirit. </i><br /><i><br /></i><i>This year is the 15th anniversary of the Agile Manifesto. Mark the occasion by refocusing on the agile principles.</i><br /><br /><b>The Age of “NO”</b><br /><br />Fifteen years ago, most business software was developed using rich but complex methodologies. Businesses had spent years refining various <a href="https://en.wikipedia.org/wiki/Waterfall_model">waterfall-based methods</a>, which were heavily influenced by two decades of <a href="https://en.wikipedia.org/wiki/Information_engineering">information engineering</a>.<br /><br />The result seemed to make sense from IT’s point of view, ensuring a predictable process, repeatable tasks, and standard deliverables.<br /><br />To the people who needed the systems, however, the process was inscrutable.<sup>1</sup> It seemed like a bewildering bureaucracy that was managed in a foreign language. They were always being told “no.”<br /><br /><blockquote class="tr_bq"><i>Can we add a field to this screen?</i><br />No, you would have had to ask that before the requirements freeze.<br /><i>Can we change the priority of this report?</i><br />No, that decision must be made by the steering committee.<br /><i>Can we add a value to this domain?</i><br />No, that is the province of the modeling group.</blockquote><br />The groups were looking at software development from completely different perspectives. They were not really collaborating.<br /><br />Business people were asking for <i>functionality</i>. IT was beating back the requests by appealing to <i>methodology</i>.<br /><br /><b>Enter Agile</b><br /><br />In 2001, a group of developers met in Colorado to talk about what was working and not working in the world of software development. They produced the <a href="http://agilemanifesto.org/">Agile Manifesto</a>:<br /><br /><blockquote class="tr_bq"><div style="text-align: center;"><b>Manifesto for Agile Software Development</b></div><div style="text-align: center;"><br /></div><div style="text-align: center;">We are uncovering better ways of developing</div><div style="text-align: center;">software by doing it and helping others do it.</div><div style="text-align: center;">Through this work we have come to value:</div><div style="text-align: center;"><br /></div><div style="text-align: center;"><b>Individuals and interactions</b> over processes and tools</div><div style="text-align: center;"><b>Working software</b> over comprehensive documentation</div><div style="text-align: center;"><b>Customer collaboration</b> over contract negotiation</div><div style="text-align: center;"><b>Responding to change</b> over following a plan</div><div style="text-align: center;"><br /></div><div style="text-align: center;">That is, while there is value in the items on</div><div style="text-align: center;">the right, we value the items on the left more.</div><div style="text-align: center;"><br /></div><div style="text-align: center;"><span style="font-size: x-small;">Kent Beck, Mike Beedle, Arie van Bennekum, Alistair Cockburn, Ward Cunningham, James Grenning, Jim Highsmith, Andrew Hunt, Ron Jeffries, Jon Kern, Brian Marick, Robert C. Martin, Steve Mellor, Ken Schwaber, Jeff Sutherland, Dave Thomas</span></div><div><br /></div><div><div style="text-align: center;"><span style="font-size: x-small;">© 2001, the above authors</span></div><div style="text-align: center;"><span style="font-size: x-small;">this declaration may be freely copied in any form, but only in its entirety through this notice. </span></div></div></blockquote><br />The thrust of the manifesto is on collaboration between business and technical personnel, with an emphasis on visible business results. That is, while methods are important, results are more important.<sup>2</sup><br /><br />The Agile Manifesto helped refocus software development on the product: functional business applications.<br /><br /><b>Agile Today: The Danger of Cooptation</b><br /><br />Fifteen years later, it is safe to say that “Agile” has permeated the mainstream. This is largely a good thing. But whenever something moves from being a new alternative to wide acceptance, there is a potential dark side. As new ideas spread, they can be misunderstood or corrupted; adoption becomes cooption.<br /><br />I frequently see signs of back-sliding to The Age of “No.” Even among developers who follow agile-based approaches, there is a tendency to lose sight of the agile principles. Here are two examples from my recent experience:<br /><br /><div style="margin-left: 2em;">A team had decided to implement an unusual data model. It could easily return incorrect results if not queried in specific ways. The recommendation was to write some extra documentation explaining the pitfalls, and a short tutorial for developers. The response: “We cannot do that. We are an Agile shop. We cannot produce documentation that is not auto-generated by our tools.”<br /><br />On another occasion, a team was developing the scope for several work streams. One set of needs clearly required a three-week collaborative activity. This was expressly forbidden. “Our Agile approach requires everything be broken down into two-week sprints.”</div><br />In both cases, the response was couched in methodological concerns, with no focus on the business benefit or value.<sup>3</sup><i> </i><br /><i><br /></i><i>This is precisely the kind of methodological tunnel-vision against which the Agile Manifesto was a reaction.</i><br /><br /><b>Keeping the Faith</b><br /><br />It is hard to disagree with the agile principles, regardless of your organization’s commitment (or lack thereof) to an agile process.<br /><br />You can take one simple step to ensure that you are not losing touch with agile principles:<br /><br /><i> Whenever you are tempted to say “no,” pause and reflect.</i><br /><br />Why are you denying the request? Is it simply based on process? Think about what the person actually wants. Is there value? Is there a way to address the business concern?<br /><br />Sometimes “no” is the right answer. But always be sure your evaluation places business capabilities and benefits ahead of process and procedure.<br /><br /><br /><b>Learn More</b>:<br /><br />Read more posts about applying agile principles to BI and analytics:<br /><br /><ul><li><a href="http://blog.chrisadamson.com/2015/07/create-social-documentation.html">Create Social Documentation</a> (July 8, 2015)</li><li><a href="http://blog.chrisadamson.com/2014/02/document-information-requirements.html">Document Information Requirements Graphically with BDM Diagrams</a> (February 10, 2014)</li></ul><b>Notes</b><br /><br />1. These people were often referred to as “users.” Over time, this became a derogatory term.<br />2. Agile is often misinterpreted as emphasizing speed.<br />3. Luckily, both of these teams saw fit to make exceptions to their processes, prioritizing business value over method.<div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-52894261138550315402016-11-17T13:02:00.001-05:002016-11-17T13:08:54.981-05:00Probability and Analytics: Reactions to 2016 Election Forecasts<i>Reactions to the 2016 election forecasts suggest we don’t do a good job communicating probability and risk.</i><br /><br />In a September 2016 post, I suggested readers check out the discussions of analytic models at FiveThirtyEight. One of the links led to their forecast model for the 2016 presidential election.<sup>1</sup><br /><br />In the past week, I have received quite a bit of email suggesting I should take down the post, given that the model “failed.” For example, one emailer wrote:<br /><blockquote class="tr_bq">How can you continue to promote Nate Silver? The election result proved the analytics wrong.</blockquote>These reactions expose a real issue with analytics: most people do not understand how to interpret probability.<br /><br /><b>An analytic failure?</b><br /><br />On November 7th, the final prediction of the FiveThirtyEight “Polls Only Model” gave Hillary Clinton a <a href="http://projects.fivethirtyeight.com/2016-election-forecast/">71% chance</a> of winning. As things turned out, she lost.<br /><br />Those emailing me were not alone in believing the model failed. The day after the election, there were many stories suggesting FiveThirtyEight and the other aggregators were wrong.<sup>2</sup><br /><br />But were they?<br /><br /><div style="text-align: center;"><iframe allowfullscreen="" frameborder="1" src="http://www.espn.com/core/video/iframe?id=18025940&endcard=false"></iframe><br /></div><div style="text-align: center;"><span style="font-size: xx-small;">Nate Silver discusses the FiveThirtyEight Model</span></div><div style="text-align: center;"><span style="font-size: xx-small;">(If the video above does not play, you can access it <a href="http://www.espn.com/core/video/iframe?id=18025940&endcard=false%22%20allowfullscreen%20frameborder=%220%22">here</a>.)</span></div><br /><b>Understanding probability</b><br /><br />The FiveThirtyEight model gave Clinton a 71% chance of winning the election. That’s about a 7 in 10 chance. To understand how to interpret this probability, try the following thought experiment:<br /><br /><i>Suppose you are at Dulles airport, and are about to board a plane. While you are waiting, you are notified that there is a 7 in 10 chance your flight will land safely. Would you get on the plane? </i><br /><br />I know I wouldn’t.<br /><br />When the probability of something happening is 70%, the probability of it not happening is 30%. In the case of the airline flight, that’s not an acceptable risk!<br /><br /><i>Now suppose the flight lands safely. Was the prediction right?</i><br /><br />Maybe, but maybe not. The plane landed safely, but were the odds with the passengers? Was there actually a greater danger that was narrowly avoided? Was there no danger at all?<br /><br />When a single event is assigned a probability, its hard to assess whether the assigned probability was “correct.” <br /><br /><i>Suppose </i>every<i> </i><i>flight departing Dulles was given a 7 in 10 chance of landing safely, rather than just one. The next day, we check the results and find that all flights landed safely. Was the prediction correct?</i><br /><br />In this case, we are able to say that the model was clearly wrong. About 1,800 flights depart Dulles airport each day. The model predicted that thirty percent, or about 540 flights, would not land safely. It clearly missed the mark, and by a wide margin.<br /><br /><div>Probabilistic predictions are easier to evaluate when they apply to a large number of events.</div><br /><b>Explaining probability</b><br /><br />In the days and weeks leading up to the election, the FiveThirtyEight staff spent a good deal of time trying to put the uncertainty of their forecast in context. As the election drew closer, these became daily warnings:<br /><ul><li><a href="http://fivethirtyeight.com/features/election-update-dont-ignore-the-polls-clinton-leads-but-its-a-close-race/">November 6</a>: A post outlined just how close the race was, and how a standard polling miss of 3% could swing the election.</li><li><a href="http://fivethirtyeight.com/features/election-update-the-state-of-the-states/">November 7</a>: An update called a Clinton win “probable but far from certain.”</li><li><a href="http://fivethirtyeight.com/features/final-election-update-theres-a-wide-range-of-outcomes-and-most-of-them-come-up-clinton/">November 8</a>: The final model discussion outlined all the reasons a Clinton win was not a certainty, and explored scenarios that would lead to a loss.</li></ul><div>Despite all this, many people were unable to interpret the probabilistic model, and the associated uncertainty.</div><div><br /></div><div></div><b>Avoiding unrealistic expectations</b><br /><br />If a <a href="https://www.quantamagazine.org/20161108-why-nate-silver-and-sam-wang-are-wrong/">research scientist at Yale</a> and the <a href="https://www.technologyreview.com/s/602829/prediction-models-gone-wild-why-election-forecasts-and-polls-were-so-wrong/">MIT Technology Review</a> misunderstood a probabilistic forecast, how well are people in your business doing?<br /><ul><li>Are people in your business making decisions based on probabilistic models? </li><li>Are they factoring an appropriate risk level into their actions?</li><li>Are you doing enough to help them understand the strength of model predictions?</li></ul>It's important that decision makers comprehend the predictive strength of the models they use. And it’s everyone’s responsibility to make sure they understand.<br /><br /><i>We have a long, long way to go.</i><br /><br /><br /><br /><div><b>Notes:</b><br /><br />1. See the post <a href="http://blog.chrisadamson.com/2016/09/read-or-listen-to-discussions-of.html">Read (or Listen to) Discussions of Analytic Models</a>. The model discussion I linked to is: <a href="http://fivethirtyeight.com/features/a-users-guide-to-fivethirtyeights-2016-general-election-forecast/">A User’s Guide To FiveThirtyEight’s 2016 General Election Forecast</a><br /><br />2. “Aggregators" is a term used by the mainstream press to describe data scientists who build models based on polling data. Here are a few stories that suggested these models were wrong: <a href="http://www.thewrap.com/nate-silver-blew-it-bigly-on-the-election-can-his-brand-recover/">The Wrap</a>, <a href="http://www.vanityfair.com/news/2016/11/how-the-polls-gave-us-president-trump">Vanity Fair</a>, <a href="http://www.newyorker.com/news/john-cassidy/media-culpa-the-press-and-the-election-result">The New Yorker</a>, <a href="https://www.quantamagazine.org/20161108-why-nate-silver-and-sam-wang-are-wrong/">Quanta Magazine</a>, <a href="https://www.technologyreview.com/s/602829/prediction-models-gone-wild-why-election-forecasts-and-polls-were-so-wrong/">MIT Technology Review</a>.<br /><br /><br /></div><div><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://1.bp.blogspot.com/-veaDmgAmYi0/V-vXqfp6hJI/AAAAAAAAAfY/dKi507dRovUZfi2fZAEb3UbRVoa3JfbRwCK4B/s1600/The_Signal_and_the_Noise.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="200" src="https://1.bp.blogspot.com/-veaDmgAmYi0/V-vXqfp6hJI/AAAAAAAAAfY/dKi507dRovUZfi2fZAEb3UbRVoa3JfbRwCK4B/s200/The_Signal_and_the_Noise.jpg" width="132" /></a></td></tr><tr><td class="tr-caption" style="font-size: 12.800000190734863px; text-align: center;"><a href="http://amzn.to/2drCmZ5">Recommended Reading</a></td></tr></tbody></table><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-16694692307292761802016-09-28T10:56:00.000-04:002016-09-28T11:00:51.947-04:00Read (or Listen to) Discussions of Analytic Models<i>Organizations often feel their analytics are proprietary, and therefore decline to discuss how their models work. One shining exception is Nate Silver’s FiveThirtyEight.com. The site makes a point of exposing how their models are built. They also discuss their models as part of their elections podcast.</i><br /><br /><b>Data Storytelling</b><br /><br /><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody><tr><td style="text-align: center;"><a href="http://1.bp.blogspot.com/-veaDmgAmYi0/V-vXqfp6hJI/AAAAAAAAAfY/dKi507dRovUZfi2fZAEb3UbRVoa3JfbRwCK4B/s1600/The_Signal_and_the_Noise.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="320" src="https://1.bp.blogspot.com/-veaDmgAmYi0/V-vXqfp6hJI/AAAAAAAAAfY/dKi507dRovUZfi2fZAEb3UbRVoa3JfbRwCK4B/s320/The_Signal_and_the_Noise.jpg" width="212" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><a href="http://amzn.to/2drCmZ5">Recommended Reading</a></td></tr></tbody></table>As students in my courses know, <a href="http://fivethirtyeight.com/">FiveThirtyEight.com</a> is a data driven journalism blog founded by Nate Silver. FiveThirtyEight covers sports, politics, science, and popular culture.<br /><br />If you are interested in visualization, analytics, or telling stories with data, you will enjoy the site.<br /><br />Stories on FiveThirtyEight are always shaped by data. And if they develop a model of any kind, that model is openly explained. You may have to cull through footnotes, but its always there.<br /><br />One of the most detailed discussions on the site right now <a href="http://fivethirtyeight.com/features/a-users-guide-to-fivethirtyeights-2016-general-election-forecast/">describes their 2016 election forecast model</a>. (With apologies to readers outside the US, this is a very US-centric topic.)<br /><br /><b>Podcasts</b><br /><br />FiveThirtyEight also offers several <a href="http://fivethirtyeight.com/tag/fivethirtyeight-podcasts/">podcasts</a>, where you can listen to analyst discussions which are driven by data.<br /><br />Until recently, these conversations rarely delved into the technical realm. On the elections podcast, if Nate Silver or Harry Enton mentioned “long tails,” “blended averages,” or “p-values,” the other hosts jokingly steered the conversation back to analysis.<br /><br />That practice was put to an end a few weeks ago with the establishment of “Model Talk” episodes. Every second Friday the model itself is discussed in greater detail. For example, in the 8/26 episode, Silver describes the predictive value of state polls over national polls, and why it is important to build a model where state by state probabilities interact.<br /><br />Here are links to the “model talk" discussions to date:<br /><ul><li><a href="http://fivethirtyeight.com/features/the-elections-podcast-checks-in-on-the-2016-forecasts/">The Elections Podcast Checks In On The 2016 Forecasts</a> (Aug 26, 2016)</li><li><a href="http://fivethirtyeight.com/features/what-makes-a-tipping-point-state/">What Makes a Tipping-Point State</a> (Sep 9, 2016)</li><li><a href="http://fivethirtyeight.com/features/the-key-states-in-the-contest-for-senate-control/">The Key States In The Contest For Control</a> (Sep 23, 2016)</li></ul><div><b><br /></b><b>Recommended Reading</b><br /><br />I also highly recommend Silver’s book, <a href="http://amzn.to/2drCmZ5">The Signal and the Noise: Why So Many Predictions Fail—but Some Don’t</a>. If you are interested in analytics, it is a fascinating read.<br /><br /><br /></div><div><br /></div><div><br /></div><br /><br /><br /><br /><br /><br /><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-24196054211552613342016-04-24T11:00:00.002-04:002016-04-24T11:33:35.517-04:00Chris Adamson on Modeling Challenges<i>In a recent interview, the folks at <a href="https://www.wherescape.com/resources/video-ask-the-experts-chris-adamson/">WhereScape</a> asked me some questions about data modeling challenges.</i><br /><br /><iframe src="https://player.vimeo.com/video/157225482?byline=0" width="480" height="270" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen></iframe><br /><p></p><br />In Business Intelligence, modeling is a social activity. You cannot design a good model alone. You have to go out and talk to people.<br /><br />As a modeler, your job is to facilitate consensus among all interested parties. Your models need to reflect business needs first and foremost. They must also balance a variety of other concerns — including program objectives, the behavior of your reporting and visualization tools, your data integration tools, and your DBMS. <br /><br />It’s also important to understand what information resources are available. You need to verify that it is possible to fill the model with actual enterprise data. This means you need to profile and understand potential data sources. If you don’t consider sources of data, your designs are nothing more than wishful thinking.<br /><br />When considering a non-relational data sources, resist the urge to impose structure before you explore it. You’ve got to understand the data <i>before</i> you spend time building a model around it.<br /><br />Check out the video above, where I discuss these and other topics. For a full-sized version, visit the <a href="https://www.wherescape.com/resources/video-ask-the-experts-chris-adamson/">WhereScape</a> page.<div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-79053050118234518362015-12-23T11:11:00.001-05:002015-12-23T12:26:39.572-05:00What Hollywood Can Teach Analytics Professionals: How to Tell Stories<i>You might not realize it, but you probably have something in common with the creators of the TV show South Park. </i><br /><i><br /></i> <br /><blockquote class="tr_bq">Analytics yield insights that can have powerful business impact. These insights come from statistics and data mining—processes that are inaccessible to most people. If you want your business to learn and remember, you have to tell a story.</blockquote><div><br />All too often, the communication of an analytic finding reads like a police report: procedural, laden with jargon, and stripped of meaningful business context.<br /><br />That’s not interesting. People won’t learn from it, and they certainly won’t change their behavior.<br /><br />How then to get your point across? You need to learn how to tell stories. Data stories.<br /><br /><iframe allowfullscreen="true" frameborder="0" height="321" id="nyt_video_player" marginheight="0" marginwidth="0" scrolling="no" src="http://graphics8.nytimes.com/video/players/offsite/index.html?videoId=100000001039812&blogPostUrl=http://artsbeat.blogs.nytimes.com/2011/09/08/hello-matt-stone-and-trey-parker-crash-a-class-at-n-y-u/" title="New York Times Video - Embed Player" width="480"></iframe><br /><br />Trey Parker and Matt Stone know a thing or two about telling a story. They are the creators of <a href="https://en.wikipedia.org/wiki/South_Park">South Park</a>, a wildly successful television show which has been on the air for 19 years. Like you, their success depends on telling interesting stories.<br /><br />In the video clip above, Parker and Stone are speaking to a group of students at NYU on storytelling strategies. Trey tells the students:</div><div><br /><blockquote class="tr_bq">We can take these beats, which are basically the beats of your outline, and if the words “and then” belong between those beats, you’re f***ed. Basically. You’ve got something pretty boring.<br /><br />What should happen between every beat that you’ve written down is either the word “therefore” or “but.”</blockquote><br />Data storytellers make this mistake all the time. "We did this…then we tried that…the algorithm showed this…the correlation coefficient is that…our conclusion is...”<br /><br />This kind of forensic storytelling is boring. It won’t be remembered, and the value of the insight will be lost. Save the procedural detail for an appendix somewhere. People learn from good stories, not lab reports.<br /><br />As Matt says later in the clip, you need causality to have an interesting story:</div><div><br /><blockquote class="tr_bq">But. Because. Therefore. That gives you the causation between each beat. And that…that’s a story.</blockquote><div style="-webkit-text-stroke-width: 0px; color: black; font-family: -webkit-standard; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; margin: 0px; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;"><br /></div>Be sure to watch the entire clip and, if you are so inclined, take some time off for an episode or two of South Park. It just might make you a better data scientist!<br /><br /><br /><br /><i>The embedded video is from the NY Times ArtsBeat blog post, </i><a href="http://artsbeat.blogs.nytimes.com/2011/09/08/hello-matt-stone-and-trey-parker-crash-a-class-at-n-y-u">Hello! Matt Stone and Tray Parker Crash a Class at NYU</a><i> (September 8, 2011). Hat tip to Tony Zhou and his </i><a href="http://everyframeapainting.tumblr.com/post/115138120266/if-you-want-to-make-video-essays-theres-no">Video Essay on F for Fake</a><i> at the marvelous blog Every Frame a Paining.</i><br /><div style="-webkit-text-stroke-width: 0px; color: black; font-family: -webkit-standard; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; margin: 0px; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;"><br /></div><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-34772405196988821282015-07-08T09:47:00.001-04:002016-12-02T12:29:01.632-05:00Create Social Documentation<em>Documentation is sometimes viewed as a necessary evil. But it doesn't have to be. Here's how to produce documentation that will be used.</em><br /><blockquote><em>Useful</em> documentation gets <em>used</em> -- during all development phases, and by all interested parties.</blockquote>Burdensome methodologies often expend precious hours producing documentation that is hard to use. Many projects leave behind fat binders of text that hardly anyone will ever open. These examples have given documentation a bad name. <br /><br />The good news is that documentation can be done right. It does not have to be a drag on project time, it does not have to be a chore to read and review, and it does not have to be something we interact with alone.<br /><br /><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-YYUOFbQAuO4/VZ0saJKHXLI/AAAAAAAAARU/7_XaUh2utVQ/s1600/Social%2BDoc%2BPPT.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="275" src="https://2.bp.blogspot.com/-YYUOFbQAuO4/VZ0saJKHXLI/AAAAAAAAARU/7_XaUh2utVQ/s400/Social%2BDoc%2BPPT.png" width="400" /></a></div><br /><strong>Why we need documentation</strong><br /><br />Documentation is not an after-the-fact explanation of what has been built. Used properly, it is a central component of the entire lifecycle of a BI solution.<br /><br />Important uses include:<br /><br /><ol><li><strong>Prior to development</strong>: Identify and validate requirements and designs</li><li><strong>During development</strong>: Specify what to build</li><li><strong>After development</strong>: Educate business people and support personnel</li></ol><br />Of course, there are many other areas in which documentation has value (program planning, governance, change management, etc.). These three above are sufficient to illustrate the value of social documentation.<br /><div class="separator" style="clear: both; text-align: center;"><br /></div><strong>Social Documentation</strong><br /><br />Useful documentation should be easy to read and discuss. It should also not be burdensome to produce. Three principles shape social documentation.<br /><br /><span style="color: #0b5394;"><i>Social documentation is the focus of collaboration.</i> </span><br /><br />Whenever possible, I recommend to my clients that we use <strong>PowerPoint</strong> for documentation. Why? Word processors are tailor made for reading, which is a solitary activity. Presentation software is tailor made for collaboration.<br /><br /><i><span style="color: #0b5394;">Social documentation is easy to navigate. </span></i><br /><br />Support "random access" rather than "sequential access." Presentation software is great for this; we can easily sort and navigate slides by their titles. This can also be achieved using document maps or outlines.<br /><br /><i><span style="color: #0b5394;">Social documentation is not prose. </span></i><br /><br />Each slide in a presentation, or section in a document, should be set up to capture essential information in a consistent format. This format may be <strong>tabular,</strong> <strong>diagramatic,</strong> or <b>both</b>. Your subject matter will dictate the appropriate format.<br /><br />But here is the important part:<br /><ul><li>No paragraphs</li><li>No prose</li><li>If using PowerPoint: No bullet lists. (They're just a back door to writing paragraphs.)</li></ul><br /><strong>Uses for social documentation</strong> <br /><br />I find the presentation format excellent for defining program priorities, defining project scope, capturing business requirements, developing top level information architectures, and a variety of other tasks. For specifications, a word processed document with multi-level headers and a document map typically fits the bill.<br /><br />When documenting business metrics for a dashboard or scorecard, for example, set up a PowerPoint presentation with one slide per metric. Use a standard tabular format to document each metric. This documentation is easy to produce, review and revise, as I will discuss in a moment.<br /><br /><br />Where presentation software is not practical, word processors can be used in the same way. Divide the document into sections, activate the contents sidebar, and use a consistent tabular format.<br /><br />Of course, not all documentation is captured in this manner. For example, we might use social documentation to capture a top level star schema design, then use a modeling tool to produce a detailed design. <br /><br /><strong>Advantages of Social Documentation</strong><br /><br />This simple approach has numerous advantages.<br /><strong><br /></strong> <i><b><span style="color: #0b5394;">Frictionless and Comprehensive</span></b></i><br /><br />During requirements specification, social documentation allows you to capture the necessary information in frictionless and comprehensive manner. A standard tabular format, for example, ensures the same items are filled in. The presentation itself is easy to navigate via sections and slide titles.<br /><br /><i><b><span style="color: #0b5394;">Engages with the business</span></b></i><br /><br />Social documentation invites collaboration. Give people a big fat binder and their eyes will cross. Show them 5 or 6 slides that capture the business metrics they care about, and they will give you feedback.<br /><br />I always have my laptop with me, so if I happen to be in a room with a SME, I can pull it out, flip to the correct slide, and ask a question.<br /><br />Incidentally, collaboration with the business is one of the cornerstones of the agile manifesto.<br /><br /><i><b><span style="color: #0b5394;">Reviewed together, rather than in isolation</span></b></i><br /><br />Ever sent out a fat document for review? If you have, you know the results are not good. Most people will not review it by the deadline. When reminded, they will say, "it looks good." A precious few will provide detailed feedback.<br /><br />Social documentation transforms this process. A review is conducted by bringing people into a room and reviewing the deck. Any agreed upon changes are made directly to the presentation slides. <br /><br />The documentation is now ready for the next tasks: guiding development and then serving as the basis for education.<br /><br /><strong>Learn More</strong><br /><br />Read more about documenting BI program activities in these posts:<br /><ul><li>A great diagramming technique for information requirements: <a href="http://blog.oaktonsoftware.com/2014/02/document-information-requirements.html">Document Information Requirements Graphically with BDM Diagrams</a> (February 10, 2014.)</li><li>Recommended approach to documenting dimensional designs: <a href="http://blog.oaktonsoftware.com/2011/07/dimensional-modelers-do-not-focus-on.html">Dimensional Modelers Do Not Focus on Logical vs. Physical</a> (July 5, 2011)</li></ul>For more details on what to document, check out my book <a href="http://www.amazon.com/Schema-Complete-Reference-Christopher-Adamson/dp/0071744320?ie=UTF8&tag=oaktonsoftwar-20&link_code=btl&camp=213689&creative=392969">Star Schema: The Complete Reference</a>. Detailed descriptions and examples can be found in Chapter 18, "How To Design And Document A Dimensional Model.”<br /><br />I also discuss documentation of information requirements and business metrics in the course “Business Information and Modern BI.” Check the sidebar for current offerings.<div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-15156326742519448242015-03-26T22:13:00.001-04:002015-03-30T07:08:49.709-04:00Join Chris in Europe: 18-22 May 2015I will be leading a week of in-depth sessions in Berlin this May. The rigorous agenda includes full courses on Performance Management, Analytics, and Dimensional Modeling.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-d6wfc47aeRA/VRUu4MvcSyI/AAAAAAAAAPo/oEqIqAVnRIw/s1600/TDWI_Europe_logo_gr.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-d6wfc47aeRA/VRUu4MvcSyI/AAAAAAAAAPo/oEqIqAVnRIw/s1600/TDWI_Europe_logo_gr.jpg" height="248" width="320" /></a></div>Join me for any or all of the following sessions:<br /><br /><ul><li><a href="http://www.tdwi.eu/fortbildung/tdwi-in-depth-education/business-analytics/monday-2015-05-18/">18 May: TDWI Performance Management: Measurement, Metrics, and Monitoring</a> Learn about performance management and BI, including metric selection, dashboards and scorecards.</li><li><a href="http://www.tdwi.eu/fortbildung/tdwi-in-depth-education/business-analytics/tuesday-2015-05-19/">19 May: TDWI Business Analytics: Exploration, Experimentation and Discovery</a> Learn about problem framing, problem models, solution models, heuristics and experimentation.</li><li><a href="http://www.tdwi.eu/fortbildung/tdwi-in-depth-education/business-analytics/wednesday-2015-05-20/">20 May: TDWI Predictive Analytics Fundamentals</a> This class covers the business case for predictive modeling, statistics and data mining, the model development process, and the organizational impacts of predictive modeling.</li><li><a href="http://www.tdwi.eu/fortbildung/tdwi-in-depth-education/advanced-dimensional-modeling/thursday-friday-may-2122/">21-22 May: Dimensional Modeling: Advanced Techniques for Practitioners</a> This is the two-day companion course to my book, <a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320">Star Schema: The Complete Reference.</a></li></ul>Hope to see you there! For more details and to register, visit TDWI Europe:<br /><br /><ul><li>May 18-20 <a href="http://www.tdwi.eu/fortbildung/tdwi-in-depth-education/business-analytics/">Info and Registration</a></li><li>May 21-22 <a href="http://www.tdwi.eu/fortbildung/tdwi-in-depth-education/advanced-dimensional-modeling/">Info and Registration</a></li></ul>Join me for the whole week, and you will have covered each of the <a href="http://blog.oaktonsoftware.com/2015/02/the-three-pillars-of-modern-bi_9.html">Three Pillars of Modern BI!</a><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-58261795012887270592015-03-20T09:27:00.001-04:002015-11-12T11:29:49.238-05:00BI and the Path to Business Value<em>Managing BI services requires a consistent information architecture, even if different teams are responsible for data marts, performance management, and analytics.</em><br /><em><br /></em><h4>Business Value From BI</h4><div><br /></div>Business Intelligence is <strong><em>the use of information to improve business performance.</em></strong><sup>[1]</sup> To improve business performance, we must do three things:<br /><br /><ul><li>Track business performance</li><li>Analyze business performance</li><li>Impact business performance</li></ul><br />Each step on the path to business value is supported by two kinds of BI services, as shown in the illustration. <br /><br /><ul><li><strong>Tracking performance</strong> requires understanding what is currently happening (Performance Management) and what has happened in the past (OLAP).</li><li><strong>Analyzing performance</strong> requires the ability to get to detail (OLAP), develop insight into cause and effect (Business Analytics).</li><li><strong>Impacting performance</strong> requires targeting a business metric (Performance Management) and taking a prescribed course of action (Business Analytics.)</li></ul><br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-lQ8jScfDNNc/VNjD-t2pNdI/AAAAAAAAAOk/Ou2S_pps6uw/s1600/Triangle%2Band%2BCircles-Reporting.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="397" src="http://1.bp.blogspot.com/-lQ8jScfDNNc/VNjD-t2pNdI/AAAAAAAAAOk/Ou2S_pps6uw/s1600/Triangle%2Band%2BCircles-Reporting.jpg" width="400" /></a></div><br /><br /><br />Each of these steps leverages a pair of BI services, and each service shares a common interest in business information.<sup>[2] </sup>Managing BI services therefore requires a consistent information architecture. This is true even when separate teams manage each area.<br /><b><br /></b> <b> Tracking Performance</b><br /><b><br /></b> Understanding performance often starts with summarized data on dashboards and scorecards (Performance Management). The need investigate potential problems requires detailed data and history (OLAP and Reporting.)<br /><br />As Wayne Eckerson demonstrated in <a href="http://www.amazon.com/gp/product/0470589833/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0470589833&linkCode=as2&tag=oaktonsoftwar-20&linkId=NYZRS52Z4MUEPZ4C">Performance Dashboards,</a><img alt="" border="0" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=0470589833" height="1" style="border: none !important; margin: 0px !important;" width="1" /> both these areas provide stronger business value when they are integrated. For example, a dashboard is more useful when someone can click a metric and bring up supporting detail in an OLAP cube. <br /><br />To successfully link Performance Management and OLAP, the two domains must share common definitions for business metrics (facts) and associated reference data (dimensions). Metrics must be calculate in the same way, linked to reference data and different levels of detail, and synchronized (if managed separately). <br /><br /><b> Analyzing Performance</b><br /><br />Analyzing performance is the process of breaking down what has occurred in an attempt to understand it better. Slicing and dicing an OLAP cube is a form of analysis, providing insight through detail. Analytic models provide a deeper level of analysis, providing insight into cause and effect, and extending this to the future through prediction.<br /><br />OLAP is largely focused on exploring various aggregations of business metrics, while analytics is largely focused on the underlying detail that surrounds them. Our OLAP solutions provide historic detail to Business Analytics in the form of data from the data warehouse.<sup>[3]</sup><br /><br />The exchange flows the opposite direction as well. Business analytics develop insights that suggest other things that should be tracked by OLAP services. For example, a particular set of behaviors may define a high value customer. This assessment is developed using Business Analytics, and applied to the customers in the OLAP data mart. For a fun example from the world of sports, check out the book <a href="http://www.amazon.com/gp/product/0393324818/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0393324818&linkCode=as2&tag=oaktonsoftwar-20&linkId=APQYEZEVJE5RB7JF">Moneyball</a><img alt="" border="0" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=0393324818" height="1" style="border: none !important; margin: 0px !important;" width="1" />by Michael Lewis.<sup>[4]</sup><br /><b><br /></b> <b> Improving Performance</b><br /><b><br /></b> All of this is somewhat academic if people in the business do not use all this information to make decisions. Business impact occurs at the intersection of Performance Management (which tells us what is important and how we are doing) and Analytics (which suggests the best course of action.)<br /><br />Every analytic model targets a business metric or key performance indicator (KPI) from the world of performance management. That same KPI, in turn, can be used to measure return on investment of the analytic model.<br /><br />For example, a direct sales manager of enterprise software wants to reduce <em>cost of sales.</em> An analytic model is developed that assesses the likelihood of a prospect to buy enterprise software. <br /><br />The manager begins using the prospect assessment model to prioritize the work of the sales team. Less likely prospects are reassigned to a telesales force. Over the next two quarters, cost of sales starts falling. The same KPI that the analytic model targeted is used to measure its return on investment.<br /><b><br /></b> <b> Information as an Asset</b><br /><b><br /></b> It is common to manage each of the pillars of Modern BI as a separate program. The path to business value, however, requires that these programs share a consistent view of business information. BI programs that are not centralized must carefully coordinate around a common information architecture.<br /><b><br /></b> <b> Further Reading</b><br /><b><br /></b> <br /><ol><li>For more on this definition of business intelligence, see <a href="http://blog.oaktonsoftware.com/2014/09/business-intelligence-in-modern-era.html">Business Intelligence in the Modern Era</a> (9/8/2014)</li><li>The three service areas are explained in <a href="http://blog.oaktonsoftware.com/2015/02/the-three-pillars-of-modern-bi_9.html">The Three Pillars of Modern BI</a> (2/9/2015). </li><li>Sometimes analytic modelers bypass the data warehouse, but there are steps you can take to make this important repository more useful. For tips on how to make your data warehouse more useful to analytic modelers, see <a href="http://blog.oaktonsoftware.com/2013/09/optimizing-warehouse-data-for-business.html">Optimizing Warehouse Data for Business Analytics</a> (9/25/13). Note that even with a well designed data warehouse, analytic models often augment this enterprise data with additional data sources. </li><li>The Oakland A's used analytics to re-evaluate the basic metrics used to assess the value of a baseball player. See <a href="http://blog.oaktonsoftware.com/2013/07/business-analytics-and-dimensional-data.html">Business Analytics and Dimensional Data</a> (7/17/13).</li></ol><div><b>** Interested in learning more about modern BI programs?</b> **</div><div><br /></div><div>Check out my new course, <i>Business Information and Modern BI: Evolving Beyond the Dimensional Data Mart.</i> Offered at TDWI conferences and onsite. See the sidebar for upcoming dates.</div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-86131619713262391042015-03-11T07:36:00.000-04:002015-03-11T07:37:49.146-04:00Modern BI with Chris Adamson: Chicago, May 7Join me at TDWI Chicago 2015 for my latest course, <b><a href="http://events.tdwi.org/Events/Las-Vegas-2015/Sessions/Sunday/Business-Information-and-Modern-BI-Evolving-Beyond-the-Dimensional-Data-Mart.aspx">Business Information and Modern BI: Evolving Beyond the Dimensional Data Mart</a>.</b><br /><br /><a href="http://4.bp.blogspot.com/-WNA5Pc4irmc/VQAnRpDpnwI/AAAAAAAAAPM/y8WK62MfJH8/s1600/Chicago_Conference_eBrochure_2015_thumb.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-WNA5Pc4irmc/VQAnRpDpnwI/AAAAAAAAAPM/y8WK62MfJH8/s1600/Chicago_Conference_eBrochure_2015_thumb.jpg" /></a>In this full-day class, I will show you how a modern BI program can help you track, analyze and improve business performance.<br /><br />With a strong focus on information, we will look at how new technologies and best practices have reshaped the way BI delivers business value.<br /><br />We will cover all <a href="http://blog.oaktonsoftware.com/2015/02/the-three-pillars-of-modern-bi_9.html">three pillars of Modern BI</a>, and also discuss organizational options, agile development and technology policies. <br /><br />I'll also be leading classes on <a href="http://events.tdwi.org/Events/Chicago-2015/Sessions/Tuesday/TDWI-Predictive-Analytics-Fundamentals.aspx">Predictive Analytics</a> (5/5/15) and <a href="http://events.tdwi.org/Events/Chicago-2015/Sessions/Wednesday/TDWI-Data-Visualization-Fundamentals.aspx">Data Visualization</a> (5/6/15).<br /><br /><b>Discount Code for Registration</b><br /><br />If you are planning to attend, <a href="https://www.etouches.com/ereg/newreg.php?eventid=115985">use this link to register</a>, and enter <b>Priority Code 111</b> for a 10% discount.<br /><br />Hope to see you there!<br /><br /><br /><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-16650624018054709132015-02-09T09:37:00.000-05:002016-03-02T16:42:32.342-05:00The Three Pillars of Modern BI<em>Data marts are no longer sufficient to meet the demands of a modern BI program. This post lays out a framework for delivering BI value in the modern era.</em><br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-lQ8jScfDNNc/VNjD-t2pNdI/AAAAAAAAAOk/Ou2S_pps6uw/s1600/Triangle%2Band%2BCircles-Reporting.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="397" src="https://1.bp.blogspot.com/-lQ8jScfDNNc/VNjD-t2pNdI/AAAAAAAAAOk/Ou2S_pps6uw/s1600/Triangle%2Band%2BCircles-Reporting.jpg" width="400" /></a></div><br />The technologies and processes that help us deliver BI services have advanced by leaps and bounds over the last two decades. A modern BI program provides three perspectives on business performance, roughly corresponding to the past, present, and future.<br /><br /><h2 id="olapandreporting">OLAP and Reporting</h2><br />OLAP and reporting services (or simply "OLAP") provide the "official record" of what has happened in the past--the canonical log of business activity.<br /><br />This pillar of the modern BI program helps the business understand "where we've been." The typical information products provided in this service area include:<br /><br /><ul><li><strong>Reports</strong> provide pre-built, parameterized access to business information</li><li><strong>Analysis</strong> provides the ability to explore the official record of business activity by slicing, dicing, drilling, and so forth (OLAP)</li><li><strong><em>Ad hoc</em> query</strong> capabilities allow people to ask their own questions about the official record, even if a pre-defined report or analysis does not exist.</li></ul><br />For people in the business, these kinds of information products come to define this pillar of the BI program. There is also a fourth important information product of which the business may have less direct awareness:<br /><br /><ul><li><strong>The integrated record</strong> of business activities, aka "Data Marts." This record combines, standardizes and organizes information for business consumption.</li></ul><br />Essential in delivering the first three kinds of information products, this component was the primary focus in the early years of BI, when we called the practice "data warehousing." Since then, the discipline has changed and expanded. But it is still essential that the BI program provide the ability to understand the past.<br /><br /><h2 id="performancemanagement">Performance management</h2><br />Performance management services provide real-time status on key performance indicators, as well as performance versus goals. <br /><br />KPI's and goals are carefully matched to the viewer's role and linked to business objectives. Goals communicate expectations, while KPI's communicate achievement of expectations.<br /><br />If OLAP is about "where we have been," then performance management is about "where we are now." Typical information products in this BI service area include:<br /><br /><ul><li><strong>Dashboards</strong> provide real-time or near-real-time status of KPI's</li><li><strong>Scorecards</strong> which communicate progress vs. goals</li></ul><br />Information on dashboards and scorecards is carefully tailored for the user or functional area. Metrics are chosen for relevance and actionability, linked to business strategy, and balanced to reflect a holistic picture of performance.<br /><br />While this service area can stand on its own, performance management solutions are more powerful when people can dig into the KPI's on their dashboards. This capability is enabled by integrating performance management services with OLAP services.<br /><br /><h2 id="businessanalytics">Business analytics</h2><br />Analytic services probe deeply into data, providing insight into cause and effect, making predictions about what will happen in the future, and prescribing a course of action.<br /><br />While analytic services draw on data from the past, their objective is to influence the future. Typical information products in this service area include:<br /><br /><ul><li><strong>Analytic models</strong> that make sense of activities or predict future events</li><li><strong>Simulations</strong> that allow the manipulation of variables to study their potential impact on results</li><li><b>Visualizations</b> that communicate analytic insights</li><li><strong>Analytic metrics</strong> that assess current state and or future outcomes which are fed to OLAP, performance management, and OLTP applications</li></ul><br />Like the other pillars of modern BI, analytic services can exist alone but are more powerful in the presence of the other pillars. Prescriptive metrics, for example, are best presented directly on operational dashboards; useful analytic metrics can be recorded and tracked over time in data marts.<br /><br /><h2 id="bringingitalltogether">Delivering Modern BI</h2>In each area of the business, these capabilities should be balanced and tied together. Centralized management of all three pillars is not required, but they should be coordinated and integrated. A shared roadmap should lay out their planned evolution.<br /><br />Your objective is business impact, and <a href="http://blog.oaktonsoftware.com/2015/03/bi-and-path-to-business-value.html">my next post</a> shows how these services deliver it. <br /><br /><h2>Learn More</h2><div><ul><li><a href="http://blog.oaktonsoftware.com/2015/03/bi-and-path-to-business-value.html">BI and the Path to Business Value</a> (3/20/2015) explores how the three pillars of modern BI enable business impact.</li><li><a href="http://blog.oaktonsoftware.com/2014/09/business-intelligence-in-modern-era.html">Business Intelligence in the Modern Era</a> (9/8/2014) provides a definition of Business Intelligence for modern information assets.</li></ul></div><div><br /></div><em>For more on managing the Modern BI program, check out Chris's latest course: </em><b>Business Information and Modern BI</b>. <em>Check the sidebar for upcoming dates.</em> <div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-66599346113692101032014-09-18T11:10:00.002-04:002015-01-24T14:21:15.780-05:00Business Intelligence in the Modern Era<i>This post offers an updated definition for BI, and suggests that you don't have to think about it as a box on an org chart.</i><br /><br />BI has changed a lot in the last two decades. Technologies and best practices have evolved, and we've found more ways in which a BI program can deliver value. Some of these innovations have occurred outside of IT or the BI Competency Centers that many businesses have established. At the same time, many organizations are moving to make business units autonomous.<br /><br />These changes lead many people to ask <i>what exactly is BI? </i>Is it a box on the org chart? Does it include analytics that were never done by IT? How do data governance and master data management fit in?<br /><br /><b>Business Intelligence Defined</b><br /><br /><div class="MsoNormal" style="margin: 3pt 0in 6pt;">I define BI as follows:</div><blockquote class="tr_bq" style="margin: 3pt 0in 6pt;"><div style="text-align: center;"><b>Business Intelligence:</b></div><div style="text-align: center;">The use of information to improve business performance</div><div style="text-align: center;">- Chris Adamson</div></blockquote><div class="MsoNormal" style="margin: 3pt 0in 6pt;"><br />The first thing to note about this definition is that it does not address any specific technologies or methods. These aspects change over time, and they certainly influence what we may be able to achieve. But the objective is always to provide business value.<br /><br />Secondly, note that this definition is not beholden to the boundaries of a departmental structure. Regardless of who develops, supports or uses solutions, it's all considered BI.<br /><br />Let's take a quick look at both these aspects.<br /><br /><b>BI Services and Activities</b><br /><br />The reason we commit resources to BI programs is simple: we intend to use information to deliver some kind of business value. The definition has been crafted to cover any activities that support this objective. It can be used to describe a variety of activities that provide business value, both old and new.<br /><br />Among the older activities it covers:<br /><ul><li>Traditional reporting, OLAP and <i>ad hoc</i> functions</li><li>Dashboards and scorecards</li><li>Traditional data warehouses and/or data marts</li><li>Data integration services</li></ul></div><div class="MsoNormal" style="margin: 3pt 0in 6pt;">At the same time, some newer uses of information are covered:<br /><ul><li>Business analytics and predictive analytic</li><li>Master data management</li><li>Data governance</li><li>Virtualization and federation services</li></ul>The definition also covers activities that some people think of as on "the other side of the fence" from BI:<br /><ul><li>Transaction processing</li></ul>That's intentional; transaction processing manufactures much of the "raw material" that BI programs attempt to leverage. When we plan an operational solution, we should be thinking about these downstream uses.<br /><br /><b>BI and the Org Chart</b><br /><br />While you may have a group responsible for BI program management, it is important to understand that the scope of BI reaches well beyond this group. The delivery of business benefit from information impacts the entire organization.<br /><br />Some of the functional areas that participate in BI are:<br /><br /><ul><li><b>Business units </b>All of the value from BI happens within business areas that use information. This is where decisions are made and impacts are realized. For many businesses, responsibility for development of BI solutions also lies in business areas. This is particularly the case for analytics, but also increasingly for the traditional forms of BI.</li><li><b>BI Competency Centers </b>Whether part of IT or external to it, many organizations have established a centralized resource for planning and overseeing the development of traditional forms of BI, such as data marts, dashboards or scorecards. In some cases, these centers have become focused on providing advisory services to business units that create and manage their own solutions.</li><li><b>Analytic Competency Centers </b>Business analytics often begins within business areas such as marketing or risk management. Analytic competency centers are developed to help other areas of the business leverage information in a similar manner. Whether part of the BI competency center or distinct from it, this is also a core BI function.</li><li><b>IT</b> At a minimum, IT has some responsibility for the technical infrastructure on top of which information systems are built -- networks, computers and the services that keep them up and running. IT may also have responsibility for some of the business applications and data management solutions.</li></ul><div>Regardless of how your organization structure divvies up these responsibilities, BI is the sum total of these activities, and not the domain of a particular group or department. A business strategy to create value through information cuts across many departments. It cannot be planned or executed in isolation.</div></div><div class="MsoNormal" style="margin: 3pt 0in 6pt;"><b>The Future of BI</b><br /><br />We're not far from an age where BI is not a separate part of our information architecture. We're not there yet, but several trends have us on this path:<br /><br /><ul><li>Focus on the future value and re-use of data managed by operational applications</li><li>Commitment to data governance</li><li>Maturation of master data management solutions</li><li>Technological advances in data management and information access</li></ul><br />When we finally arrive at a unified information architecture, the definition of BI will still hold. We will be closer to delivering on its promise than ever before.<br /><br />And, without a doubt, we will have come up with ways of using information to deliver value that have not even be thought of today.<br /><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-49853319748466033562014-02-10T12:33:00.001-05:002016-12-02T12:28:42.533-05:00Document Information Requirements Graphically With BDM Diagrams<i>BI teams often struggle to keep the business engaged, especially during requirements analysis. This post looks at a graphical technique for documenting information requirements -- one that business people will read <u>and</u> respond to.</i><br /><br />Keeping the business engaged is one of the keys to a successful BI program. One technique I have found to be very helpful on this front is Laura Reeves's Business Dimensional Model (BDM).<br /><br />The BDM is a technique for documenting information requirements. Before I explain the BDM, a few words on the requirements themselves.<br /><br /><b>Information Requirements</b><br /><br />Before you can design a dimensional model, you need to capture the business requirements that it will support. The most successful projects capture business requirements by working directly with people in the business, often through interviews or requirements sessions.<br /><br />In <a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320" target="_blank">my book</a>, I suggest that as you organize your information requirements by business function. You then state them in simple form: as a group of metrics and their associated dimensionality. <br /><br />For example, a set of interviews about the taking orders might boil down to a requirements statement such as:<br /><ul><li><b>Order Information</b> by <b>order date</b>, <b>order line</b>, <b>salesperson</b>, <b>customer</b> and <b>product</b>.</li></ul>The metrics that comprise the group are then fully documented. For example, "Order Information" is further supported with documentation of:<br /><ul><li>Order dollars</li><li>Order quantity</li><li>Cost dollars</li><li>Gross margin dollars</li><li>Gross margin rate</li></ul>Relevant hierarchies in the dimensions should also be specified. For example, "Product" might be described as:<br /><ul><li>All Products <span style="font-family: "wingdings";">à</span> Category <span style="font-family: "wingdings";">à</span> Brand <span style="font-family: "wingdings";">à</span> Product </li></ul>Finally, the major dimensions are cross-referenced to the metric groups in a conformance matrix. <br /><br />These information requirements then drive solution modeling. The next step is to develop a top level dimensional model, and then a detailed database design.<br /><br />(For more on developing and documenting requirements, including a fully fleshed out example, see my book -- it's listed at the end of this post.)<br /><br /><b>Getting People to Read It</b><br /><br />When it comes to information requirements, you must ensure that the business stakeholders review and respond. (Better still is to involve the business in the identification and documentation process.)<br /><br />In the book <a href="http://www.amazon.com/gp/product/0470176385/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0470176385&linkCode=as2&tag=oaktonsoftwar-20" target="_blank">A Manager's Guide to Data Warehousing</a><img alt="" border="0" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=0470176385" height="1" style="border: none !important; margin: 0px !important;" width="1" />, Laura Reeves provides a graphical technique that helps keep the business's attention. She calls it the "Business Dimensional Model (BDM)."<br /><br />This technique integrates nicely with the approach I've outlined above.<br /><br />Each group of metrics is depicted in a simple diagram, with the metric group in the center and the major dimensions arrayed around it in circles. <br /><br />For example, the Order Information metric group above might be documented thusly:<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-Re13UDY_ECY/UvjjMMR_r_I/AAAAAAAAAMI/MQ9UheBCfik/s1600/BDM+Fact+Group.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://2.bp.blogspot.com/-Re13UDY_ECY/UvjjMMR_r_I/AAAAAAAAAMI/MQ9UheBCfik/s1600/BDM+Fact+Group.jpg" width="395" /></a></div><br />Within each circle, the underlined text identifies a dimension. Beneath the dimension, the level of detail applicable in the metric group is listed.<br /><br />Additional illustrations document the dimension hierarchies. For example, the product dimension from the picture above might be documented like this:<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-SENxRVl7vGY/UvjjZqig3NI/AAAAAAAAAMQ/VJKl3HZOeUQ/s1600/BDM+Dimension.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="326" src="https://3.bp.blogspot.com/-SENxRVl7vGY/UvjjZqig3NI/AAAAAAAAAMQ/VJKl3HZOeUQ/s1600/BDM+Dimension.jpg" width="400" /></a></div><br /><br />The most detailed level of the dimension is shaded darkly. The arrows indicate hierarchies, going from summarized to detailed. Elements that will drive Type 2 slow changes have a shadow. Separate symbols (not shown) are used for junk dimensions, other derived elements, and future attributes.<br /><br /><b>People Like Pictures</b><br /><br />I've found that using BDM diagrams <i>dramatically increases the participation of business stakeholders</i>. People look at BDM diagrams, understand them, and react to them -- often with great enthusiasm. That's a powerful aid in refining and validating your requirements.<br /><br />These diagrams are also <i>easy to produce</i> using the built in drawing tools that come with basic productivity software. This means you can often get business stakeholders to <i>participate in their creation</i>. For example, the pictures above were created in Microsoft PowerPoint using basic shapes and Smart Shapes.<br /><br />Lastly, the ability to produce these diagrams using basic productivity software means they are easy to incorporate in <i>the best format for this kind of documentation:</i> the presentation. I find the presentation format is far more likely to be reviewed than a word processing document. (More on this topic in a future post.) <br /><br /><b>Further Reading</b><br /><br />As I said back in 2009, I am a big fan of Laura Reeves's approach to requirements and design. As you can see, there is a natural affinity between the BDM and the techniques I've talked about in the past. I encourage readers to check out her book (see below).<br /><br />More info about requirements and documentation can be found on this blog. Have a look at these posts:<br /><ul><li>I first mentioned Laura's book in this blog back this post from 2009: <a href="http://blog.oaktonsoftware.com/2009/07/recommended-books-on-data-warehouse.html">Recommended Books on the Data Warehouse Lifecycle</a> (July 27, 2009)</li><li>For an explanation of the three levels of a dimensional model (Requirements, Top Level Design and Detailed Design) see <a href="http://blog.oaktonsoftware.com/2011/07/dimensional-modelers-do-not-focus-on.html">Dimensional Modelers Do Not Focus on Logical vs. Physical</a> (July 5, 2011)</li><li>For an example of a conformance matrix, see <a href="http://blog.oaktonsoftware.com/2012/06/conformance-matrix.html">The Conformance Matrix</a> (June 5, 2012)</li><li>For an explanation of the type 2 slow change, see <a href="http://blog.oaktonsoftware.com/2007/10/for-slowly-changing-dimensions-change.html">For Slowly Changing Dimensions, Change is Relative</a> (October 9, 2007)</li></ul>You can read more about the process of identifying information requirements in these books:<br /><ul><li>For a full explanation of the BDM, see Laura Reeves's <a href="http://www.amazon.com/gp/product/0470176385/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0470176385&linkCode=as2&tag=oaktonsoftwar-20" target="_blank">A Manager's Guide to Data Warehousing</a><img alt="" border="0" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=0470176385" height="1" style="border: none !important; margin: 0px !important;" width="1" /> (Wiley, 2009). The BDM is covered in Chapter 7, "Modeling The Data For Your Business"</li></ul><ul><li>The examples in this post are drawn from my book, <a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320" target="_blank">Star Schema: The Complete Reference</a> (McGraw-Hill, 2010) A more fleshed out explanation of tasks and deliverables, with examples, cab be found in Chapter 18, "How To Design and Document a Dimensional Model." The examples from this post come from Figure 18-4 (which in turn builds on the star in Figures 3-3, and the hierarchies in Figure 7-3).</li></ul><div><i>You can help support this blog by using the links above to purchase these books from Amazon.com.</i><br /><i><br /></i><i>[Edited 2/13/14 - Corrected the links, thank you for the emails.]</i></div><div><br /></div><br /><div></div><br /><div><br /></div><div><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-21980678914851378362013-11-14T10:19:00.000-05:002013-11-14T10:21:30.974-05:00Facebook's Ken Rudin on AnalyticsIf you are interested in how business analytics impact your BI program, carve out forty-five minutes of time to watch Ken Rudin's recent TDWI keynote: "Big Data, Bigger Impact." The video is embedded below.<br /><br />Rudin is the director of analytics at Facebook. In his presentation, he discusses several topics that are of interest to readers of this blog. Among them:<br /><ul><li>Big data technology should be used to extend your traditional BI solution, not replace it. Facebook has realized this, and is working to bring in relational technology to answer traditional business questions.</li></ul><ul><li>Successful analytics programs bring together centrally managed core data metrics with a variety of data that is not centrally managed. Rudin shares different ways he has been able to make this happen.</li></ul><ul><li>A similar balance can be attained with your organizational structure. Use of "embedded analysts" provides the business benefits of decentralization, while maintaining the efficiencies and scale advantages of a centralized program.</li></ul>These are just a few of the points made during his talk. If you don't have the time to watch it now, bookmark this page for later.<br /><br />You'll also want to check out Wayne Eckerson's latest book, <a href="http://www.amazon.com/gp/product/1935504347/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=1935504347&linkCode=as2&tag=oaktonsoftwar-20" target="_blank">Secrets of Analytical Leaders.</a><img alt="" border="0" height="1" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=1935504347" style="border: none !important; margin: 0px !important;" width="1" /><img alt="" border="0" height="1" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=1935504347" style="border: none !important; margin: 0px !important;" width="1" /> (Details below.)<br /><br /><div style="text-align: center;"><div style="text-align: left;"><b>Big Data, Bigger Impact</b></div></div><div style="text-align: center;"><div style="text-align: left;">Ken Rudin</div></div><div style="text-align: center;"><div style="text-align: left;">TDWI World Conference, Chicago 5/6/2013</div></div><div><div style="text-align: center;"><br /></div><div style="text-align: center;"><iframe allowfullscreen="" frameborder="0" height="225" src="//www.youtube.com/embed/xKrw2TKfj4w?rel=0" width="400"></iframe><br /></div><div style="text-align: center;"><br /></div><br /><b>Recommended Reading</b><br /><b><br /></b><a href="http://www.amazon.com/gp/product/1935504347/ref=as_li_ss_il?ie=UTF8&camp=1789&creative=390957&creativeASIN=1935504347&linkCode=as2&tag=oaktonsoftwar-20" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" target="_blank"><img border="0" src="http://ws-na.amazon-adsystem.com/widgets/q?_encoding=UTF8&ASIN=1935504347&Format=_SL160_&ID=AsinImage&MarketPlace=US&ServiceVersion=20070822&WS=1&tag=oaktonsoftwar-20" /></a>Wayne Eckerson's excellent book, <a href="http://www.amazon.com/gp/product/1935504347/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=1935504347&linkCode=as2&tag=oaktonsoftwar-20" target="_blank">Secrets of Analytical Leaders,</a><img alt="" border="0" height="1" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=1935504347" style="border: none !important; margin: 0px !important;" width="1" /><img alt="" border="0" height="1" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=1935504347" style="border: none !important; margin: 0px !important;" width="1" />features more insights from Ken Rudin and others.<br /><br />I highly recommend this book if you are interested in analytics. <br /><br />Get it from Amazon.com in <a href="http://www.amazon.com/gp/product/1935504347/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=1935504347&linkCode=as2&tag=oaktonsoftwar-20" target="_blank">paperback</a> or <a href="http://www.amazon.com/gp/product/B009VI87WA/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=B009VI87WA&linkCode=as2&tag=oaktonsoftwar-20" target="_blank">Kindle</a> editions.<br /><br /><div><div class="separator" style="clear: both; text-align: center;"></div><img alt="" border="0" height="1" src="http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=1935504347" style="border: none !important; margin: 0px !important;" width="1" /></div><div><br /></div><div></div></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-12157446094881950102013-09-25T12:57:00.002-04:002015-12-24T12:50:35.795-05:00Optimizing warehouse data for business analytics<i>Business analytics often integrate information from your data warehouse with other sources of data. This post looks at the best practices of warehouse design that make this possible.</i><br /><br />I receive a lot of questions regarding the best way to structure warehouse data to support an analytics program. The answer is simple: <b>follow the same best practices you've already learned</b>.<br /><br />I'll cover these practices from a dimensional modeling perspective. Keep in mind that they apply in any data warehouse, including those modeled in third normal form.<br /><b><br /></b><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-RNr07z9RfjI/UkL4m72G2AI/AAAAAAAAALU/ozUfHhKvM4c/s1600/AnalyticsDataVenn.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="307" src="http://2.bp.blogspot.com/-RNr07z9RfjI/UkL4m72G2AI/AAAAAAAAALU/ozUfHhKvM4c/s320/AnalyticsDataVenn.png" width="320" /></a></div><b>1. Store Granular Facts</b><br /><br />Analytic modelers often choose sources external to the data warehouse, even when the warehouse seems to contain relevant data. The number one reason for this is insufficient detail. The warehouse contains summarized data; the analytic model requires detail.<br /><br />In this situation, the analytic modeler has no choice but to look elsewhere. Worse, she may be forced to build redundant processes to transform source data and compile history. Luckily, this is not a failure of warehouse design principles; its a failure to follow standard best practices.<br /><br />Best practices of dimensional design dictate that we set the grain of <b>base fact tables</b> at the <b>lowest level of detail possible</b>. Need a daily summary of sales? Store the individual order lines. Asked to track the cost of tips? Store detail about each leg.<br /><div><br /></div><div>Dimensional solutions <i>can</i> contain summarized data. This takes the form of cubes, aggregates, or derived schemas. But these summaries should be derived <b>exclusively from detailed data that also lives in the warehouse</b>.</div><div><br /></div><div>Like all rules, this rule has exceptions. There are times when the cost/benefit calculus is such that it doesn't make sense to house highly granular indefinitely. But more often than not, summary data is stored simply because basic best practices were not followed.<br /><br /><b>2. Build “Wide” Dimensions</b><br /><b><br /></b>The more attributes there are in your reference data (aka dimensions), the more useful source material there is for analytic discovery. So build dimensions that are <b>full of attributes</b>,<b> </b> as many as you can find.<br /><br />If the grain of your fact table gives the analytics team “observations” to work on, the dimensions give them “variables.” And the more variables there are, the better the odds of finding useful associations, correlations, or influences.<br /><br />Luckily, this too is already a best practice. Unfortunately, it is one that is often misunderstood and violated. Misguided modelers frequently break things down into the essential pieces only, or model just to specific requirements. </div><div><br /></div><b>3. Track Changes to Reference Data (and Use Effective Dating)</b><br /><br />When reference data changes, too many dimensional models default to updating corresponding dimensions, because it is easier.<br /><br />For example, suppose your company re-brands a product. It's still the same product, but with a new name. You may be tempted to simply update the reference data in your data warehouse. This is easier than tracking changes. It may even seem to make business sense, because 90% of your reports require this-year-versus-last comparison by product name.<br /><br />Unfortunately, some very important analysis may require understanding how consumer behavior correlates with the product name. You've lost this in your data set. Best practices help avoid these problems.<br /><br />Dimensional models should <b>track the change history of reference data.</b> In dimensional speak, this means application of type 2 slow changes as a rule. This preserves the historic context of every fact recorded in the fact table.<br /><br />In addition, every row in a dimension table should <b>track "effective" and "expiration" dates</b>, as well as a <b>flag rows that are current</b>. This enables the delivery of type 1 behavior (the current value) even as we store type 2 behavior. From an analytic perspective, it also enables useful "what if" analysis.<br /><br />As with all rules, again there are exceptions. In some cases, there may be good reason not to respond to changes in reference data by tracking history. But more often than not, type 1 responses are chosen for the wrong reason: because they are easier to implement.<br /><br /><b>4. Record Identifying Information, Including Alternate Identifiers</b><br /><br />Good dimensional models allow us to trace back to the original source data. To do this, <b>include transaction identifiers</b> (real or manufactured) in fact tables, and <b>maintain identifiers from source systems</b> in dimension tables (these are called "natural keys").<br /><br />Some of this is just plain necessary in order to get a dimensional schema loaded. For example, if we are tracking changes to a product name in a dimension, we may have multiple rows for a given product. The product's identifier is not a unique identifier, but we must have access to it. If we don't, it would become impossible to load a fact into the fact table.<br /><br />Identifying information is also essential for business analytics. Data from the warehouse is likely to be combined with data that comes from other places. These identifiers are the connectors that allow analytic modelers to do this. Without them, it may become necessary to bypass the warehouse.<br /><br />Your analytic efforts, however, may require blending new data with your enterprise data. And that new data may not come with handy identifiers. You have a better chance blending it with enterprise data if your warehouse also includes <b>alternate identifiers</b>, which can be used to do matching. Include things like phone numbers, email addresses, geographic coordinates—anything that will give the analytics effort a fighting chance of linking up data sources.<br /><br /><b>Summary</b><br /><br />If you've been following the best practices of dimensional modeling, you've produced an asset that maximized value for analytic modelers: <br /><br /><ul><li>You have granular, detailed event data.</li><li>You have rich, detailed reference data.</li><li>You are tracking and time-stamping changes to reference data. </li><li>You've got transaction identifiers, business keys, and alternate identifiers. </li></ul><br />It also goes without saying that <b>conformed dimensions</b> are crucial if you hope to sustain a program of business analytics.<br /><br />Of course, there are other considerations that may cause an analytic modeler to turn her back on the data warehouse. Latency issues, for example, may steer them to operational solutions. Accessibility and procedural issues, too, may get in the way of the analytic process.<br /><br />But from a database design perspective, the message is simple: <b>follow those best practices!</b><br /><br /><b>Further Reading</b><br /><br /><span style="background-color: white; font-family: inherit; line-height: 18.90625px;">You can also read more in prior posts. For example:</span><br /><ul style="background-color: white; line-height: 18.90625px;"><li><span style="font-family: inherit;"><a href="http://blog.oaktonsoftware.com/2009/12/rule-1-state-your-grain.html">Rule 1: State Your Grain </a>(December 9, 2009) covers the fundamentals of grain</span></li><li><span style="font-family: inherit;"><a href="http://blog.oaktonsoftware.com/2011/01/build-high-resultion-stars.html" style="color: #cc6633;">Build High Resolution Stars</a> (January 7, 2011) discusses the importance of setting grain at the lowest level possible</span></li><li><span style="font-family: inherit;"><a href="http://blog.oaktonsoftware.com/2007/10/for-slowly-changing-dimensions-change.html">For Slowly Changing Dimensions, Change is Relative</a> (October 9, 2007) covers type 1 vs type 2 processing and surrogate keys vs. natural keys.</span></li><li><span style="font-family: inherit;"><a href="http://blog.oaktonsoftware.com/2008/03/responding-to-star-schema-detractors.html">Responding to Star Schema Detractors with Timestamps</a> (March 12, 2008) covers the use of effective and expiration dates with Type 2 slow changes.</span></li><li><span style="font-family: inherit;"><a href="http://blog.oaktonsoftware.com/2009/05/do-i-really-need-surrogate-keys.html">Do I Really Need Surrogate Keys</a> (May 20, 2009) covers the fundamentals of business keys vs. warehouse keys</span></li><li><span style="font-family: inherit;"><a href="http://blog.oaktonsoftware.com/2011/10/creating-transaction-identifiers-for.html">Creating transaction identifiers for fact tables</a> (October 17, 2011) covers real and manufactured transaction identifiers</span></li></ul><span style="font-family: inherit;">You can also read more in my book, </span><a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320" style="background-color: white; color: #cc6633; line-height: 18.90625px;" target="_blank">Star Schema: The Complete Reference</a><span style="font-family: inherit;">. </span><span style="background-color: white; line-height: 18.90625px;">If you use the links on this page to</span><span style="background-color: white; line-height: 18.90625px;"> </span><a href="ttp://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320" style="line-height: 18.90625px;" target="_blank">pick up a copy on Amazon</a><span style="background-color: white; line-height: 18.90625px;">, you will be helping support this blog. </span><br /><span style="background-color: white; line-height: 18.90625px;"><br /></span><span style="background-color: white; font-family: inherit; line-height: 18.90625px;"> It covers the best practices of dimensional design in depth. For example:</span><br /><br /><ul style="background-color: white;"><li><span style="font-family: inherit; line-height: 18.90625px;">Grain, identifiers,</span><span style="font-family: inherit; line-height: 18.90625px;"> keys</span><span style="font-family: inherit;"><span style="line-height: 18.90625px;"> and </span></span><span style="font-family: inherit; line-height: 18.90625px;">basic slow change </span><span style="line-height: 18.90625px;">techniques</span><span style="font-family: inherit;"><span style="line-height: 18.90625px;"> are covered in Chapter 3, "Stars and Cubes"</span></span></li><li><span style="font-family: inherit;"><span style="line-height: 18.90625px;">The place of summary data is covered in Chapter 14, "Derived Schemas" and Chapter 15, "Aggregates"</span></span></li><li><span style="font-family: inherit;"><span style="line-height: 18.90625px;">Conformance is covered in Chapter 5, "Conformed Dimensions"</span></span></li><li><span style="font-family: inherit;"><span style="line-height: 18.90625px;">Advanced slow change techniques are explored in Chapter 8, "More Slow Change Techniques"</span></span></li></ul><br /><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-19000782804833711422013-07-17T16:03:00.001-04:002013-07-18T11:26:01.324-04:00Business Analytics and Dimensional Data<i>Readers of this blog frequently ask about the relationship of business analytics to the dimensional data that is recorded in data marts and the data warehouse.</i><br /><i><br /></i> <i>Business analytics operate on data that often does not come from the data warehouse. The </i>value<i> of business analytics, however, is measured by its impact on business metrics that </i><u>are tracked</u><i> in the data warehouse. </i><br /><i><br /></i> <i>Business analytics may also help adjust our notion of which metrics matter the most.</i><br /><br /><b>The Data Warehouse and Dimensional Data</b><br /><br />The <i>dimensional model</i> is the focal point of business information in the data warehouse. It describes how we track business activities and measure business performance. It may also be the foundation for a performance management program that links metrics to business goals.<br /><br /><i>Dimensional data</i> is the definitive record of what matters to the business about activities and status. Clearly defined performance indicators (facts) are recorded consistently and cross referenced with standardized and conformed reference data (dimensions).<br /><br />In this post, when I talk about "the data warehouse," I will have this dimensional data in mind.<br /><br /><b>Business Analytics</b><br /><br /><i>Business analytics</i> seek to provide new insight into business activities. Analytics do not always operate on business metrics, and they don't rely exclusively on information form the data warehouse. Dimensional information <i>may</i> be an input, but other sources of data are also drawn upon.<br /><br />The <i>outputs</i> of business analytics, however, aim directly at the metrics tracked by our dimensional models. Insights from analytics are used by people to move key metrics in the desired directions. These results are called <i>impacts</i>.<br /><br />Business analytics may also help in another way. Sometimes, they help us determine which metrics are actually the most important.<br /><br />A great illustration of these dynamics can be found in the business of Major League Baseball. (If you don't follow baseball, don't worry. You don't have to understand baseball to follow this example.)<br /><br /><b>Metrics in Baseball</b><br /><br />Major league baseball has long been in the business of measurement. Followers of the game are familiar with the "box score" that summarizes each game, "standings" that illustrate the relative performance of teams, and numerous statistics that describe the performance of each player.<br /><br /><a href="http://www.amazon.com/gp/product/0393338398/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0393338398&linkCode=as2&tag=oaktonsoftwar-20%22%3EMoneyball%3C/a%3E%3Cimg%20src=%22http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=0393338398%22%20width=%221%22%20height=%221%22%20border=%220%22%20alt=%22%22%20style=%22border:none%20!important;%20margin:0px%20!important;%22%20/%3E" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;" target="_blank"><img border="0" src="http://ws-na.amazon-adsystem.com/widgets/q?_encoding=UTF8&ASIN=0393338398&Format=_SL160_&ID=AsinImage&MarketPlace=US&ServiceVersion=20070822&WS=1&tag=oaktonsoftwar-20" /></a>These metrics have precise definitions and have been recorded consistently for almost 150 years.<sup>1</sup> Like the metrics in your data warehouse, they are tracked systematically. Professional baseball teams can also set goals for these metrics and compare them to results, much like a scorecard in your performance management program.<br /><br />How does one improve these results? If you run a baseball team, part of the answer lies in how you choose players. In the book <a href="http://www.amazon.com/gp/product/0393338398/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0393338398&linkCode=as2&tag=oaktonsoftwar-20%22%3EMoneyball%3C/a%3E%3Cimg%20src=%22http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=0393338398%22%20width=%221%22%20height=%221%22%20border=%220%22%20alt=%22%22%20style=%22border:none%20!important;%20margin:0px%20!important;%22%20/%3E" target="_blank">Moneyball</a><sup>2</sup> Michael Lewis describes how the Oakland Athletics used a set of techniques known as sabermetrics<sup>3</sup> to make smarter choices about which players to add to their roster.<br /><br />These analytics allowed the A's to make smarter choices with measurable impact--improving performance and reducing costs. Analytics also motivated the A's to change the emphasis given to various metrics.<br /><br /><b>Business Analytics and the Oakland Athletics</b><br /><br />The traditional approach to selecting players was focused on long-held conventional wisdom about what makes a valuable player. For example, offensive value was generally held to derive from the ability to contact the baseball, and with a player's speed. These skills are at least partially evident in some of the standard baseball metrics -- things like the batting average, stolen bases, runs batted in and sacrifices.<br /><br />The Oakland A's looked to data to refine their notion of what a valuable player looks like. How do the things players do actually contribute to a win or loss? To do this, the A's went beyond the box scores and statistics -- beyond the data warehouse, so to speak.<br /><br />By studying every action that is a part of the game -- what players are on base, what kind of pitches are thrown, where the ball lands when it is hit, etc -- the A's realized they could be smarter about assessing how a player adds value. These business analytics led to several useful conclusions:<br /><ul><li>Batting averages don't tell the whole story about a player's ability to get on base; for example, they exclude walks.</li><li>Stolen bases don't always contribute to scoring; much depends on who comes to bat next.</li><li>Runs batted in tell as much about who hits before a player as they do about the player himself</li><li>Sacrifices, where an out is recorded but a runner advances, were found to contribute less to the outcome of a game than conventional wisdom held.</li></ul>You may or may not understand these conclusions, but here is the important thing: the analytics suggested that the A's could better assess a player's impact on winning games by turning away from conventional wisdom. Contact and speed are not the best predictors for winning game. "Patience at the plate" leads to better outcomes.<br /><br /><b>Impact for the A's</b><br /><br />By using these insights to make choices, the A's were able to select less expensive players who could make a more significant contribution to team results. These choices resulted in measurable improvement in many of the standard metrics of baseball--the win/loss ratio in particular. These insights also enabled them to deliver improved financial results.<br /><br />Analytics also helped the A's in another way: they refined exactly which metrics they should be tracking. For example, in assessing of offensive value, on base percentage should be emphasized over batting average. They also created some of their own metrics to track their performance over time.<br /><br /><b>The Impact of Analytics</b><br /><br />Business analytics tell us what to look for, what works, or what might happen. Examples are signs of impending churn, what makes a web site "sticky", patterns that might indicate fraud, and so forth.<br /><br />These insights, in turn, are applied in making business decisions. These choices provide valuable impact that can by tracking traditional business metrics. Examples include increased retention rates, reduced costs associated with fraud, and so forth.<br /><br />These impacts are the desired outcome of the analytic program. If the analytics don't have a demonstrable impact on metrics, they are not providing value.<br /><br />Business analytics can also help us revise our notion of <i>what to track </i>in our data warehouses, or which metrics to pay closest attention to. Number of calls to the support center, for example, may be less of an indicator of customer satisfaction than the average time to resolve an issue.<br /><br /><b>Conclusion</b><br /><br />As you expand the scope of your BI program to include analytics, remember that your desired outcome is a positive impact on results. Move the needle on business metrics, and the analytics have done their job.<br /><br /><i>Thanks to my colleague Mark Peco, for suggesting that I use </i>Moneyball<i> as a way to explain analytics without revealing the proprietary insights attained by my customers. </i><br /><br /><b>Notes</b><br /><br />[1] The box score and many of these statistics were established in the mid 1800's by a sports writer named <a href="http://en.wikipedia.org/wiki/Henry_Chadwick_(writer)" target="_blank">Henry Chadwick</a>.<br /><br />[2] <a href="http://www.amazon.com/gp/product/0393338398/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=0393338398&linkCode=as2&tag=oaktonsoftwar-20%22%3EMoneyball%3C/a%3E%3Cimg%20src=%22http://ir-na.amazon-adsystem.com/e/ir?t=oaktonsoftwar-20&l=as2&o=1&a=0393338398%22%20width=%221%22%20height=%221%22%20border=%220%22%20alt=%22%22%20style=%22border:none%20!important;%20margin:0px%20!important;%22%20/%3E" target="_blank">Moneyball</a> by Michael Lewis (Norton, 2011).<br /><br />[3] The Oakland A's are a high-profile example of the use of sabermetrics, but did not originate the concept. See <a href="http://en.wikipedia.org/wiki/Sabermetrics" target="_blank">wikipedia</a> for more information.<br /><br /><b>Related Posts</b><br /><br />See also these posts:<br /><br /><ul><li><a href="http://blog.oaktonsoftware.com/2013/06/in-era-of-big-data-dimensional-model-is.html">In the Era of Big Data, The Dimensional Model is Essential</a> (June 5, 2013)</li><li><a href="http://blog.oaktonsoftware.com/2013/04/the-role-of-dimensional-model-in-your.html">The Role of the Dimensional Model in Your BI Program</a> (April 30, 2013)</li></ul><br /><br /><br /><br /><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-83865885333440880672013-06-05T12:26:00.001-04:002013-06-05T12:26:53.072-04:00In the Era of Big Data, The Dimensional Model is Essential<i>Don't let the hype around big data lead you to believe your BI program is obsolete. </i><br /><br />I receive a lot of questions about "big data." Here is one:<br /><blockquote class="tr_bq">We have been doing data warehousing using Kimball method and dimensional modeling for several years and are very successful (thanks for your 3 books, btw). However, these days we hear a lot about Big Data Analytics, and people say that Big Data is the future trend of BI, and that it will replace data warehousing, etc.<br /><br />Personally I don't believe that Big Data is going to replace Data Warehousing but I guess that it may still bring certain value to BI. I'm wondering if you could share some thoughts.</blockquote><br />"Big data" is the never-ending quest to expand the ways in which our BI programs deliver business value.<br /><br />As we expand the scope of what we deliver to the business, we must be able to tie our discoveries back to business metrics and measure the impact of our decisions. The <i>dimensional model</i> is the glue that allows us to achieve this. <br /><br />Unless you plan to stop measuring your business, the dimensional model will remain essential to your BI program. The <i>data warehouse </i>remains relevant as a means to instantiate the information that supports this model. Reports of its death have been greatly exaggerated.<br /><br /><b>Big Data</b><br /><b><br /></b> "Big Data" is usually defined as a set of data management challenges known as "the three V's" -- volume, velocity and variety. These challenges are not new. Doug Laney first wrote about the three V's in 2001 -- twelve years ago.<sup>1</sup>And even before that, we were dealing with these problems.<br /><br /><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody><tr><td style="text-align: center;"><a href="http://3.bp.blogspot.com/-GMFCJ2OZZuA/Ua9OCK7DMyI/AAAAAAAAAKU/pdv9yxPcWvw/s1600/Ngc5194_remixed.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="200" src="http://3.bp.blogspot.com/-GMFCJ2OZZuA/Ua9OCK7DMyI/AAAAAAAAAKU/pdv9yxPcWvw/s200/Ngc5194_remixed.jpg" width="198" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Photo from NASA in <a href="http://commons.wikimedia.org/wiki/File:Ngc5194.jpg" target="_blank">public domain</a>.</td></tr></tbody></table>Consider the <i>first edition </i>of <i>The Data Warehouse Toolkit, </i>published by Ralph Kimball in 1996.<sup>2</sup> For many readers, his "grocery store" example provided their first exposure to the star schema. This schema captured <i>aggregated</i> data! The 21 GB fact table was a daily summary of sales, not a detailed record of point-of-sale transactions. Such a data set was presumably too large at the time.<br /><br />That's <i>volume</i>, the first V, circa 1996.<br /><br />In the same era, we were also dealing with <i>velocity</i> and <i>variety</i>. Many organizations were moving from monthly, weekly or daily batch loads to real-time or near-real time loads. Some were also working to establish linkages between dimensional data and information stored in document repositories.<br /><br /><b>New business questions</b><br /><br />As technology evolves, we are able to address an ever expanding set of business questions.<br /><br />Today, it is not unreasonable to expect the grocery store's data warehouse to have a record for every product that moves across the checkout scanner, measured in terabytes rather than gigabytes. With this level of detail, market basket analysis is possible, along with longitudinal study of customer behavior.<br /><br />But of course, the grocery store is now looking beyond sales to new analytic possibilities. These include tracking the movement of product through the supply and distribution process, capturing interaction behavior of on-line shoppers, and studying consumer sentiment.<br /><br /><b>We still measure our businesses</b><br /><br />What does this mean for the dimensional model? As I've posted before, a dimensional model represents how we <i>measure</i> the business. That's not something we're going to stop doing. Traditional business questions remain relevant, and the information that supports them is the core of our BI solution.<br /><br />At the same time, we need to be able to link this information to other types of data. For a variety of reasons (V-V-V), some of this information may not be stored in a relational format, and some may not be a part of the data warehouse.<br /><br />Making sense of all this data requires placing it in the context of our business objectives and activities. <br /><br />To do this, we must continue to understand and capture business metrics, record transaction identifiers, integrate around conformed dimensions, and maintain associated business keys. These are long established best practices of dimensional modeling.<br /><i><br /></i>By applying these dimensional techniques, we can (1) link insights from our analytics to business objectives and (2) measure the impact of resultant business decisions. If we don't do this, our big data analytics become a modern-day equivalent of the stove-pipe data mart.<br /><br /><b>The data warehouse</b><br /><br />The function of the data warehouse is to instantiate the data that supports measurement of the business. The dimensional model can be used toward this aim (think: star schema, cube.)<br /><br />The dimensional model also has other functions. It is used to express information requirements, to guide program scope, and to communicate with the business. Technology may eventually get us to a point where we can jettison the data warehouse on an enterprise scale,<sup>3 </sup>but these other functions will remain essential. In fact, their importance becomes elevated.<br /><br />In any architecture that moves away from <i>physically</i> integrated data, we need a framework that allows us to bring that data together with <i>semantic</i> consistency. This is one of the key functions of the dimensional model.<br /><br /><i>The dimensional model is the glue that is used to assemble business information from distributed data.</i><br /><br />Organizations that leverage a bus architecture <i>already understand this</i>. They routinely bring together information from separate physical data marts, a process supported by the dimensional principle of conformance. Wholesale elimination of the data warehouse takes things one step further.<br /><br /><b>Notes</b><br /><ol><li>Doug Laney's first published treatment of "The Three V's" can be found <a href="http://blogs.gartner.com/doug-laney/deja-vvvue-others-claiming-gartners-volume-velocity-variety-construct-for-big-data/" target="_blank">on his blog</a>.<br /></li><li>Now out of print, this discussion appeared in Chapter 2, "The Grocery Store." Insight into the big data challenges of 1996 can be found in Chapter 17, "The Future."</li><li>I think we are a long time away from being able to do this on an enterprise scale. When we do get there, it will be as much due to master data management as it is due to big data or virtualization technologies. I'll discuss virtualization in some future posts.</li></ol><b>More reading</b><br /><div><b><br /></b></div><div>Previous posts have dealt with this topic.<br /><ul><li><a href="http://blog.oaktonsoftware.com/2013/04/the-role-of-dimensional-model-in-your.html">The Role of the Dimensional Model in Your BI Program</a> (4/30/2013) details the four ways we use the dimensional model. Only one of these functions involves a database.</li></ul><ul><li>In <a href="http://blog.oaktonsoftware.com/2012/04/big-data-and-dimensional-modeling-video.html">Big Data and Dimensional Modeling</a> (4/20/2012) you can see me discuss the impact of new technologies on the data warehouse and the importance of the dimensional model.</li></ul><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-18464616039953450652013-04-30T12:05:00.000-04:002013-05-06T22:15:20.157-04:00The Role of the Dimensional Model in Your BI Program<i>The dimensional model delivers value long before a database is designed or built, and even when no data is ever stored dimensionally. While it is best known as a basis for database design, its other roles may have more important impacts on your BI program.</i><br /><div><div><br /></div><div>The dimensional model plays four key roles in Business Intelligence:</div><div><div><ol><li>The dimensional model is the ideal way define <b>requirements</b>, because it describes how the business is measured</li><li>The dimensional model is ideal for managing <b>scope</b> because it communicates to business people (functionality) and technical people (complexity) </li><li>The dimensional model is ideal as a basis for <b>data mart design</b> because it provides ease of use and high performance</li><li>The dimensional model is ideal as a <b>semantic layer</b> because it communicates in business terms</li></ol></div><div><b>Information Requirements</b></div><div><br />The dimensional model is best understood as an <i>information model,</i> rather than data model. It describes business activities the same way people do: as a system of measurement. This makes it the ideal form to express information needs, <i>regardless of how information will be stored</i>.<br /><br /></div><div><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; text-align: right;"><tbody><tr><td style="text-align: center;"><a href="http://2.bp.blogspot.com/-L-1AbIUjD_0/TXTbwLrLp1I/AAAAAAAAAB8/42Z2Ogi0_ZA/s1600/271893974_d7a19e1d02_m.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="200" src="http://2.bp.blogspot.com/-L-1AbIUjD_0/TXTbwLrLp1I/AAAAAAAAAB8/42Z2Ogi0_ZA/s200/271893974_d7a19e1d02_m.jpg" width="185" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Image by <a href="http://www.flickr.com/photos/gx9/271893974/in/photostream/" style="background-color: white; color: #cc6633; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: right;">Gravityx9</a><span style="background-color: white; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: right;"><br />licensed under </span><a href="http://creativecommons.org/licenses/by/2.0/deed.en" style="background-color: white; color: #cc6633; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18px; text-align: right;">Creative Commons 2.0</a></td></tr></tbody></table>A dimensional model defines business metrics or performance indicators in detail, and captures the attendant dimensional context. (For a refresher, see the post <a href="http://blog.oaktonsoftware.com/2010/04/basics-what-is-dimensional-model.html">What is a Dimensional Model</a> from 4/27/2010.) Metrics are grouped based on shared granularity, cross referenced to shared reference data, and traced to data sources.</div><div><br /></div><div>This representation is valuable because business questions are constantly changing. If you simply state them, you produce a model with limited shelf life. If you model answers to the question of today, you've provided perishable goods.</div><div><br /></div><div>A dimensional model establishes information requirements that endure, even as questions change. It provides a strong foundation for multiple facets of BI:</div><div><ul><li>Performance management, including <i>dashboards</i> and <i>scorecards</i></li><li>Analytic processing, including <i>OLAP</i> and <i>ad hoc analysis</i></li><li>Reporting, including both <i>enterprise and operational reports</i></li><li>Advanced analytics, including <i>business analytics, data mining</i> and <i>predictive analytics</i></li></ul></div><div>All these disciplines center on business metrics. It should be no surprise that when Howard Dresner coined the term Business Intelligence, <a href="http://www.b-eye-network.com/view/9007" target="_blank">his definition</a> referenced "facts and fact based systems." It's all about measurement.</div><div><br /></div><div><b>Program Roadmap and Project Scope</b></div><div><br /></div><div><div class="MsoBodyText">A dimensional model can be used to describe scope because it communicates to two important audiences.</div><div class="MsoBodyText"></div><ul><li><b>Business people: functionality </b> The dimensional model describes the measurement of a business process, reflecting how the process is evaluated by participants and observers. It communicates business capability.</li></ul><ul><li><b>Technical personnel: level of effort</b> A dimensional model has technical implications: it determines the data sources that must be integrated, how information must be integrated and cleansed, and how queries or reports can be built. In this respect, it communicates level of effort. </li></ul><div class="MsoBodyText">These dual perspectives make the dimensional design an ideal centerpiece for managing the roadmap for your BI program. Fully documented and mapped to data sources, a dimensional model can be divided into projects and prioritized. It is a blueprint that can be understood by all interested parties. A simple <a href="http://blog.oaktonsoftware.com/2012/06/conformance-matrix.html">conformance matrix</a> communicates both intended functionality and technical level of effort for each project.</div><div class="MsoBodyText"><br /></div><div class="MsoBodyText">At the project level, a dimensional design can be used as the basis for progress reporting. It can also serve as a nonambiguous arbiter of change requests. Changes that add data sources or impact grain, for example, are considered out of scope. This is particularly useful for organizations that employ iterative methodologies, but its simplicity makes it easy to reconcile with any development methodology.</div><!--EndFragment--></div><div><br /></div><div><b>Database Design</b></div><div><br /></div><div>The dimensional model is best known as the basis for database design. The term "star schema" is far more widely recognized than "dimensional model" (a fact that influenced the name of my <a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320" target="_blank">most recent book</a>).</div><div><br /></div><div>In fact, the dimensional model is <a href="http://blog.oaktonsoftware.com/2007/03/three-data-warehouse-architectures-that.html" target="_blank">the <i>de facto</i> standard for data mart design</a>, and many organizations use it to shape the entire data warehouse. It has an important place in the W.H. Inmon's <i>Corporate Information Factory</i>, Ralph Kimball <i>Dimensional Bus</i> architecture, and even in one-off data marts that lack an enterprise focus.</div><div><br /></div><div>Implemented in a relational database, the dimensional model becomes known as a star schema or snowflake. Implemented in a multidimensional database, it is known as a cube. These implementations offer numerous benefits. They are:</div><div><ul><li>Easily understandable by business people</li><li>Extraordinarily flexible from a reporting and analysis perspective</li><li>Adaptable to change</li><li>Capable of very high performance</li></ul></div><div><b>Presentation and the Semantic Layer</b></div><div><br /></div><div>A dimensional <i>representation</i> is the ideal way to present information to business people, regardless of how it is actually stored. It reflects how people <i>think</i> about the business, so it is used to organize the catalog of items they can call on for analysis.</div><div><br /></div><div>Many business intelligence tools are architected around this concept, allowing a <i>semantic layer</i> to sit between the user and database tables. The elements with which people can frame questions are categorized as facts and dimensions. One need not know what physical data structures lay beneath.</div><div><br /></div><div>Even the earliest incarnations of the semantic layer leveraged this notion. Many organizations used these tools to impose a dimensional view directly on top of operational data. Today, semantic layers are commonly linked to dimensional data marts.</div><div><br /></div><div>A dimensional representation of business activity is the starting point for a variety of BI activities:</div><div><ul><li>Building enterprise reports</li><li>Defining performance dashboards</li><li>Performing ad hoc analysis</li><li>Preparing data for an analytic model</li></ul></div><div>The concept of dimensional presentation is receiving renewed attention as federated solutions promise the construction of virtual solutions rather than physical ones.</div></div></div><div><br /></div><div><b>Further information</b></div><div><br />I briefly covered these four roles in an interview last year:</div><div><ul><li><a href="http://blog.oaktonsoftware.com/2012/04/big-data-and-dimensional-modeling-video.html">Big Data and Dimensional Modeling</a> (4/20/2012)</li></ul></div><div>Many of these themes have been discussed previously:</div><div><ul><li><a href="http://blog.oaktonsoftware.com/2007/03/three-data-warehouse-architectures-that.html">Three Data Warehouse Architectures that Use Star Schema</a> (3/26/2007)</li><li><a href="http://blog.oaktonsoftware.com/2007/07/drive-warehouse-strategy-with.html">Drive Warehouse Strategy with a Dimensional Model</a> (7/6/2007)</li><li><a href="http://blog.oaktonsoftware.com/2010/04/basics-what-is-dimensional-model.html">What is a Dimensional Model?</a> (4/27/2010)</li><li><a href="http://blog.oaktonsoftware.com/2012/06/conformance-matrix.html">The Conformance Matrix</a> (6/5/2012)</li></ul></div><div>Although I've touched on these topics before, I wanted to bring them together in a single article. In the coming months, I will refer back to these concepts as I address common questions about big data, agile BI and federation.</div><div><br /></div><div>In the mean time, please help support this blog by picking up a copy of <a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320">my latest book</a>.</div><div><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-5049844048226315512013-03-28T09:38:00.002-04:002013-03-28T09:38:53.812-04:00Where To Put Dates<i>A reader is trying to decide if certain dates should be modeled as dimensions of a fact table or as attributes of a dimension table.</i><br /><br /><blockquote class="tr_bq">I have two attributes that I'm really not sure where is the best place to place:'Account Open Date' and 'Account Close Date'. In my model, I have [Dim Accounts] as a dimension and [F transact] as a fact table containing accounts transactions. An account can have many transactions, so the dates have different cardinality than the transactions.<br /><ul><li>I thought to put the dates in the Accounts dimension, but this led to problems: difficulties in calculations related to those dates--like if I want to get the transactions of the accounts that opened in the 4th quarter of 2012, or to get the difference between the date of last transaction and the account opening date, and so on. In other words I can't benefit from the Date dimension and the hierarchies it contains.</li></ul><ul><li>So I though about placing those dates in the fact table, but what made me hesitate is that the granularity of those dates is higher than the fact table, so there will be a lot of redundancy.</li></ul><div style="text-align: right;">- Ahmad</div><div style="text-align: right;">Bethlehem, Palestine</div></blockquote><div style="text-align: right;"><br /></div>This is a common dilemma. Many of our most important dimensions come with a number of possible dates that describe them.<br /><br />Ahmad is thinking about this problem in the right way: how will my choice affect my ability to study the facts?<br /><br />It turns out that (1) this is not an either/or question, and (2) granularity is not an issue.<br /><br /><b>Dates that Describe Important Dimensions</b><br /><br /><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; text-align: right;"><tbody><tr><td style="text-align: center;"><a href="http://3.bp.blogspot.com/--s_jEy9-hvI/TZHHkfSf0FI/AAAAAAAAACA/PUoLEmOMcx0/s1600/4579775685_b8c9c16288_m.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="200" src="http://3.bp.blogspot.com/--s_jEy9-hvI/TZHHkfSf0FI/AAAAAAAAACA/PUoLEmOMcx0/s200/4579775685_b8c9c16288_m.jpg" width="200" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><div style="background-color: white; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18.90625px; text-align: right;"><span style="font-size: xx-small;">Image licensed via <a href="http://creativecommons.org/licenses/by/2.0/deed.en" style="color: #cc6633;" target="_blank">Creative Commons 2.0</a></span></div><div style="background-color: white; font-family: 'Trebuchet MS', Verdana, Arial, sans-serif; line-height: 18.90625px; text-align: right;"><span style="font-size: xx-small;">from <a href="http://www.flickr.com/photos/zooboing/4579775685/in/photostream/" style="color: #cc6633;" target="_blank">Patrick Hoesley</a></span></div><div><br /></div></td></tr></tbody></table>The dates are clearly useful dimension attributes. I suggest that you keep them in the dimension in one of two ways, which I will discuss in a moment.<br /><br />First, though, lets look at what happens if the dates are only represented as foreign keys in the fact table: <br /><br />If the dates are not stored in the dimension, the open and close date are <i>only</i> associated with the Account dimension through the fact table. The fact table only has records when transactions occur. So it becomes harder to find a list of open accounts, or to find the set of accounts that were active as of a particular date.<br /><br />An additional factless fact table may help here, but it is far more simple to store the dates in the dimension.<br /><br /><b>Date as Attribute vs Outrigger</b><br /><br />If plan to represent the dates in your dimension table, you have two choices. You can model the dates themselves as attributes, or you can model a pair of day keys in your account dimension. Either approach is acceptable. <br /><br />The first option does not expose the richness of your full day dimension for analytic usage, but it may be simpler to use for many business questions. Other questions (like your quarterly example) will require a bit more technical knowledge, but most BI tools help with this.<br /><br />The second option transforms your star into a (partial) snowflake. The day dimension becomes known as an "outrigger" when it connects to your account dimension. This allows you to explicitly leverage all the attributes of your Day dimension. The cost is some extra joins, which may be confusing and may also disrupt star-join optimization.<br /><br />Making the correct choice here involves balancing several perspectives:<br /><br /><ul><li>The business view and usability</li><li>The capabilities of your BI software front end</li><li>The capabilities of your DBMS back end software</li></ul><br /><b>Day Keys in the Fact Table</b><br /><br />Having said all that, it is <i>also</i> useful to represent at least one of these dates in the fact table. The account open date may be a good dimensional perspective for the analysis of facts.<br /><br />As you observed, this date has different cardinality than the transactions. The account open date for an account remains constant, even if it has dozens of transactions in your fact table. But the fact that it has low cardinality should <i>not</i> stop you from choosing it as a major dimension as your star!<br /><br />Your account transaction fact table may have a pair of day keys -- one for the date the account was opened, and one for the date of the transaction.<br /><br />If you choose to do this, the account dimension itself should include the open date. The outrigger solution is not necessary since your fact table has full access to the Day dimension.<br /><br />Note that I do not recommend this for your account closed date, because that date changes. Storing it as a key for every transaction against an account would require a lot of updates to fact table rows once the account becomes closed.<br /><br /><b>More Information</b><br /><br />I've touched on this topic in the past. In particular, see this post:<br /><br /><ul><li><a href="http://blog.oaktonsoftware.com/2011/05/dates-in-dimension-tables.html">Dates in Dimension Tables</a> (5/12/2011)</li></ul><br />Although I edited it out of Ahmad's question, he also cited an issue surrounding the use of NULL for accounts that do not have a closed date. On that topic, see this recent post:<br /><br /><ul><li><a href="http://blog.oaktonsoftware.com/2013/01/avoid-null-in-dimensions.html">Avoid NULL in Dimensions</a> (1/7/2013)</li></ul><br /><br /><b>Support this Blog</b><br /><br />I maintain this blog in my spare time. If you find it helpful, you can help support it by picking up a copy of my book: <a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320" target="_blank">Star Schema: The Complete Reference</a>.<br /><br />Use the links on this blog to get a copy of this or any of the other recommended books, and you will be helping to keep this effort going.<div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-41822002149870925612013-03-06T11:49:00.000-05:002013-03-06T11:49:29.833-05:00Learn Dimensional Modeling With Chris in 2013Several courses in Dimensional Modeling are already scheduled for 2013:<div><ul><li><a href="http://www.tdwi.eu/fortbildung/tdwi-in-depth-education/march-2013/">Berlin, Germany</a> (March 13-15)</li><li><a href="http://events.tdwi.org/Events/London-BI-Symposium-2013/Sessions/Monday/TDWI-Dimensional-Data-Modeling-Primer.aspx">London, UK</a> (March 18-20)</li><li><a href="http://events.tdwi.org/events/seminar-series-2013/information/washington-dc-seminar.aspx">Washington DC</a> (April 23-25)</li><li><a href="http://tdwi.org/microsites/australia-international-site/courses/tdwi-data-modeling.aspx">Sydney, Australia</a> (June 24-25)</li><li><a href="http://tdwi.org/microsites/australia-international-site/courses/tdwi-data-modeling.aspx">Melbourne, Australia</a> (June 27-28)</li><li><a href="http://events.tdwi.org/events/seminar-series-2013/information/minneapolis-seminar.aspx">Minneapolis, MN</a> (October 8-10)</li></ul><div>Chris is also scheduled to teach several TDWI courses at these and other events. For full details, check the sidebar of this blog. (If using a reader, you will need to click through)</div></div><div><br /></div><div>These don't work for you? Check back from time to time. As new cities and dates are added, they will appear on the sidebar.</div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.comtag:blogger.com,1999:blog-2707613555623047672.post-32295101457213419762013-02-13T10:03:00.001-05:002013-02-15T09:31:04.447-05:00Optional Relationships Without NULL<i>Optional relationships are important in dimensional models. This post shows you how to support them without resorting to NULL keys in the fact table.</i><br /><br /><br />Last month, we looked at the impact of allowing dimension attributes to contain NULL. In this post, we'll look at the impact of allowing foreign keys in fact tables to contain NULL.<br /><br />Once again, NULL will prove problematic. What should be simple queries will require an alternate join syntax, multiple comparisons and nested parentheses.<br /><br />The preferred solution is to establish special-case rows in dimensions. These rows can be referenced by fact table rows that do not have corresponding dimension detail.<br /><b><br /></b> <b>Recap on NULL</b><br /><br />NULL is a special SQL keyword used to denote the absence of data.<br /><br />Last month, I explained why we avoid allowing dimension attributes to contain NULL. NULL fails standard comparisons, necessitating query predicates containing numerous tests which are carefully balanced within sets of parentheses.<br /><br />For the full story, and the preferred solution, see last month's post: <a href="http://blog.oaktonsoftware.com/2013/01/avoid-null-in-dimensions.html">Avoid NULL in Dimensions</a> (1/7/2013).<br /><br />But that was NULL dimension attributes. What about NULL foreign keys?<br /><br /><b>Optional Relationships and NULL</b><br /><br />Sometimes, the relationship between a fact table and a dimension is optional. This means some rows in the fact table cannot be associated with the dimension.<br /><br />In an ER model, the traditional solution is to store NULL foreign keys for such rows. Let's take a look at what would happen if we did that in a dimensional model.<br /><br />You may have noticed that in some stores, the cashier asks you if a salesperson helped you. If so, they record that info. So some sales have a salesperson, some do not.<br /><br />With an optional relationship to Salesrep, you star schema might look like this:<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-DLIIMP5wFcw/URuWfHNKg7I/AAAAAAAAAJs/x9GXEUb4Cdo/s1600/Slide2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-DLIIMP5wFcw/URuWfHNKg7I/AAAAAAAAAJs/x9GXEUb4Cdo/s1600/Slide2.jpg" /></a></div><br /><br />The dotted line represents an optional relationship. (In other notations, optionality is represented by including circles at the ends of relationship lines.) For fact table rows with no salesperson, salesrep_key contains NULL.<br /><br /><b>Usability Harmed by NULL Foreign Keys</b><br /><br />When a foreign key can contain NULL, we once again face difficulties when answering some simple business questions. As before, NULL complicates queries because it requires a comparison syntax that is different from the syntax for standard values. This time, we'll also be facing different join syntax.<br /><br />For example, using the sales star, you might like to see all sales where a manager was not involved. Assuming the Salesrep table has a column called salesrep_type, you would be forgiven for adding this to your query:<br /><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> </span><span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">WHERE salesrep.salesrep_type != "Manager"</span><br /><br />This predicate is not sufficient to find all sales without managerial involvement.<br /><br />Assuming a standard join is linking sales_facts to salerep, rows with <i>no salesrep</i> will not appear in the query results. This happens because, for any fact without a salesrep_key, the join to salesrep will fail. An outer join must be used to help facts with no salesreps survive the join.<br /><br />Even when an outer join is employed, the above constraint remains insufficient. That's because a side effect of the outer join is to create NULL salesreps in the data set. <br /><br />In addition to an outer join, we must supplement the constraint above:<br /><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> WHERE</span><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ( salesrep.salesrep_type != "Manager" OR</span><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> salesrep.salesrep_type IS NULL</span><br /><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ) AND...</span><br /><br />NULL keys force us to choose the correct join type, perform multiple comparisons against the same dimension attribute, and carefully balance parentheses.<br /><br />A dimensional model is meant to be understandable and usable from a business perspective. NULL keys do not fit the bill.<br /><br /><b>Use a Special Case Row</b><br /><br /><br />When there is an optional relationship between fact and dimension, best practices call for a special row in the dimension. This row is referenced by facts that would otherwise require a NULL foreign key.<br /><br />For example, we add a "not applicable" row to our salesrep table as so:<br /><div><style type="text/css">table.tableizer-table { border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #ccc; } .tableizer-table th { background-color: #104E8B; color: #FFF; font-weight: bold; } </style><br /><br /><table class="tableizer-table"><tbody><tr class="tableizer-firstrow"><th>salerep_key</th><th>row_type</th><th>salerep_type</th><th>salesrep_name</th></tr><tr><td>0</td><td>No Salesrep</td><td>n/a</td><td>n/a</td></tr><tr><td>100</td><td>Salesrep</td><td>Associate</td><td>Paul Cook</td></tr><tr><td>101</td><td>Salesrep</td><td>Associate</td><td>Steve Jones</td></tr><tr><td>201</td><td>Salesrep</td><td>Manager</td><td>Glen Matlock</td></tr></tbody></table><br /></div><br />Now we don't need outer joins, and we don't need to bend over backwards to perform simple comparisons.<br /><br /><b>Further Reading</b><br /><br />The technique described in this post can be extended to handle other situations (invalid data, future events, or reference data that becomes available after facts). <br /><br />Read more about these possibilities in Chapter 6 of my book, my book, <a href="http://www.amazon.com/gp/product/0071744320?ie=UTF8&tag=oaktonsoftwar-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071744320">Star Schema: The Complete Reference</a>. <br /><br /><div>Also check out the previous post, <a href="http://blog.oaktonsoftware.com/2013/01/avoid-null-in-dimensions.html">Avoid NULL in Dimensions</a> (1/7/2013)<br /><br />Edited 2/13/13 5:30pm to correct mismatched table headings. Thanks for the emails.</div><div><br /></div><div class="blogger-post-footer"><div style="padding: 10px; background: #cce5ff; border: 1px solid Black; clear: both; text-align: center;">
<p>This is a post from
<b>
<a href="http://blog.chrisadamson.com">Chris Adamson's Blog.</a>
</b>
<br//>
Content is copyright (c) 2007-2017 Chris Adamson, except where noted.
<br//>
Visit
<a href="http://blog.chrisadamson.com">Chris's Blog</a> for more posts and resources.
</p>
</div></div>Chris Adamsonhttp://www.blogger.com/profile/10777611015436313229noreply@blogger.com