Why this blog series exists:
When you buy an out of the box data warehouse & cube solution like Jet Enterprise from Jet Reports, www.jetreports.com or ZapBI, it usually meets the 80/20 rule (the stock solution satisfies 80% of your reporting requirements, and you'll need further customizations to meet your remaining analytics requirements).
Client requests from customers like you inspire this blog series.
Set a Default Value for Budget Amount
Problem: Some measures require filtering by a default value to show reasonable results.
Consider the example of the company that creates and revises a new budget each quarter. While it's perfectly reasonable to show the [Budget Amount] for March 2017 from the 'Initial 2017 Budget' or 'Q1 2017 Amended', it would be misleading to show the sum of both budgets for March 2017. Similarly, if your organization uses a forecasting tool to calculate expected item consumption or sales, you may have multiple forecasts numbers for the same item and month.
Solution: To minimize the risk of 'double counting', we'll modify [Budget Amount] to always filter by a default [Budget Name].
In this solution, we hard-coded a budget into the Default Member as part of the dimension definition. For improved maintenance, we could add an [IsDefaultBudget] attribute to the [Budget] dimension in our cube and data warehouse. Then reference the new column when defining the Default Member. Ideally our source ERP system can identify the current budget, or we can implement business processes and SQL code to auto-identify the correct budget.
Note: Because we can have multiple concurrent budgets, but shouldn't sum [Budget Amount] across different budgets, the measure is a semi-additive fact -- in Business Intelligence nomenclature semi-additive facts cannot be summed across all dimensions. Other common examples include balance, profit percent, average unit price, company consolidation, as well as most cost-allocation schemes. When customizing our data warehouses and cubes, we must be careful where we implement the calculation to avoid reporting errors.
Non-additive facts cannot be summed (ex. [First Invoice Date]). Fully-additive facts are 'normal' facts which can be summed across all dimensions.
Invert the sign of Income Statement Accounts
Problem: Sometimes the dimension used affects how we want to calculate a measure.
In many ERP systems the [Amount] or [Quantity] column have counter-intuitive signs. Consider G/L Entries which have negative signs for revenue and positive signs for expenses or inventory transactions which show a negative sign for item sales and shipments and positive signs for item purchases. While the data makes sense to accountants and analysts, business users frequently appreciate seeing signs that intuitively make sense.
In cube-based reports, we'd like to invert the sign on [Amount] for all Income Statement accounts but not Balance Sheet accounts. Note, it's arguably 'wrong' to invert the sign in the data warehouse because that would make our [Amount] column semi-additive, and potentially cause problems for our auditors!
Solution: In the cube, use a SCOPE statements to assign a different calculation to [Amount] based on the [GL Account] dimension.
Note: in SSAS cube nomenclature we typically deal with two out of three measure types: Standard and Calculated (avoid Derived measures when possible).
A Standard measure is typically a SUM or COUNT of a column that exists on the fact table in the data warehouse: ex. sum(Amount) or sum(Quantity).
A Calculated measure is an expression that typically uses standard measures in conjunction with MDX expressions and functions. Typical examples include division (profit margin percent or avg. daily sales) as well as all business functions (Year To Date, Balance, or Previous Period).
A Derived measure is a calculation based on fields from the same fact table -- ex. Profit Margin ([Sales Amount] - [Cost Amount]) When possible, avoid derived measures and use standard measures instead-- just add the calculation as a new column in the data warehouse. Using standard instead of derived measures has two major advantages:
That's All Folks
Remember, out of the box solutions by definition are designed to work for ALL customers. Further customization is expected, even required! Onyx Reporting is available for development services, training and mentoring as you implement minor (if not major) customizations to your Jet Enterprise implementation.
For clients not using Jet Enterprise, please contact us to find out how data warehouse automation can cut your business intelligence implementation time from weeks and months into days.
Last week we explored 6 dashboard design tips to improve the aesthetics of existing dashboards. In this post, we share design tips from our analysts about dashboard and OLAP cube requirements gathering as well as costly mistakes to avoid.
12 Tips to Remember
Tailor your dashboard to the audience AND the process they're trying to optimize.
5 Tips to Avoid
Kicking off a project with an overly complex problem quickly leads to project stall or paralysis.
Go Forth and do Great Things
Still looking for inspiration?
In his article "Choosing the Right Chart for your Data," Brian Petersen (VP of Professional Services at Jet Reports) writes: "Data is the foundation of effective business. ... Being able to quickly read and analyze your data enables you ... to understand how a particular set or group of facts contributes to your overall success and steer your decisions proactively."
He then goes on to describe several common charts including:
For more seasoned analysts, the challenge is less about finding the right chart so much as laying out a dashboard that effectively communicates a broad scale of both summarized as well as detailed information. As we delve into optimizing dashboards, we move away from technical or domain expertise and transition toward questions of User Experience and User Interface.
For these projects, I'll leverage knowledge gleaned from Stephen Few's guide to dashboarding -- "Information Dashboard Design" which was heavily influenced by Edward Tufte's seminal work "The Visual Display of Quantitative Information".
Charts Jump off the Page with these 6 Tips
1) "Brevity is the soul of wit" -- Do not exceed 1 page.
Any CxO will describe the perfect dashboard as an interactive report where they can see all the important information on one page. Translation: Edit. Edit. Then edit again. Examine how much excessive detail or decoration you can pare away without supplying inadequate context.
Your final product shouldn't require scrolling, changing tabs, or (ideally) a legend.
2) How good is good? -- Provide enrichment and context through comparison.
3) Consider providing non-quantitative data
If you frame dashboards around improving a process or keeping 'two fingers on the pulse of the company'; in addition to measuring activity, it may make sense to provide non-numeric data.
4) Emphasize the important things.
By understand how the eye travels across the page, designers can highlight, prioritize and de-epmphasize. This is particularly important when planning the placement of auxiliary elements including filters, slicers, legends and labels.
5) Maximize the "Data-Ink Ratio"
6) Organize information to support interpretation and application
My favorite feature of Few's book was his analysis of sample dashboards, wherein he described, not only the flaws in various dashboards but also modeled various alternative ways of presenting the data. For dashboard developers this analysis would prove invaluable for sharpening our critical eye, but also provide inspiration for what dashboards can (or shouldn't) look like!
Bridge the Gap between Concept and Execution
For those of you using Pivot Tables or PowerBI to access data from a Jet Enterprise cube, it can be difficult pursue the optimum dashboard layout or chart because you're constrained by the limits of the pivot table or data model.
In a previous post: Better Dashboarding with Cube Functions, Onyx Reporting presents a tutorial for converting Pivot Tables into infinitely more manipulable Cube functions.
One Book to Rule them All
Content and images from this blog post were taken from Stephen Few's Information Dashboard Design (buy it on Amazon).
Check out our blog at www.onyxreporting.com/blog or sign up for our weekly newsletter: "The Data Dump".
If you need support knocking out a batch of reports or want to customize your Jet Reports cubes to include some new comparative measures, our services team is amoung the best in the business.
With modern reporting tools allowing more and more companies to answer 'what did we do yesterday?' the logical progression is to then predict, 'what are we going to do tomorrow?' Forecasting solutions vary in complexity with options including elaborate third party software, complex spreadsheets developed by internal analysts, and occasionally a shake of the good-ol' crystal eight ball. I recently had a conversation with a consultant who confided with me that their client swears by XYZ solution. "How does it work?" I asked. Does it look at a bell curves and confidence intervals? 12 month moving average? 3 month moving average? Weighted average? Linear regression? Seasonal adjustment? "It looks at all of that" I was assured. "How does the system know which forecast method to use?" ... silence.
To be honest, almost any method could be a completely (in)appropriate way of forecasting the future! If you sell ice cream or agricultural pesticides, there's likely to be seasonality in your data. If you're predicting the stock market, moving averages and linear regression are common prediction methods. If you're tracking click rates after launching a new marketing campaign ... well that's another level of complicated, but to get the ball rolling, linear or exponential regression models may be a solid place to start.
Calculating Error in a Forecast
In a nutshell, cost functions evaluate a forecast method's goodness of fit by measuring the amount of error where:
Absolute Error = Absolute Value (Expected - Actual).
In the sample data set I whipped up, we have several months of Actuals and two forecast methods, 3 Month Moving Average and 5 Month Moving Average (3MA and 5MA respectively). An n-month moving average forecasts the current month by taking the average of n preceding months.
In a future blog post, I'll share how I built these forecast models into my SSAS Sales cube using MDX.
For the month of December, 3MA forecast 625 while 5MA predicted 596. Given that actuals were 846, 3MA was clearly a better forecast because it had the least absolute error.
Google tells me that cost functions "provide a criterion by which forecast methods can be compared," but I've chosen to reinterpret the definition as, "cost functions help us decide which forecasting method to use."
To come clear, I am not a statistician. While developing a forecasting data model for a client, I was able to consult a trusted mathematician who guided me through the basics of using cost functions to evaluate and select the 'best' forecasting model. I'm writing this article with the assumption that you either have forecast models that you're currently using, or are interested in adopting budgeting tools for use.
Calculating the Cost Functions MAD, MAPE, and MSEDon't go running for the hills just yet! Cost functions and abbreviations can sound intimidating, but this 5th grade level math will enable us to quantify and compare the error between two forecasts. The three most common cost functions are MAD (Mean Absolute Deviation), MAPE (Mean Absolute Percent Error) and MSE (Mean Squared Error).
Calculating MAD (Mean Absolute Deviation)
MAD is the sum of absolute error (also called deviations) divided by the number of periods measured.
In December and November the absolute errors for 3MA were 221 and 92 as opposed to 5MA's 250 and 187. We concluded that 3MA on average had less error.
Calculating MAPE (Mean Average Percent Error)
MAPE differs from MAD by calculating the average absolute percent error instead of simply taking the average absolute error.
Absolute Percent Error: ABS(Expected - Actual) / Actual
Calculating MSE (Mean Squared Error)
Mean Squared Error takes the Average of Error Squared.
As aforementioned, MAPE is the preferred cost function because it minimizes overall error, but consider the challenge of equipping production lines across factories in different countries. Once resources have been allocated, it may be difficult to reapportion or procure additional machinery; therefore, in this instance our primary concern is not minimizing error across the entire data set so much as minimizing error at each forecast point. In other words, the ideal forecast will allow many smaller errors while avoiding a few significant errors.
In this new data set we have two forecast methods, 3MA and Growth Factor (wherein using a crystal ball we 'managed' to predict expected as actuals * 1.28). Both forecast methods have the same total deviation and MAD but 3MA, with a max percent error of 70%, has larger swings in absolute deviation while Growth Factor consistently has a 28% error for each forecast.
In terms of our factory example, using MSE we would have selected the forecast that equally over allocated equipment at a +28% error across all factories instead of the 3MA model which would have wide swings in equipment over and under allocation.
Which Cost Method Should I Choose?
Someone said "there's lies, damned lies and statistics", and it's currently uncertain whether to credit Mark Twain or a British dignitary. Go figure. As with all things business related when faced with the question, "Which cost function is best?" the answer is, "It depends." Although MAPE and MAD weigh error evenly MAPE provides context for the severity of error where MAD does not, thus making it the de facto choice in business analysis. However, because MAPE represents percent error and 5th grade math tells us we can't divide by zero, you may have to use MAD or MSE in cases where your data set's actuals could include 0's--consider monthly inventory demand for example. Lastly, in instances where we're more concerned with reducing error per forecast, it may be more important to use the MSE cost factor.
One last thought to keep in mind before embarking on the world of forecast evaluation: not all errors were made equal! With MAD, MSE, and MAPE, we're assuming positive error and negative error are equally weighted, but consider a retail environment where running out of inventory may be more costly than carrying inventory. While warehouse fees or product obsolescence from overstocking inventory may add up, overall it may be less costly than a lost sale or disappointed customer.
Visualization: Closing the Loop
Take a look at this ball of spaghetti! In a world where our reporting and analytics tool can graph historical demand and quickly generate multiple forecasting models, this mess is just a few clicks away, and though loaded with an overabundance of forecasting data, the chart is too busy and doesn't actually tell us which forecast method to use. Which option has the lowest MAPE?
The above chart is halfway there insofar as it could be filtered to show us todays' period and identify the forecast with the lowest MAPE (or MSE as appropriate). I can then simplify my chart to the bare essentials: actuals, expected, a trend line, and the prediction for the next period.
That's All Folks
With the exception of the simplified introductory datasets at the top of the blog, all the charts were generated based on MDX added to the stock cubes provided with Jet Enterprise. If you're interested in adding forecasting or forecast evaluation to your cube-based reporting environment give me a holler at Jae@OnyxReporting.com
Between now and my previous post --'why Power BI won't replace a Data Warehouse'--which was built on ideas shared in 'why people should stop confusing BI with Visualization', I've done even more thinking on the topic of why clients have unsuccessful or incomplete Business Intelligence implementations. The good news is, it's seldom a case of bad software fit, lack of resources or expertise, so much as uncertainty around what the desired outcome should be. It doesn't matter if your solution is Targit, Crystal, SAP, ZapBI or Jet Enterprise, if the client doesn't start with a clear vision of what they want, no matter what you deliver, it seems, they don't want that!
Ultimately, the root challenge is not a question of functionality or capability so much as whether the user can articulate what they want or need. I just had a conversation with a potential client who lamented, "No one on my team except me really knows what they want." Honestly, that degree of transparency is better than the dreaded "we just want a simple dashboard," but there's still work to be done!
It's time to sit down with your users and map out some mock ups of what you want your final reports to look like. Get away from emails and lengthy paragraph long descriptions; instead, use simplified spreadsheets with sample headers, fake data, mock-up graphs, or a list of the KPI's you want to see in your solution. Even an seemingly obvious question like, "How do you want to consume this data, (spreadsheet, pivot table, web page, or mobile device) can have far reaching implications on what Front End software you use to develop your reports! I've delivered more than a handful of dashboard workshops where my opening question"what do you want your dashboard to look like?" was met with dead silence. #FacePalm.
If this still seems too daunting, maybe it's time to do a little window shopping--did you know that Jet Reports has over 100+ generic reports available for you to use? I know, "these generic reports don't quite meet your company's unique needs." But that's not our expectation! If you can express what does and doesn't work about a generic report, then we can uncover not only what you want and need, but now you have ideas about what it could look like on paper! A good Jet consultant can customize any of the Report Player reports, or even build a more robust one from scratch with all the features you want!
Unfortunately consultants are sometimes slow to promote the Report Player. "Our end users don't like it because XYZ." Trust me, I've heard every possible reason, from both clients and consultants, but I'll shorten my counter-argument to the following: the Report Player inspires users with glimpses of what's possible. At the end of the day, consultants can only deliver what users ask for, and clients can only ask for what they know about or have seen before. I like to think of the Report Player as a mini IKEA catalogue: the pictures are there to whet your appetite. Don't expect completeness; instead, let the report layouts be a source of inspiration. I love it when a client shows me a report and says: "I love it, but can it do ..." Now we can focus the conversation to delivering exactly what you want because you know what you're looking for.
At the risk of being the unpopular consultant, I'm going to make a recommendation that no one wants to hear. Please. Stop spending money on visualization software.
If you Google "Top 10 BI Software" or go to a trade show, you'll see all sorts of visualization tools that promise to sing, dance, and make your dreams come true. It'll make dashboards, KPI's, and pretty charts--maybe even warn you if you drop below Safety Stock. Wait, put the credit card down, and please stop buying visualization software.
While it is fun believing that a shiny new software can solve your data problems, you probably already own a visualization tool that will meet 90% of your reporting and analytics needs! Don't believe me? I'll bet if you asked an analyst to give you numbers about <insert your business metric here>, after a few hours, they'd come back to you with an Excel Spreadsheet or Pivot Chart or two. Turn around and ask the Finance Department for an Income Statement report broken out by Department and Region with measures for Current Year, Previous Year and %Growth. Again, I bet you get another spreadsheet. And why not? No matter how unsexy it seems, Excel is the de facto tool for business analysis, and it will probably meet 90% of your reporting needs.
The real question though, is how much sweating did the analyst have to go through to get you those spreadsheets? If you look at the formulas and see a slew of SUMIF()s and VLOOKUPS() how nervous are you about presenting to the bank, auditors or in meeting rooms? Only way it could be worse is if you saw a bunch of static numbers! Truthfully, this is where BI comes to play. Although our eyes may start glazing over when you start talking about SSIS, ETL, star schemas, and multidimensional models, the fact of the matter is, any visualization tool will perform better when pointed at a well-designed Data Warehouse or Cube. In fact, if you ask the vendor giving you your Visualization Software demo, I’ll be they’ll tell you that data is coming out of a sanitized data warehouse, cube or data model that emulates a star schema. If they’re using none of these tools they’re assuming that your ERP contains clean data. <Insert skeptical snerk here>.
So what exactly is Data Warehousing all about?
Well, the Data Warehouse is actually the end of a tumultuous story of data access and extraction from your various source databases, Point of Sales systems, Google Analytics, Bloomberg CSV's, customs spreadsheets, or web-based Census Bureau data). Once extracted, data undergoes rigorous application of business logic and queries to conform, validate, and clean all the disparate data, then finally a transformation process forges the cleaned data into a Data Model that consists navigable dimensions and fact measurements. Voila. Your Data Warehouse.
Sounds dry and possibly unsexy? Consider the alternative. Earlier this year I worked with a client who wanted a ranked dashboard that would show the amount and volume of deals that salespersons were bringing through the door. The first draft of the report took less than an hour to build, but it was immediately sent back for revision. "That lead conversion number is almost triple our average," I was told. Further investigation revealed that Status could waffle between Lead and Conversion multiple times based on a combination of factors including: data entry problems, whether the data was collected via web forms or hand written forms, or if specific intermediary statuses were made or missed. My report needed Olympic-quality gymnastics to extract a reasonable measurement at the cost of report performance.
Suffice to say, what should have been an easy report ended up taking more time than expected because there wasn't one source of clean data, and I loathe to think what would happen if the client wanted the report rolled up by region, or presented in a different layout. Every client this story of having paid a consultant an ungodly amount of money for a report that we’re only marginally comfortable with and cannot be modified to present the data a slightly different way. So what’s the solution? A new visualization tools? No! Invest in a clean data warehouse that handles all the data gymnastics for you! Don't get lured in by a vendor's siren's song of sexy graphics, KPI's and dashboards if you don't have a solid data warehouse already. Instead, I would recommend looking around for a Data Warehouse automation tool.
Many of my client have enjoyed success working with the Jet Enterprise data warehouse automation tool, http://jetreports.com/products/jet-enterprise.php. And I’d be thrilled to work with you to develop data warehouses either from scratch or customize one from the library of pre-built projects. Once that’s done, THEN we can talk about sexy visualization tools.