Do You Really Need/Want All That Detailed Data In Your Power BI or Tableau Or Other DataSets?

Huh ?

I remember some years ago standing next to an IT guy at the biggest Brewing company in Canada of the time.  He proudly showed me a new product he had created that allowed the President (down the hall) to see down to the level of how much Brand X was sold at that bar across the street last evening!! 

My reply was “Why?“. I don’t think he got it, but oh well.

Data at any level above lowest level Operations and Accounting is “Business Reporting” and that means aggregations. The President of a large Corporation does not need nor should be interested in that level of “Granularity”.  That is MY opinion after decades of building reports for businesses worldwide.

If I am a Manager I will say:

“Show me data of how did we do in Sales, Operations, HR or Finance for a period of time against what we said we’d do, and against how we did previously, in the same units of time and with the same or different business activities or business organization aggregated to a level that is not exposing data that might be a security breach of our staff or customers’ info.  Display that data only down to a level of granularity that helps me run the part of the business that I am responsible for.”

This is in fact a sub-set of the BI “story” and actually needs a different approach to data management than you would for that mythical analysis at all levels for all time.

Pre-Aggregation

Main point … Detail records are useless in business reporting and take up a lot of room and slow processes down and especially quickly become a security issue (if there is info in the details that expose data that is sensitive).

Detailed records are for querying individual invoices or shipments or variances in production on a specific date and time. They are the realm of the AR or AP clerk and the individual salesperson. A properly configured AR report is not just about one or two invoices, it is about trends in customer AR to see weak spots in your sales channel and to manage your financial risk.   If a line manager is looking at too detailed records then they are not able to see the “Big Picture”.  The need is to aggregate the information to the point where it tells you something about what’s happening or going to happen to your business.

You can of course use a A380 to fly 80 people a few hundred miles.  But there are many more Regional Jets and prop-jobs making short-haul flights than there are A380 flights … And it’s not only the size of the plane that makes the big plane a problem … it’ the overhead.  An A380 would just be over-kill at every level of the process.  Same with data frankly.

You might be surprised how a few repeatable, understandable techniques in Microsoft Excel can wrestle even large amounts of data better than most people realize. (I have posted about this before here and might revisit it soon).  Combined with all the capabilities originally built into Excel thanks to Power Pivot and Power Query combined with the advanced capabilities of Pivot Tables anyone can leverage their existing Excel models and skills to maximum advantage without having to retool to something like Power BI Desktop.  Other tools for Reporting (Tableau, Qlikview, iDashboard, Sisense, etc., etc.) have their own tech for this but fundamentally Excel can feed them all.  Or you can just use the summarized Excel results in Excel Dashboards or Reports themselves.

Don’t get me wrong, I am a database guy, having been a senior contributor to the Microsoft Access community for decades and especially using SQL Server and SQL Azure Databases as the backend.  I would be totally happy to just do all my Microsoft Power BI or Tableau dashboards using just database server sources directly … But Excel has ALL the hooks to ALL the data servers and sources everywhere that I might need.

Furthermore (rightly or wrongly) Excel is undeniably the largest “store” of organizational data and results in the world and this is not going to change anytime soon – nor should it.  Excel is also a great platform for new purpose-built data aggregation going forward.

Conclusion

Using Excel to pre-aggregate your organization’s data, the way it is intended to be used, will get people up and running with their “modern”, sexy business reports and dashboards with a minimum of cost, time, general disruption or risk to the running of their businesses. 

This is the reason we built the XLpublish Add-In which we built to close the gap between you Excel models and the disciplined table structures needed to publish your Business Reports.  It is the next natural step for Excel users in our opinion.  Without XLPublish it will continue to be hard to properly integrate your Excel results with your other organizational data collection.

With this series of posts I hope to lead us BACK to Excel not away from it.  I believe that Excel can and should be the core of quality “Business Reporting” today and tomorrow.

I will be discussing how this can be achieved in a series of posts right here . To be continued….

Dick Moffat

dick@xlpublish.com