Power BI and Spotify – A match made in heaven?

Data isn’t life. They say music is. Well, is it? Wouldn’t it be awesome to simply combine both worlds? Let’s find out if this could turn out to be a perfect marriage.

Today, with approximately 286 million users to date, including 130 million subscribers,
Spotify can be considered as one the world’s most popular music streaming services.
In a remarkable coincidence, I happen to consume their service every once in a while myself. Actually, this is an understatement as I stream music basically on a daily basis:
During travel, development, while exercising or even when writing this blog at home.

What would we like to find out?

In regards to my streaming behavior, I came up with the following questions:

> What artists did I favor the most

> What songs did I listen to the most ?

Can certain events, such as festivals, cause a change in my streaming behavior?

> When did I stream music?

What KPI’s can we define to provide us the answers with?

Of course we need to quantify these questions based on a KPI. I believe there are two ways of measuring whether I like or dislike a specific song. The first one is the time I actually listened to the song, which I will be referring to as the listening time (in minutes). The second KPI is the song count, being the number of times a song started, regardless of me manually pressing the play button or Spotify’s autoplay starting the song.

Where can we gather the data we need?

Spotify offers their users to retrieve data through their Spotify API. Recently, they launched a refined version of this API which, at the moment of writing, is still in BETA. I can only praise Spotify for how well they document their API. They offer a clear process description on how to connect to their API, written down call definitions and even providing developers a test environment console. More information can be found here.

Evaluation time!

As we now have all data in place, we can assemble our very own Spotify dashboards with Power BI. Let’s analyze my streaming behavior of the past year by trying to formulate an answer to our predefined questions. 😊

What artists did I favor the most?

Post Malone seems to be the clear winner based on my streaming behaviour, there’s no doubt there. I tend to have a preference for R&B with Tyga and Blackbear claiming a spot in my top 5 artists.

What is interesting here is that my top 5 artists slightly differs when switching between our two predefined KPI’s. Spot 1-3 are identical, regardless of the KPI we base ourselves upon. However, in terms of song count, Nickelback and Loud Luxury are my respective number 4 and 5 whereas, in terms of play time, Loud Luxury and Blackbear take in those positions. This can have two possible explanations:

1) Songs of Nickelback are simply shorter than Blackbear songs

or

2) I’m excited to hear Nickelback songs’ refrains and get bored afterwards, forcing myself to switch songs once the refrain passed by.

What songs did I listen to the most?

It wouldn’t surprise you I listened more than 150 times to Post Malone’s top songs such as A Thousand Bad Times, Sugar Wraith and Goodbyes.

Can certain events, such as festivals, cause a change in my streaming behavior?

Yes! Last August I attended the Sziget festival in Hungary. According to my streaming history, I enjoyed Post Malone’s performance so much that the following months, Post Malone skyrocketed and became my number one streaming artist.

When did I stream music?

It turns out I’m actually getting value out of my monthly subscription. The past year, I streamed 357 out of 365 days. My listening time stays more or less steady throughout the week. During the work week, I mainly stream music traveling between home and work. And if you’re interested; yes, I’m not an early bird. 😊

Wrap-Up

This article described one way of vizualising your Spotify streaming behavior with Power BI. Of course there are many more insights these reports can be enriched with. I tried to provide a high level summary of how I created this analysis. If you’re interested in the technical details or would like to get help upon solving your own data visualisation enquiries, do not hesitate to get in touch with me or any of my colleagues at Lytix.

All aboard the data train today, CHOO CHOO 🚂 

Lou Segers
Microsoft Analytics Consultant @ Lytics
Follow me on LinkedIn

10 Performance tips to speed up your Power BI reports

Arriving at work and the first thing you do is opening your Power BI report and going for a coffee while waiting for the report to render. Seems familiar?

Well, it shouldn’t. Your reports should render instantly, especially if there is a large or C-level audience consulting them on a regular basis. Luckily enough there are some tips and tricks to melt your frustrations like snow in the sun.

1. Only keep necessary visuals

Every single additional visual needs time to render and will therefore slow down your report. And if, in some cases such as when relying upon bookmarks, you really see added value in enriching your report with a bunch of shapes or data visualizations, please do hide them whenever possible. As hidden visuals won’t be rendered, they do not have a single impact upon report performance.

2. Use integers instead of text values whenever possible

String values do not compress as well as integer values. By replacing string values with integer values, preferably at source level, both .pbix file size as total memory size will reduce exponentially. Examples: Adjust data types of numeric codes to integer values where possible and use 1/0 instead of TRUE/FALSE.

3. Stick to Power BI’s default visuals

Report developers are often very tempted to scroll through the list of third-party visuals, which can be found in the marketplace, and end up importing them. In some specific business scenarios, they can actually be very suitable. However, please do use them with care. The biggest performance wins I’ve ever made was accomplished by simply converting third-party visuals to default visuals.

4. Avoid both-directional relationships in your data model

The number one performance killer. A both-directional relationship between two tables actually means both tables will cross-filter each other. This has a serious drawback on report performance as every interaction is very plausible to cause a loop of filtering actions, especially when your data model consists of multiple enabled both-directional relationships. Optimal data model design is key. Alternatively, DAX’ CROSSFILTER() function can come in handy as well.

5. Understand the difference between duplicate and reference queries

Duplicate queries will simply serve as a one time copy of the M query code whereas referencing a query also pushes through any changes performed on a referenced query to the queries referring to this query. Does this impact report performance? No, but it goes without saying the latter improves query maintainability as you only need to adjust the referenced query.

6. Only load data which will be reported upon

What about the options to “enable load” and “include in report refresh”?

Many of you have probably came across these options when developing reports but don’t exactly know how to correctly interpret their function. When the enable load checkmark is unchecked, data won’t be loaded into memory. The respective query will still be available in the query tab but will not be cluttering your data modeling or report view. This is often used for intermediary queries: intermediate results you want to refer to or merge/append other queries with. There is a clear distinction with the “include in report refresh” option: when unchecking this checkmark, data simply won’t be refreshed when you click the refresh button of your report. These query fields will still be visible in your data modeling and report view if you have the “enable load” option checkmarked. This feature is perfect for preventing static data from redundantly refreshing. Additionally, incremental refresh can be configured as well for example to prevent frozen historic data from refreshing unnecessarily. This feature used to be premium exclusive but it is generally available as of the end of February 2020 for Pro users as well, so make sure to check it out!


7. Slicers can be real performance killers

These are quick wins: change your slicers to dropdown instead of list. When a user opens a report, all (non-hidden) visuals require render time: the time Power BI needs to actually load & show a data visualization. Slicer lists will last longer to render in comparison with slicer dropdowns as the former presents filter values on report initialization. The latter will only load filter values when a user triggers the dropdown.


Even more important, is the slicer visual selection. As stated before, you should try to stick to default visuals if possible instead of relying upon third-party visuals from the marketplace. However, there are some value adding visualizations in there as well. To provide you with a clear example visual significantly contributing to user experience, the market place’s portfolio includes the so-called ‘HierarchySlicer’. This visual -unsurprisingly- enables users to add multiple fields to one and the same slicer, creating a hierarchy. At the time of report creation, Power BI did not offer the possibility to add multiple fields to their default slicer.. YET. As of Power BI’s February 2020 update, the default slicer now also supports hierarchies. In fact, one of the biggest performance wins I came across existed in simply changing a the third-party ‘HierarchySlicer’ to the Power BI default variant reducing report render time with an astonishing 90%. Continue reading to learn how you can measure performance.

 

8. Overthink your data modeling technique

I’ve had fantastic experiences reporting on a dimensional model. Bottom line, creating report look and feel should only take up a small proportion of your total efforts spent. Designing the right data model will mainly decide whether your reports will behave in a performant way. I am aware there are several ways to design a data model but I can only strongly advise to stick to Kimball’s approach. Other best practices you should keep into account exist in:

> The use of surrogate keys

> The use of role-playing dimensions

> The determination of fact(s), possibly enabling parallel loads

> No snow-flaking

Please do mind I only scratched the surface of dimensional modeling here. Always try to question your decisions in your journey to the ideal data model. Critical thinking is key. In most cases, an additional pair of eyes won’t harm.

9. Split granularity to lower cardinality

To perform calculations one or more requests are sent to the storage engine where the data is stored. This could be either an external relational engine (DirectQuery) or Power BI’s storage engine, called the Vertipaq engine (Import). Due to the variety of DirectQuery connections, I’ll be focusing on the latter. In contrast to a traditional SQL database, which evaluates data row by row, the Vertipaq engine saves data into memory in a columnar format.

With this small introduction to the Vertipaq engine, I want to draw your attention to how Power BI handles its imported data: the higher the cardinality of a column, the longer Power BI will need to perform its calculations upon this data. Put in other words: try to limit the number of distinct values of an attribute.

For example, you could split datetime fields in a date and a time field. Consequently, memory load will be reduced to a minimum as both fields will have a much lower cardinality in comparison with the original datetime field. This is something very case specific but I hope you were able to grasp the idea.

10. Measure performance !

Get a clear view on how a report is currently performing so you can quantitively measure and compare the outcome of any of your taken actions. Power BI has a built-in feature, called the performance analyzer, enabling users to measure how much time is spent on rendering visuals and how much time is spent on executing DAX code. It even gives you the full extract of the DAX code, which you can further analyze in tools as DAX Studio.

Remember the difference between Power BI’s updated default slicer and the HierarchySlicer, imported from the market place? I’ve compared their performance on one of my reports resulting in the following excerpts:

Wrap-Up

The above tips and tricks are some of the actions you could opt for while tuning performance of Power BI reports. I did not have the intention to assemble a complete list. There are several more options but I can only advise to start with the ones I’ve described above. If none of them result in the performance you were hoping for, do not hesitate to get in touch with me or any of my colleagues at Lytix.

All aboard the data train today, CHOO CHOO 🚂 

Lou Segers
Microsoft Analytics Consultant @ Lytics
Follow me on LinkedIn

Power BI Governator: Auditing Made Easy

Power BI Governator

There’s no denial that Power BI has been maintaining a steep growth since its release in 2015 in terms of capabilities. Consequently, the same positive trend also applies to its community and user base, shifting greater priority towards the art of Power BI governance. Nowadays, companies begin to feel the struggle they’re up against when simultaneously trying to encourage Power BI adoption and keeping a crystal-clear overview of their Power BI landscape. If you’re new to Power BI governance or interested in how to perform a Power BI rollout in a governed self-service way, make sure to check out our previous blog as well.

While Power BI is gaining momentum within your company, the number of workspaces, dashboards, reports and datasets often rise significantly. This makes it harder to maintain an overview, especially in a self-service context in which users are pertmitted to create and publish reports to collaborative workspaces theirselves. This is where Lytix’ Power BI Governator comes into play.

Power BI Governator to the rescue!

The Power BI Governator is a plug-and-play solution built in Power BI, which enables companies to easily govern both their Office 365 and Power BI landscapes upon prefabricated reports.

In se, a data model has been built combining three difference sources being the Microsoft Graph API, the Power BI API (which also is a part of the O365 API) and the Office 365 Audit Logs. All sources support scheduled data refreshes in the Power BI service portal.

Want more information?

Feel free to contact us if you would like to receive detailed documentation or request a demo of the full version of the Power BI Governator. There are several more valuable insights which can be showcased in no time!

Wrap-Up

Thanks for reading through this blog post, we at Lytix hope you’ve learned something new.
If you need advice on your data & analytics landscape,
send a message to Lytix or the authors Lou Segers and Arne Polfliet we’ll be more than happy to introduce ourselves. 🤜

Lou Segers
Microsoft Analytics Consultant @ Lytics
Follow me on LinkedIn

Arne Polfliet
Microsoft Analytics Consultant @ Lytics
Follow me on LinkedIn

Azure Analysis Services vs Power BI Premium

Azure Analysis Services vs Power BI Premium

One of the most burning questions we get asked while designing the architecture of a Microsoft based BI solution is how to make the trade off between Azure Analysis Services and Power BI Premium. As Microsoft clearly stated the latter to be considered their primary enterprise BI platform, opting for the former still gives us a slight edge over Power BI Premium in terms of available features. This article will get you acquainted with both technologies. We’ll also provide you with a nifty embedded Power BI report to help you sort out the right technology for your project!

Can’t wait to make the trade-off yourself until the end of this blog? 

Are you already familiar with both Azure Analysis Services and Power BI Premium? In analogy with Dave Ruijter‘s absolute guide to selecting between AAS and Power BI Premium (make sure to check out Dave’s blog as well), we’ve composed the below shown interactive and embedded Power BI report which enables you to make a well-considered decision yourself. It will serve as a starting point, guiding you towards the right technology according to your business requirements. Feel free to play around as much as you’d like! Eager to learn about the differences of technologies? Continue reading all about it and impress your colleagues with your newly acquainted knowledge! 

Note: You can simply click Yes or No for some or all specified evaluation topics. Based on your selection, one technology will triumph.

What is this Power BI Premium hype all about?

Power BI Premium is -unsurprisingly- considered as Power BI on steroids. Instead of purchasing Power BI Pro licenses for individual users, Power BI Premium main focus lies on providing both the necessary amount of space and processing capacity you want to carve out for your business. This service is intended for companies with a large user base where extensive app access is required throughout the company.

It’s important to note Power BI Premium isn’t really a license an sich: you are paying for an exclusive use of a predetermined amount of computing power. Users who would like to publish their reports, share their dashboards and collaborate with their beloved colleagues in app workspaces, the so-called “contributors”, will still need to be assigned a Power BI Pro license. “Consumers”, being users who only need access to an app and consume Power BI content, do not need to be in the dispose of a license.

Premium… As in daylight robbery?

The dedicated resource Power BI Premium offers is priced by capacity, for which the minimum capacity level, P1, currently starts at a cost of €4200/month. Additional Power Pro licenses, currently valued at €8,4/user/month, need to be purchased on top of the price you pay for this dedicated resource.

As a rule of thumb, I would advise to consider Power BI Premium as a sensible option if your business consists of 400+ Power BI Pro users. You can easily calculate the total cost, being the desired Premium capacity on the one hand and the number of Pro licenses on the other hand, yourself by dividing your user base into three levels of involvement, as indicated below.

This tool is publicly available here.

At first glance, this seems to be one hell of a costly service. However, you should keep in mind this premium capacity plan offers all users the ability to consume reports once they’re published in an app. Without this premium capacity plan, this wouldn’t be feasible as a Pro license would be required to view content in the app as well. By cleverly indicating your Power BI key users, and thus not upgrading every employee who claims to be in desperate need of a Pro license, purchasing additional Pro licenses can be limited significantly. For contribution purposes, such as sharing and editing reports in app workspaces, a pro license is still required.

Power BI Licensing explained

Microsoft brought into existence two different license types.

Per-User: Power BI Pro licensing and free licensing.

A Power BI Pro license enables access to all content and capabilities in the Power BI service, including the ability to share content and collaborate with other Pro users. Only Pro users can publish content to app workspaces, share dashboards, and subscribe to dashboards and reports. A free license enables access to some of the features of the Power BI service.

Capacity-based: Power BI Premium licensing.

Premium provides dedicated capacity to deliver more consistent performance and support larger data volumes in Power BI. For individual users, Premium also enables widespread distribution of content by Pro users without requiring Pro licenses for recipients who view the content.

Confused yet? Below summary is all you need and will help you tidy up your mind regarding Power BI licensing. 😊 

 What about Azure Analysis Services?

Azure Analysis Services (AAS) is a fully managed platform as a service (PaaS) that provides enterprise-grade data models in the cloud. Use advanced mashup and modeling features to combine data from multiple data sources, define metrics, and secure your data in a single, trusted tabular semantic data model. And this is the most important term to remember: semantic layer. This semantic layer is actually a layer built on top of multiple sources which enables performant slicing and dicing of your data. When opting for this technology, a company shifts its development towards their IT department, instead of their business users, making it easier to manage new business requirements of an existing data model and enabling users to report upon one single version of the truth. 

This service can be directly purchased from the Azure platform and thus supports all advantages which are typical for this platform such as

> Scalability

> DevOps experience

> Azure AD integration

> Global availability

> Flexible expenditure.

You can actually temporary turn of this service, saving costs at times no end users are reporting.

Your turn!

Congratulations! You’re now fully up-to-date about both Azure Analysis Services and Power BI Premium! Head over to the Power BI embedded report at the top of this page and put your knowledge into practice now! 

Wrap-Up

Thanks for reading through this blog post, we at Lytix hope you’ve learned something new.
If you need advice on your data & analytics landscape,
send a message to Lytix or the author Lou Segers and we’ll be more than happy to introduce ourselves. 🤜

Lou Segers
Microsoft Analytics Consultant @ Lytics
Follow me on LinkedIn