Tag Archive for Business Intelligence

Electrify Your Business with Data

Data is a lot like electricity – a little bit at a time, not too much direct contact, and you’re fine. For example, a single nine-volt battery doesn’t provide enough power to light a single residential bulb. In fact, it would take about a dozen nine-volt batteries to light that single bulb, and it would only last about an hour. It’s only when you get massive amounts of electricity flowing in a controlled way that you can see real results, like running electric motors, lighting up highway billboards, heating an oven or powering commuter trains.

iStock_000006412772XSmallIt’s the same with data. The sale of a single blue shirt at a single outlet store is not much data. And it’s still not much even when you combine it with all the sales for that store in a single day. But what about a year’s worth of data, from multiple locations? Massive amounts of data can do amazing things for us as well. We have all seen in today’s data centric business environment what controlled usage of data can do.

Some examples include:

  • The National Oceanic and Atmospheric Administration (NOAA) can now more accurately predict a hurricane’s path thanks to data that has been collected over time
  • Marketing firms can save money on culled down distribution lists based on customer demographics, shopping habits and preferences.
  • Medical experts can identify and treat conditions and diseases much better based on a patient’s history, life risks and other factors.
  • Big ‘multi-plex’ movie houses can predict more accurately the number of theatres it will need to provision for the latest summer block buster by analyzing Twitter and other social media feeds as related to the movie.

All of this can be done thanks to controlled data analytics.

The key word here is “controlled.” With a background in marine engineering and shore-side power generation, I have seen my share of what can happen when electricity and other sources of energy are not kept ‘controlled.’ Ever see what happens when a handful of welding rods go through a steam turbine spinning at 36,000 RPM and designed for tolerances of thousandths of an inch? It’s not pretty. After as many years in database technologies, data analysis and visualizations, I have also seen the damage resulting from large quantities of uncontrolled data. In his book Signal: Discerning What Matters Most in a World of Noise, author Steven Few shows a somewhat tongue-in-cheek graph that ‘proves’ that the sale of ice cream is a direct cause of violent crime. Or was it the other way around?  It’s an obvious comic hyperbole that serves to illustrate his point that we need to be careful of how we analyze and correlate data.

With the ‘big data‘ explosion, proponents will tell you that ‘if a little is good, then more is better.’ It’s an obvious extension, but is it accurate? Is there such a thing as ‘too much data’?

Let’s say you are a clothing retail store in the mall. Having data for all of your sales over the past ten years, broken down by item, store, date, salesperson and any number of other dimensions may be essential. What if we were to also include ALL the sales of ALL competitors’ products, seasonal weather history, demographic changes, foot traffic patterns in the mall and just about anything else that could influence a customer’s decision to buy your product even down to what they had for lunch just before they made the purchase? The result would most likely be UN-controlled data analysis. This tends to lead to erroneous correlations and bad decisions. For instance, you just might discover that customers are four times as likely to make a purchase if they had pizza for lunch, never realizing that there are more pizza restaurants near your stores than any other type of food service!

When it comes to data, stick with what you know is good data. Make sure it’s clean, reliable and most of all, relevant. Most of all, make sure you CONTROL your data. Without control, there may be no stopping the damage that results.

About Todd: Todd Chittenden started his programming and reporting career with industrial maintenance applications in the late 1990’s. When SQL Server 2005 was introduced, he quickly became certified in Microsoft’s latest RDBMS technology and has added certifications over the years. He currently holds an MCSE in Business Intelligence. He has applied his knowledge of relational databases, data warehouses, business intelligence and analytics to a variety of projects for BlumShapiro since 2011.

BI Call to Action

How to Setup the Waterfall Chart in Power BI

In this article, I wanted to touch on a visualization that was part of the initial release of Power BI, but one I seldom see on Dashboards or requested by Business Users: The Waterfall Chart. What is it? How do I set it up? What is it useful at displaying? How does it differ from a simple Column chart? We’ll answer those questions below.

What is the Waterfall chart?

A Waterfall chart is a column type chart that shows aggregated data over time. It is useful for displaying the individual components of a measure and how they continually contribute to the overall number. Something like ‘Variance to Goal’ would make good use of a Waterfall chart. I’ll explain more of the features as we explore and set up a simple example.

For this exercise we’ll make up some data in the Power BI Desktop designer. Click the Enter Data button on the Home ribbon and enter values and Column Headers as follows:

Waterfall Data

After clicking OK and loading the table, format the Date column as “(MMMM, yyyy)” and the other two as your favorite currency. That’s it. That’s all we need to move on to some charting. And first up will be a Clustered Column chart showing both Sales and Sales Goal side-by-side for the 12 months:

Sales Goal Column Chart

Because the default for a Column chart is to show the full height, this particular comparison, where the variance between the two numbers is relatively small compared to the total height, the eyes may have a hard time differentiating the columns. Also, we have to work hard to find the four months where the Sales value was below the Goal.

I see this type of visualization often and every time I think, “There’s got to be a better way to show this.” Fortunately, there is, but we need to add a variance calculation. Let’s add a Calculated Column to the table with the following formula:

Sales Goal Variance = [Sales] – [Sales Goal]

This should also be formatted as currency. Charting this value in a simple Column chart will give us a little more insight into how the variance has changed over the course of those same 12 months:

Sales Goal Variance Column Chart

This is a little easier to understand, particularly when it comes to determining when the Variance is negative or positive. But it still lacks the ability to show at what point, for example, we recovered from the shortfall in January and broke even, or if we are above or below our Goal for the entire year.

As useful as this plot is, there is an even better way. Here’s what the Waterfall chart would do with these same data points:

  1. Plot negative numbers in a different color than positive ones.
  2. Start each succeeding month at the point where the prior month ended.
  3. Show a total (positive or negative) for the entire time frame.

For example, if we could slide down the February column so that it started at the end of January’s ($2K),

Waterfall 1

then slide March’s $500 down so it starts where February ended  at ($1k) ( = ($2K) at the end of January + $1K added by February ) and so on, the result would be a Waterfall chart as show here:

Waterfall ChartThe resulting plot is an easy to understand column-type chart showing how each monthly segment contributed to the overall value for the total period. From this chart, we can easily pick out the following tidbits from our data that were not readily available in either of the two previous chart styles:

  • January was disastrous we knew, but by the end of April we had recovered and were back on track.
  • We had four consecutive months of Sales greater than the corresponding monthly Goal (February through May), then settled into a back-and-forth for most of the rest of the year.
  • November was just as bad as January, and brought us slightly behind the Goal for the year.
  • December’s gain was the best, and brought us over our Goal by a wide margin.

How do I set up a Waterfall Chart?

The Waterfall chart is one of the easiest in Power BI to set up. There are three very basic steps, as outlined below, and which need not be done in the order specified.

Select Waterfall chart

  • Select the Waterfall chart from the Visualizations palette. If you have not already added any other elements, it will add a blank chart to the report canvas.
  • Select the Y Axis. In our case, we used a Calculated Column called Sales Goal Variance, which, being a numeric data type, was automatically summed by default. We could have just as easily selected a Measure that already had the appropriate aggregation defined.
  • Select the Category. Typically, a date type is used here, and be careful after selecting as Power BI tends to create Date Hierarchies which must then be manipulated. For this exercise, since my data was already at a monthly grain, I by-passed the hierarchies and went straight for the Date column.

select Date

There are some useful format options, too, that should be noted. The most prominent one is the Sentiment Colors option. With this, the designer can assign colors to the Increase, Decrease and Total bars if, for example, the data was such that a DECREASE was favored over INCREASE.

Sentiment Colors

How does a Waterfall chart differ from a Column chart?

As shown, the two are very close in layout, but there are some major differences:

  • The Waterfall chart cannot show a Series, such as Territory or Product, as either stacked or clustered, like a Column chart can. It is limited to one value only.
  • It does not allow superimposing of a line with a secondary Y axis that shares the same X axis.
  • It can only be displayed with vertical columns and does not allow horizontal bars.

What is a Waterfall chart useful for?

Limitations above aside, the Waterfall chart is still quite useful in the right situation and with the right data. As already shown, when plotting a variance, numbers where there is a potential for negative values, the Waterfall chart excels. It gives somewhat of a Year-to-Date look and feel of the data without the need to write the DAX expression. Note that the ending point for each month (the top of each green column and the bottom of each red column) represents the point of YTD Variance for that month. (I sometimes refer to a Waterfall chart as a ‘poor man’s YTD’.)

Consider the following Column chart that plots the DAX Measure:

YTD Variance = TOTALYTD(SUM(‘Table1′[Sales Goal Variance]),’Table1′[Date])

YTD Variance

This plot is, I believe somewhat misleading to the viewer, even though the ENDS of each monthly column correspond with the ENDS of the columns in the Waterfall. All we can tell for any one month is where we are in YTD Variance, but not how we got there. To arrive at July’s YTD Variance value of $750, for example, we had to DROP from June’s point of $1,750, something that the Waterfall chart clearly indicates, but something we need to derive in the above plot based on the position of July’s end point relative to June’s. Our Sales Goal Variance was NEGATIVE for July, but the above plot seems to indicate that July was POSITIVE!

And lastly, YTD plots always end at the end of the year because, well, that’s in their definition. A Waterfall chart can span multiple years with ease, or even be sliced or filtered to start at a different point along the X (Category) axis. The plot below charts the same data, but shows April through October.

April Waterfall

Hopefully, now that you understand the benefits and limitations of the Waterfall chart, you’ll be more inclined to introduce it and explain its benefits to Business Users for displaying variance data.

Learn more about Blum Shapiro Technology Consulting

Also by this author: KPI’s in Power BI, Not as Hard as You Think.

Drive Your Business with a Dashboard, not Intuition

Business Owners and Senior Management should be constantly monitoring the performance of their business.  Some managers may accomplish this by talking to the people who work for them.  Ultimately, however, management is called upon to make decisions, and they do so with the information they have available to them.   Often the biggest challenge is bringing all of the data together into something which can be understood.

Most business leaders want to base their decisions upon clear, reliable information.  But, this can be a challenge.  Information can be difficult to obtain in a timely manner.  The accuracy of the data contained in reports is sometimes questionable.  Management reports may be extremely detailed without providing critical metrics which are easy to locate.  So what do you do?

If you are a manager with some of these issues, I would urge you to clear your head for a moment and go for a spin in your car.  That’s right – turn on the ignition, be careful in the parking lot, put on your favorite music and go for a 30 minute drive.  While you are there, take a moment to appreciate the amazing information solution right in front of you – your car dashboard.

A car dashboard exhibits three powerful concepts.

Consolidation– my dashboard prominently shows me my speed, my engine temperature, the gasoline remaining in my tank, among other things.  Where does this information come from? Generally, I know – but not specifically, and frankly, I’m not that interested.  But to answer the question, the data feeding the three dials I mention above each comes from three distinct components, undoubtedly manufactured by three separate vendors.   Can you imagine if your car started behaving the way most IT departments do? “I’m sorry, but we can’t tell you how much gas you have left, because that information is in a different system.” I know what I would do. I’d get a new car.

Context – I’m not an auto mechanic. I have no idea what the correct oil pressure should be in my car, or at what temperature my engine overheats. Fortunately, my dashboard is color coded. When the dial goes Red, that is bad. I need to take corrective action to move the dial out of the Red, into the Yellow or Green.

Relevance – for some pieces of information, any kind of numeric measurement is not useful. I simply want to know if I should be concerned or take action. For example, the alert that tells me that I don’t have my seatbelt on (and the car is moving). Or the one that tells me that I need to check my engine.  Fortunately, the dashboard does not provide me with a diagnostic code – because I would not know how to interpret it.  But I do know how to respond to a Check Engine light.

Operating a business is not very different from operating an automobile.  In both cases, you are working with a sophisticated, complex system which has a lot of moving parts.  Further, you need to keep your eyes on the road in front of you.  Driving or operating your vehicle is not the time to be looking for information, and getting that information can mean having to take your car out of service, costing money.

Unlike automobiles, it’s up to you as the manager of the business to decide which metrics are Key Performance Indicators – the ones you need to keep your eye on to ensure optimal performance.  Therefore, recognize that you will need to address your organizational readiness to begin monitoring your business in the same manner.

  1. Define your strategy – have a clear understanding of what the critical metrics are, and remove data and information which is not important to monitor.
  2. Embrace Data Driven Decision making – organizations which have committed to data driven decision making take good care of their operational data, attending to data quality issues as they arise.
  3. Keep it Actionable – Your envisioned dashboards will need right-time access to operational data – depending upon what you are looking to accomplish, you may or may not need “real-time information”. What is important is that you have information in time to make a correction, and don’t spend time and money investing on technology capabilities which your organization does not need.
  4. Keep it simple – You’ll need easy to use tools, both for IT and Management – and there are plenty of them available today!

By implementing a dashboard, or set of dashboards for your business, you can expect to be able to apply better focus to the critical processes that drive your business, because “What gets measured gets done”.  You’ll make better decisions, and also make these good decisions in time to take impactful action.  Finally, dashboards can help you communicate your strategy and performance to your management team.




Adding User Configurations to an Analysis Server Cube Part 2

Part 2: Dynamic User Configurations

In Part 1 of this series, we hard-coded some MDX values into the cube. That approach works in that it produces the desired end result, but if the values need to change, a developer is needed to make it happen. What is needed is a way to persist the configuration values outside of the cube itself. In Part 2, we will create a configuration table to store the values. The structure is borrowed from that used by earlier versions of Integration Services:

Really, the only two fields absolutely required here are the Name and Value fields; the other two are added for administration and clarification. Next we’ll insert some fictional values into this configuration table:

Next, create a view that pivots the Configuration Name and Configuration Value fields with T-SQL code like this:


The dataset returned by this view will be a single row of data with one column for each Configuration named in the PIVOT section, and a static [DummyKey] value of -1.


Again, some purists may dislike my use of “SELECT * FROM …” in my view definition, but since I am limiting the columns returned via the ” . . . FOR ConfigurationName IN (…) . . .” statement of the PIVOT clause, there is not much chance of getting unneeded columns.


Next, add this view to the cube project Data Source View, then add it as a Measure Group to your cube. Delete the COUNT and the SUM(Dummy Key) measures that were added by the Measure Group wizard. Since there is only one row in the measure group’s base table, a SUM( ) aggregations for the configurations are fine. Lastly, since a Measure Group MUST be joined to at least one Dimension, on the Dimension Usage tab join the Configuration Measure Group to a dimension in your cube that meets the following criteria:

  1. The dimension has a member row with a key value of -1. (Data Warehouse designers typically add a -1 key as the “Unknown” member of the dimension table.)
  2. You will NOT be using the dimension in conjunction with the Configuration Values. This sounds rather counter intuitive based on cube design practices, but it is explained below.

Browsing the cube by any dimension OTHER than the one used to join the Configuration Measure Group will return the configuration measure values at every cube intersection. This is because you are actually selecting the [All] member of that one dimension, which includes the SUM of each Configuration Value. And since there is only one row at the [Unknown] member (Key = -1), the SUM at the [All] level is the one row. Browsing the cube INCLUDING the one dimension will show that the configuration values are ONLY available for the “Unknown” member, and not for any others. If your configuration values, whatever they represent, will NEVER be used with the dimension you have them joined to, then this is just fine. But if there is any possibility that the Configuration Measures would be needed for any and every dimension in the cube, then you need to do a little editing of the view. We’ll cover that in Part 3.

The advantage of this method over what was covered in Part 1 is that if the Configuration Values ever need to be changed, it is now simply a matter of changing a single value in a table and reprocessing the Measure Group instead of editing the cube design and redeploying the entire cube. To add additional configurations would involve the following:

  1. Add the entry in the table
  2. Edit the view to include the appropriate [ConfigurationName] in the PIVOT clause
  3. Refresh the Data Source View for the cube project
  4. Add a new measure to the Configuration Measure Group for the newly added column
  5. Deploy and process the cube

In Part 3, we will overcome the limitation of NOT being able to use the Configuration Measures for EVERY dimension.