You got your Power BI in my Excel!?!?
I know, I know, I know... no one wants to see the spreadmartsblossom and grow. IT doesn't want it. The business doesn't want it. Heck, Analysts don't want it. Trapping data in hundreds of millions of spreadsheets across your network is a horrible way to manage data for any organization.
Yet, there is a reason that every organization continues to fight the fight against that ever growing spreadmart ecosystem.
Spreadmarts grow because they work to address short term needs of analysts or the business that IT cannot simply handle. The needs for spreadmarts are being addressed by Self-Service Power BI, but even that does not necessarily address all of the needs of business.
We now have the ability to integrate Enterprise Datasets hosted in the Power BI service with the work analysts do in Excel. The combination of Power BI and Excel offer us the opportunity to enable analysts to continue to run and gun in Excel without having to worry about data integrity issues common within spreadmarts.
I am not going to get into too much detail on the GREAT new security features that Microsoft and Azure is rolling out, but soon you wont even have to worry about files being emailed off of your network with protected information. Insane right???
You Never Know When an Excel File Becomes a Production File
For a long time, Excel has been a hidden gem in the Microsoft Business Intelligence (MSBI) portfolio. While there are many great books on the amazing things that you can do in Excel, many people didn't realize that Excel was the birth place of Power Query (M), Power Pivot (xVelocity - Tabular engine) and Power View. These three pieces of software have evolved into the Power BI platform.
This Excel Seed has evolved over the course of more than a decade of working with Power Query, Power Pivot, Power View and Power BI.
By using this Excel Seed, you retain the ability to use Enterprise Datasets, while allowing analysts to use all of their existing Excel features. While Power BI is awesome, and the future for analytics, Excel has been around for over 30 years and a slew of features and capabilities that Power BI simply does not offer.
On countless occasions, I have found that quick bit of analysis that I did in Excel became a guiding benchmark for teams, divisions or even entire companies or industries.Using these Excel Seeds for any Excel work ensures that the work you are doing has a polished look and feel, becomes a common UI, and has a large number of features that you don't have to recreate in each excel file.
"Three may keep a secret, if two of them are dead" Benjamin Franklin
Tabs within the Kratos BI Seed fall into three categories
- Visible to Users
- Optional for Users (Yellow)
- For BI Developers (Red)
When you create a new Seed, take the time to hide any of the tabs that you will not be using or sharing with your users.
The Excel Equivalent to The Hitchhiker's Guide to the Galaxy
It's very easy to grow a large number of tabs within Excel for different purposes. The analyst that created the spreadsheet will know how it works, but navigating someone else's work can be very difficult. The Index tab works as a guide through out the workbook to the various tabs with a description of what each should be used for.
Feel free to Add more rows or columns to your Seed. It's Excel after all! Super easy to customize and make your own.
Who Doesn't Love a Sandbox?
The Sandbox is the single most powerful tab and mechanism within the Excel Seed. The Sandbox contains a pivot table linked to your data source that allows users to connect to the data, drag and drop fields, and perform all kinds of additional measurements.
The Sandbox is used to create new tabs, build out focused and detailed charts and graphs that create specific analytical views of your data.
Look for more detailed write-ups and videos on many things that you can do with this Sandbox.
Webster aint got nottin on you!
Use the Definitions table to document the data that exists within your Dataset. This is HIGHLY effective and necessary if you are using Power Query and Power Pivot to shape the data from within Excel.
If you are using data from an Enterprise data source, then simply hide the tab and ignore it!
Enable People by Telling Them How To Use Your Work
Use the "How To" tab to document how users can use your Excel Seed. This is an excellent place for screenshots, narratives, and specific use cases that are addressed within the Seed.
This Can't Be Right!
While the Sandbox is the most powerful tab for the vast number of things that it can do, the Balancing tab is out of the box one of the more useful concepts in the Seed. The Balancing tab is designed for you to be able to validate the data in the enterprise cube vs a validation data set. The Test Values calculations allows you to easily see if your dimensions are not matching or if any of your values are off (and by how much)!
This is the BI Testing Team's most useful tab. If you are in testing, or know someone in testing, please please please, spend a great deal of time with this.
Move at the Speed of Business
As a Seed grows and evolves, you will want users of the Seed to know about changes that you have made (or are planning to make). The Updates tab was designed as a single place to document the various updates and enhancement that you make to the Seed.
On Brand and On Message
Many companies have a Branding guide of approved colors and logos to be used in any official materials published by the organization. The Branding Tab is used to capture the branding colors, themes and logos to be used within the Seed.
When you are customizing the Kratos BI Seed for your own use, you should start working on getting this branding in-line with your organization.
This Bud's for You
The BI Admin tab is used to contain all of that stuff that you may want to keep hidden from users. Use this tab as a central place for lists, reference cells, instructions for updates, etc.
When You Need A Clean Slate
Use the Template tab to create a new clean tab for any other documentation, manual imports, data or any other needs you may have for a clean tab without a pivot table on it.
Don't forget those Random Queries
Hopefully you are adding any random queries into Power Query.
If you are manually running a query and copying the data into the Excel file (for what ever reason you have), paste the query into this tab along with the necessary information for someone else to be able to run the query.
Really though... best to put the query in Power Query.
Summary
The Kratos BI Seed combines Power BI and Excel to be used by analysts of all shapes and sizes for a million different purposes.
I hope you find this Seed helpful in leveraging the power of Power BI and Excel.
