Self Service BI is here! With Power BI, you no longer need a traditional data warehouses. Blue is the new Black and White is the new Gold!
In my last blog post, we discussed how data visualization is just the last phase of the business intelligence process. I sought to emphasize the value of a data warehouse in conjunction with the sexy visualization tools. Power BI is upsetting the market because some fear (and others purport) that it completely supplant a data warehouse. Let's be clear. No tool is going to replace the need for a conformed, sanitized, and policed data warehouse (a data warehouse takes the current and historical data from your ERP and restructures it for reporting and analytic purposes in a database separate from your transactional systems).
Now, for clarity of language, let’s agree to condense the definition of Business Intelligence, to “a data analysis process aimed at boosting business performance by helping stakeholders make more informed decisions” (thank you Bing search engine). Having agreed on BI’s definition, let’s now expand our paradigm of BI tools to encompass models suitable for an individual, a small team, or an entire Enterprise.
Hopefully everyone is still onboard with our definition of BI and the purpose of a data warehouse; here, however is where some part ways. Because emerging tools like Power BI can create cube structures fed directly by an ERP, controversy arises as to whether we still need a data warehouse. While I fully endorse and look forward to more companies leveraging Power BI as a presentation tool, I do not believe that an exclusively Power BI solution will replace the data warehouse for Corporate and Team BI. Let’s back up and discuss.
Why you need a Data Warehouse.
In traditional BI, whenever possible, we separate the transactional system (your ERP—whether NAV, GP, AX, Quickbooks, Sage) from the reporting environment. You may have heard names like Inmon or Kimball, tabular versus multidimensional, and star versus snowflake schema bandied about when discussing data warehousing, cubes and your reporting environment. Where your ERP may have data entry problems, incorrect status changes, or even truncate historical data (when you migrate or upgrade your ERP systems), the purpose of a data warehouse is to create a policed structure that measures and holds all historically relevant company activity and then gives the ability to present said measurements across a series of structured dimensions (like Customer, Vendor, or Date).
Some cost conscious business owners having invested in an ERP may balk at investing in a separate reporting solution. “Shouldn’t reporting just come with NAV?” Yes, and no. Actually … mostly no. Just as there’s an industry around recording your company’s transactions, there’s an equally robust industry around extracting and presenting that data. Some clients want a standalone front-end application like Targit; others need access via mobile devices and web pages; while some analysts just want their data in Excel. Furthermore, in order to create a consolidated view of a customer or your service offerings, it’s valuable to integrate data from CRM, Helpdesk software and custom CSVs generated by online sources or machines off the manufacturing line.
Admittedly, for ease of reportability, I’ve seen companies integrate these external datasources directly into their ERP, but a hybrid ERP/data warehouse solution often yields subpar results. “The system only works if customer number in the ERP matches the account number in CRM”, “there’s no system for handling what to do if the customer information changes in CRM but doesn’t update NAV” or vice versa. We haven’t even begun discussing how to match Tech Support tickets with an invoice line or SLA in your ERP, and you can forget about cost allocation!
Please, stop asking your ERP to be a Unicorn and a Dragon at the same time. The ERP should capture transactions; your Data Warehouse should be your reporting solution! And no new-fangled tool should unify those inherently disparate needs.
It is worth noting that not every reporting request requires a data warehouse. At the Personal (or trendily named Self-Service) level, an employee on the purchasing team may need a tool that can project demand based on scheduled production and then calculate shortages based on Inventory On Hand and Inventory on Production Orders. Alternatively, you may have already seen a Power BI demo based on basic sales analytics. None of this simple analysis requires a data warehouse, but as data volume or sources increase, the need for an alternative to direct queries to the ERP emerges.
Let your Analysts be Analysts
Let’s pause for a moment. Again, I like the idea of Power BI for presentation. And if I had to guess you were sold on Power BI by the pretty dashboarding tools too! But that’s not what we’re evaluating here. The question we’re tackling is, whether you need a data warehouse.
When an analyst expands their reporting requirements horizontally to span multiple departments or vertically to roll up by dimension we move past a ‘do-it yourself’ self-service option toward needing a data warehouse where all this data has been collected and sanitized. Consider the challenge of evaluating whether there could be cost savings in having larger POs and holding inventory versus adopting a Just-In-Time model. Alternatively, what if we wanted a dashboard that gave us the health of our warehouse measured across 5 or 6 metrics compared against last month and last year? Because the data may not be measured at the same granularity we then have to agree on consistent allocation rules. This project might be a bit too rigorous to assemble with Excel Spreadsheets and it’s here where we see the benefit of a data warehouse and accompanying cube solution.
Whatever you do, don’t let vendors sell you on the idea that your analysts can build a data model themselves (unless they ARE BI developers / analysts). Remember, you were sold on the sexy visualizations NOT the thrill of learning to build data models. Yes, an analyst can learn how to create a pivot table or pivot chart, but that’s a far cry from asking you to dabble in MDX, DAX, SQL, SSIS, and SSAS or asking you to get around SSMS and SSDT to build star schemas optimized with indexes, appropriate data types, surrogate keys and all the other arcane concepts supporting cube-based reporting. Let your analyst do what they do best: analyze data! Don’t ask them to become data architects or database developers too.
In conclusion, while it’s true that modern tools are making it easier for end users to create their own small scale data models, there’s still a need for a conformed enterprise-wide model that has been rigorously tested and reviewed for soundness of business logic. And while we appreciate the flexibility when analyzing sales trends or conducting exploratory analytics at the Team and Corporate level, the flexibility of a self-service option is borderline inappropriate. There cannot be any creativity with reported measures like EBITDA, Profit Margin and Overhead Costs!
Does Power BI mean I can't ...
If we now agree that you need a data warehouse, the next question to tackle is “do you want Power BI? Because if you do, you can’t …” or “you must …” or “it differs from traditional BI because …” Again, stop and step back from the ledge. Let’s not confuse building a data warehouse with finding tools for presenting the data. Agreeing that you need a data warehouse has NOTHING TO DO with whether you use Power BI for visualization or as a self-service option.
“But I heard I can only use a Tabular model with Power BI”. Today, it’s true. Power BI’s support for traditional multidimensional cubes (as opposed to the newer Tabular model) is limited. Rumors are flying that by the end of 2016, Power BI will have better support for multidimensional cubes which renders this concern moot. There are existing articles comparing the merits of Tabular cubes versus Multidimensional cubes, each claims to be easier or more sophisticated than the other, but that’s beyond the scope of this article. Once you get past the Data Warehouse, I’d argue the larger concern is what kind of tools you want to use to visualize the data and what resources you have access to that can implement the data model you want.
If you want to empower your employees with more sophisticated reporting and analytics options, whatever you do, don’t let someone tell you, you don’t need a Data Warehouse.
Many of my client have enjoyed success working with the Jet Enterprise data warehouse automation tool, http://jetreports.com/products/jet-enterprise.php to build their Data Warehouse. I’d be thrilled to work with you to develop data warehouses either from scratch or customize one from the library of prebuilt projects. Once that’s done, THEN we can talk about sexy visualization tools.
Image from Microsoft Whitepaper: "Introducing the BI Semantic Model in Microsoft® SQL Server® 2012"
Welcome back from the holidays!
I wish I could say I put all work behind me, but instead I spent some time working on a few projects I've had on the burner, including exploring how Power Pivot and Power View might affect our work with Jet Enterprise and 'formal' BI projects.
Through blogs and interactions with customers and partners, we're hearing murmurs questioning whether data warehouses can be replaced by Excel's Power BI family. In this blog I demonstrate that since Power Pivot can recreate a lot of the functionality that Jet Enterprise's dimensional projects leverage, instead of fearing or demonizing the disruptive potential of Power Pivot, we can welcome it into our workflow. This tool can empower business analysts to prototype the cube customizations they desire, as well as act as a stop gap for analysis and reporting while cube customizations are implemented.