Excel and BI: Using Excel as a Visualization Tool

Post date:
Post views: 2,174

Although Excel is not the best tool for a BI platform (see I Don’t Need BI–I Have Excel), it can perform as a serviceable visualization tool for a full BI platform. Excel allows for very nice formatting and good basic graphics. And as I’ve noted in the last post, most organizations already have Excel and most users are familiar with how to use it.

Let’s dig into this a little deeper using BIO business intelligence from BIO Analytics Corp. as the underlying business intelligence (BI) platform. Using Excel, you can bring in the BIO data by creating formulas that are based on the intersection of the members. However, what remains in the cell is not the data, but the formula to call up that piece of data. For instance, if you have a report that looks at product p&ls by month, you can create a formula that says in this cell, fetch the result of the intersection of x month and x account and x product and x region from the BIO data. Once the data is in the spreadsheet, because you a using Excel strictly as a viewer, you can create calculations, like percentages and ratios, without the error-prone process of referring back to cell addresses. If you want a column called Gross Margin Percentage, you create that definition just once. Then every time you want to use that percentage, it’s available. And every time you refresh the page, the most current data is inserted into the cells.

Just to be clear, we are talking about using Excel as the viewer for BIO, we are NOT talking about using Pivot Tables. With Pivot Tables, you would not be able to create new calculations on the fly without disengaging from the data source, you would not be able to add a new column right from the screen without re-programming, and you would not be able to change the formatting without significantly more effort. You can see how easy this is in this short video. Further, using BIO as the underlying platform gives you access to the BIO content, such as pre-defined cubes, user-defined hierarchies, masks, and ranges, no matter which viewer you chose.

BIO business intelligence comes with BIOVue, a workhorse viewer that provides reporting, dashboards, and analysis for all users from analysts to casual and non-technical users. BIOVue comes with 50 out-of-the-box reports and lots of additional viewer-specific content, like an in-grid calculator, traffic lights, and more. However, with BIO, you can use any of the visualization tools on the market today, including Excel.

To learn more about BIO, click here to register for an upcoming webinar. And please contact me at 203.705.4648 or by email at sandi@bio4analytics.com if you have any questions about BIO or business intelligence in general.

By Sandi Richards Forman of BIO Analytics Corp., Microsoft Dynamics Business Intelligence (BI) Solution Provider

Leave a Reply