Press "Enter" to skip to content

Category: Power BI

Power Pivot Or Power Query?

Published 2016-03-17 by Kevin Feasel

Avi Singh explains when to use Power Query versus Power Pivot:

On Power BI Desktop, you don’t even have a choice – the only route to connect to data is via the “Get Data/Power Query” interface. Which is A-Okay with me. Even with Excel, I now connect to ANY data using Power Query.

Use Power Query to fill all your Get Data needs

Yes, ANY data. Even if I could connect using Power Pivot to those data sources and did not need any transformation  – I still always use Power Query.

Power Query to get data, Power Pivot to model data.  Avi then gives a few examples of scenarios, explaining where each fits in.

Comments closed

Data Modeling In Power BI

Published 2016-03-14 by Kevin Feasel

Ginger Grant discusses creating models in Power BI:

When comparing the features of the Online Client with the Desktop version of Power BI, there is one very obvious difference, there is no way to create a data model in Power BI online. It is not possible to create a data model using the online client. The online client is designed to connect to an existing online source such as Sales Force or Azure DB. If you are using an existing model, there is no need to create one. When using the enterprise gateway, which uses an on-premises database such as a SQL Server, SSAS or Hana, the data model is contained within the database exposed via the enterprise gateway, so again no reason exists to create a data model. Report creation can occur either using the online client or desktop as there is compelling technical reason that I am aware of which would determine where the report is created.

Cf. an earlier post on Power Pivot and Power BI modeling.

Comments closed

Asking Questions With Power BI

Published 2016-03-14 by Kevin Feasel

Reza Rad shows us how to interrogate Power BI:

When you published your Power BI file into the Power BI desktop, usually you create a dashboard for it. For Power Q&A to work (the version of Power Q&A at the time of writing this post) you should create a dashboard for your report. After creating the dashboard you will see the question bar of Q&A on the top of your dashboard.

My preferred technique is good developer, bad developer.

Comments closed

Custom Power BI Visuals

Published 2016-03-10 by Kevin Feasel

Reza Rad shows us how to create a custom visualization in Power BI:

This is one of the coolest custom visuals I’ve seen so far. The reason is that this custom visual has a customization in it! with this visualization you can define regions in any picture or images, and map data points to the image in your Power BI report. The image can be everything; human body, airplane seat layout, shop floor layout or football field. You choose the image you want, then you define regions. Let’s have a closer look at this visual.

It’s amazing how easy Power BI makes that.  Almost easy enough for me to do it…

Comments closed

Movie Analysis Using Power BI

Published 2016-02-26 by Kevin Feasel

Dan English compares Marvel versus DC movies using Power BI:

This is an interactive report comparing the results of Marvel versus DC movie information with regards to the number of movies, adjusted worldwide gross box office earnings, and includes IMDb ratings. You can get a feel for the shift from the 1960’s through the 1990’s as DC dominated the market and then Marvel stepped in and has dominated the box office since.

This is a fun data set and dashboard.

Comments closed

Power BI Roundup

Published 2016-02-23 by Kevin Feasel

Jorge Segarra has a roundup of this month’s T-SQL Tuesday:

This month I challenged the blogging community to share their own creations in Power BI. We got a ton of great entries this month, thank you everyone who participated! My overarching goal for this month’s topic was to get folks who may not normally play in the BI space to use this fantastic solution and maybe get some ideas flowing on how they may be able to apply it in their everyday work.

The part I like most about T-SQL Tuesday is that it introduces you to a whole new set of bloggers and a whole new set of perspectives on any particular topic.

Comments closed

Power Pivot And Power BI Data Modeling

Published 2016-02-22 by Kevin Feasel

Avi Singh has a post on data modeling in Power Pivot and Power BI:

It was Greg, who suggested that we form a book reading club. Our first book was one I had heard about, but never read – The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling By Ralph Kimball. As a business analyst, I had leaned heavily on Excel, along with a mishmash of other technologies. Data warehouse and data modeling didn’t seem like topics that would be relevant to me; more for an IT/BI team perhaps. But I figured, it couldn’t hurt to learn something new.

Our book club meetings looked more as if, class was in session. We brought in our questions, and Greg patiently answered them, helping us realize the importance of the topics, and trade-offs involved in various choices. As things go, our reading club was disbanded before we were even halfway through the book. But the knowledge that I had gained, helped me grow by leaps and bounds in my Power Pivot and Power BI journey.

Kimball-style fact-dimensional modeling remains a brilliant solution.

Comments closed

Getting Into Power BI

Published 2016-02-22 by Kevin Feasel

Jorge Segarra jumps into Power BI:

For my contribution to this contest I’ve decided to share with you a work in progress. If you know me, I’m a huge lover of Policy-Based Management. In fact, I’m actually part of the Enterprise Policy Management Framework (EPMF) project on Codeplex. T-SQL Tuesday event is normally a DBA-centric event so I figured I’d help the DBA crowd wrap their heads around how a BI solution can help them in their day to day.

What I did to kick start this effort was to create this Power BI report that allows you to explore the database repository that contains the EPMF policy evaluation results. The current EPMF project uses Reporting Services to deliver its reports. This won’t change. If anything I’ll be exploring new capabilities with SQL Server 2016 and R-integration. Here’s a screenshot of what the SSRS dashboard report looks like:

I like this post because most Power BI examples tend to be personal (Fitbit stats, etc.) or business-y.  This is a good example of a use of Power BI for back-office database administrators.

Comments closed

Power BI With SSAS

Published 2016-02-19 by Kevin Feasel

Jens Vestergaard’s T-SQL Tuesday entry involves Power BI feeding from Analysis Services:

My story with this half-baked product (the Dashboard you are about to see), is that I needed some way of tracking performance on a couple of Analysis Services (SSAS) query servers. There are a lot of good posts and talks about how to collect and store performance counters and SSAS logs out there, and I suggest you look into this, this or that, if you need inspiration.

The current data set is about 200K rows, as I am sampling each server every 5th minute.

Both of these are valuable tools in a Microsoft BI environment.

Comments closed

CSVs With Line Breaks In Power BI

Published 2016-02-17 by Kevin Feasel

Chris Webb talks about one of the banes of my existence:  flat files with “rogue” line breaks:

But what if you can’t fix the source data? I was asked this question the other week, and since I had been asked about it before and not come up with a good answer, I decided to spend some time researching the problem.

What I found was that it was relatively easy to write some M code that gave me the correct results, but very hard to write code that performed acceptably well on a large data set (I was testing on a CSV file containing almost half a million rows). Here’s the code for the function I ended up with:

It’s nice to see that Power Query & Power BI have methods to get around this sort of issue, but it sounds like even those methods are limited in value.

Comments closed