In a previous post (click here), we looked at how to quickly connect Acumatica to Power BI with nothing more than a few clicks. Of course, the connection was safe and secure. It just happened to be easy to do.
But the out-of-the-box content pack for Acumatica in Power BI is merely scratching the surface by showing a limited amount of CRM data. It’s a teaser that gives you a quick glimpse of Power BI, but leaves you wondering if you can do more.
A Sad Story
Many people follow the steps in the previous post (click here) and connect to Power BI, but they can’t figure out how to do more. Disheartened and discouraged, they conclude that Power BI really isn’t that great and people like Tim Rodman are fools for writing so much about it. While their Acumatica database contains a massive amount of data, potentially more valuable than Donald Trump’s real estate holdings, Power BI can only see a sliver of the data, the equivalent of one puny gold watch to the Donald.
Alas, I wish I could reach out to these people and explain to them that there is more. I wish I could catch them at the digital trash can when they are about delete their Power BI account from their computer. I wish I could be there to tell them the good news that there is indeed more, that the out-of-the-box content pack is merely a pretty bow on a box that contains a diamond ring. Sure, the bow looks nice, but the diamond ring is so much better.
In order to get to that proverbial diamond in the box, you need to open the box.
And that box is called Power BI Desktop.
Display Engine vs. Calculation Engine
PowerBI.com is primarily a display engine. You can build some quick dashboards on-the-fly, but the powerful modeling engine that sits underneath isn’t exposed.
In order to build your own model, you need the Power BI Desktop application. With it, you can connect to data sources, pass the data through a cleaning/shaping process, then relate multiple data sources together in the model.
Lastly, you can create measures that sit on top of the model. The measures contain the calculations that ultimately deliver meaningful numbers to the display engine so it can apply the pretty visualizations to the user, the cherry on top.
The calculation engine matters and the Power BI Desktop delivers a calculation engine in a big way. First, as you are connecting to a data source (like Acumatica OData), you can run the data through a series of steps in order to clean and shape it. You can apply filters, add columns, split columns into multiple columns based on delimiters, transform date/time fields, and a lot more. Actually, the cleaning/shaping process has its own language called the M Language. As you are clicking buttons in the graphical user interface, the M Language code is getting generated for you, much like recording a macro in Excel. So, you can start in the graphical user interface and then move into editing the code directly if you choose so you can do even more advanced stuff.
Once a dataset comes in, it comes in as a table. But you can bring in as many datasets as you want to the tabular model. Once the datasets land in the model, you need to define relationships. Relationships in Power BI are very similar to joins in SQL, but not quite the same. As far as I have been able to tell, the Power BI Tabular Model works best with a Star Schema. The transactional tables which tend to have a small number of columns, but a large number of rows, are referred to as Fact Tables. Then the master record tables which tend to have a large number of columns, but a small number of rows, are referred to as Dimension Tables. The Star Schema typically has a Fact Table or multiple Fact Tables at the core with Dimension Tables circling around like points on a star.
The Power BI Desktop gives you a nice graphical way to view the relationships between your tables. It looks a lot nicer than the schema designer in SQL Management Studio. This helps a lot when you want to take a quick look at a Tabular Model in Power BI to see how everything is related.
When it comes to creating measures that sit on top of the model, my favorite thing is that working with measures feels like working with formulas in Excel. Many of the functions are the same and the feel of being able to type a formula while seeing what it requires also is the same. Just as the cleaning/shaping language is called M, there is a language for Measures and it is called DAX. DAX was created by the SQL Server Analysis Services (SSAS) team at Microsoft, not the Excel team, so it delivers the power of SSAS, but with the usability of Excel.
Why a desktop application?
I really can’t put this any better than Rob Collie, so I suggest that you read his post (click here).
I have been using Power BI Desktop for my Timesheet at work (another topic for another day) and I have found so far that it’s actually easier to work with Power BI Desktop rather than the web version of Power BI.
Now, if I want other people to use what I created, then we can easily push the entire Power BI model complete with visualizations out to PowerBI.com where other people can consume it in their Web Browser or on the Power BI Mobile App.
But nothing beats a desktop application for authoring content.
As a matter of fact, I’m sitting here in the rocking chair waiting for my 2 year old son to fall asleep so I’m typing this post into the Microsoft Word app on my iPhone (great app by the way). And my thumbs are hurting. It would be a much more pleasant experience if I were typing on my laptop.
How to Get It
The good news is that Power BI Desktop is really easy to get. In fact, you don’t even need a PowerBI.com account. Just go to PowerBI.com and do two clicks: