What is Power BI and Why Should I Care?

As my regular readers may know, I am the co-organiser and co-founder for the official Power BI User Group here in Sydney.  During the period where COVID has impacted our ability to congregate face to face, Iman and I have been running virtual, online meetup events.  Last week I presented an intro session on Power BI titled “What is Power BI and Why Should I Care?“.  Many people (me included) have been using Power BI since the beginning, however there are always new people discovering Power BI every day.  Despite this being an absolution beginners session, there were many in the audience with Power BI experience that told me the session was quite informative to them. As a result, I decided to share the recording of the session here with you on my blog, and also answer some of the questions that came up during the live session. You can find the video below embedded below.

What I covered during the Session

As I mentioned already, the session is meant to be for those who are new to Power BI. So the focus was broad and covered how to Power BI can be used to benefit businesses. It is not exhaustive but it touched on the important points covered below.

• The Power BI Ecosystem  showing the various component parts.
• What Power BI is, and what it is not.
• My four stages of self service BI and how they are mapped to the Power BI components.
• Data Acquisition: how you can use Power Query to get and transform data to make it usable in Power BI.
• Data Modelling: how you can use DAX to extract data insights buried in the raw data.
• Visualisation: A high level overview of building visualisations on top of the data model.
• Sharing: How to share the reports on Power BI.com.
• Analyze in Excel:  how you can connect Excel to Power BI dataset in Power BI.com, to produce pivot tables without exporting static data to Excel.

The Video of the Session

Questions and Answers

There were some very good questions during the call; here are some of them along with my answers.

Cristian: Could you please tell us your short definition on what does “Self Service Business Intelligence” mean?

Matt:  We had Self Service business intelligence for ever.  It’s called Excel. My definition of self service business intelligence is the stuff that the end user does themself when they can’t get the IT department to do for them (for whatever reason). That reason might be because they are too busy, they don’t have time, it could be your requirements are short term. I like to use an example –  I used to work for Coca-Cola and we launched a product called Coke Zero into the market, we wanted to track sales for four weeks. Well, you might not get the IT department to build a brand new reporting tool just for a 4 week period. And so users would default to Excel and they would try and solve those problems themselves using excel. Self service BI is trying to bring together that enterprise strength and capability of proper BI tools and put it into the hands of people like me, Excel users who have a valid need to do reporting on a regular basis when it can’t be handled by the IT department.

Note:  Power BI is not just a self service BI; it’s also an enterprise BI tool.

Anon: What is the diff between Import and Direct Query?

Matt: Power BI has a stand alone database. When you use import mode, you are loading the data into the Power BI database. Technically it is Analysis Services Tabular – the latest version of SSAS that Microsoft has developed. The data is highly compressed and the resulting database is highly performant. It’s an excellent piece of software. However you don’t have to import the data into the database; you can actually do a direct query back against a SQL server database if you have that as your back end. So if you have MS SQL server, you can do a direct query back to the underlying SQL server database instead of using import mode. But if you do this, every visual in Power BI generates a query that is sent back to the database to be fulfilled. This implies your back end database needs to be highly performant; it needs to be structured in such a way that those queries that are generated by the visuals are quick to return the data.  Generally speaking I would recommend people only use direct query in 2 main use cases.  One is if it’s a very large database, i.e,  peta bytes, where there are billions and trillions of rows of data and where  taking copy as a refresh may be a problem,  the second reason that you might do direct query would be if the timeliness of the data is really important, e.g. if you need the data updated to the latest minute. But just keep in mind that if you choose not to do the import mode you’re not leveraging the latest DB technology and instead living with the underlying transactional database. That adds a whole new layer of responsibility on the back end to ensure that the transactional database is working at the performance level required to cover reporting, too.

Anon: Can the date table creation (M code) be shared?

Matt: I have a blog that shares the M code but also explains step by step how to create the calendar table using Power Query. To get this go to my website https://xbi.com.au and search for power query calendar table (here is the link https://exceleratorbi.com.au/power-bi-calendar-tables/) This takes you to my blog article where I teach you all the steps and also the M code that you can copy and paste into your query.

Nima: As the input data is growing gradually, the loading takes more time. Is it any way to not reload the whole data and only the new data when power BI refreshes?

Matt: Very good question. That’s called incremental refresh and yes, you can. Previously you needed Power BI Premium to do incremental refresh.  The entry price for premium is about 7000 Australian dollars per month. The good news is that Microsoft has extended the incremental refreshing capability to Pro users also, so this is now an affordable option.

Shaw: Is it possible to refresh specific queries only? Sometime it happens that when you link your query to SQL database it will take long time to refresh so can we stop that query to refresh every time.

Matt: The short answer is an yes and a no.  When you do a refresh using Power BI.com it refreshes every single query. It is possible to use the tool called Dataflows. Dataflows is a bit like Power Query in the cloud (Power Query is also part of Power BI). Dataflows is a standalone online deployment of Power Query and you can use dataflows to refresh all of the tables at different frequencies. So you can refresh the customer table once a month but a sales table every day. You still have to use Power Query to load the data into Power BI.com but at least conceptually you could see that if  you do not have to refresh all of the tables that process could be faster. It all depends on your data.

It is a highly requested feature to choose which tables get refreshed on demand. You could also use incremental refresh.

Anon: How to go about convincing on use of Power BI when SAP is being used?

Matt: I always recommend that you download Power BI onto you PC, start using it, and show people how great it is. Soon or later, you will get the attention of others.  Having said that, one of the biggest barriers to adoption of new software is the sunk cost in previous investments.  You can read my article I wrote about that here. https://powerpivotpro.com/2016/08/enterprise-bi-flawed-strategy-2016/