A Fine Slice Of SQL Server
Once saved, the Power BI file size was 289MB! Is this good for 10 million rows? It’s certainly better than the 360MB CSV file but not by much. Certainly not close to the 10:1 compression claimed to be achievable using the SSAS Tabular engine used by Power BI.
I think we can do better than that….
Read on to see the specific optimizations, turning this from a 289 MB data model into a 9 MB data model.
Comments closedIn many demo cases, you will have an all-in-one server where you have installed Power BI Report Server, SSAS (tabular or multidimensional) and SQL Server. In those cases you don’t need any form for credentials delegation since the Report Server is on the same box than the data source.
But there are scenarios where you have a distributed environment like the one I have on my VMs demo domain and for jumping around servers and passing credentials around, you need to setup Kerberos Constrained Delegation. Furthermore you will need protocol transition for it to work in Power BI Report Server.
Read on for step-by-step instructions showing how to do this.
Comments closedDevin Knight continues his Power BI custom visuals series:
In this module you will learn how to use the Play Axis Power BI Custom Visual. The Play Axis visual works like a dynamic slicer that animates your other report visuals without needing to click every time you want to change your filter value.
This is a valuable custom visual when dealing with time series data, but as Devin shows, you can iterate through other sets, like a set of employee names.
Comments closedDevin Knight has started a new series, walking through problems his clients have faced implementing Power BI solutions. In this edition, Devin wants to build a comma-delimited list to display on a tooltip:
This works perfectly for Stock because it automatically summarizes the value but, you’ll notice above that the tooltip for Subcategory has an interesting behavior. Rather than displaying the list of the values in Subcategory it actually just show the very first value. This happens because the Tooltip field requires that any column used in it be able to aggregate or roll up the values into what’s shown on the chart. Since Subcategory is just a text field Power BI automatically applies the FIRST function to return back the first value that appears. You could optionally change this from FIRST to either LAST, COUNT, or COUNTDISTINCT.
So the real problem I want to solve here is rather than only showing the first subcategory how do I list all the subcategories in a comma separated list in the tooltip? Let’s walk through a couple possible designs to this solution.
Read on for two different designs, including the code to implement the solutions.
Comments closedAdam Saxton has a video showing how to install and configure Power BI Report Server:
In this video, I look at how to install and configure the May 2017 Preview of Power BI Report Server. Power BI Report Server has a new standalone install experience and this product allows for Power BI reports to be rendered in the web portal along with paginated reports.
This will get you started with the new version.
I was really excited about this preview until I realized that, for now, it only works for Analysis Services data sources.
Comments closedDevin Knight continues his Power BI custom visuals series:
Key Takeaways
Let’s you expand and dig into each level of you hierarchy data.
Allows you to display a measure value at the lowest level.
Replaces the idea using multiple regular slicers to represent what the Hierarchy Slicer does.
This is a great visual if your dimensional data fits a good natural hierarchy.
Comments closedTo reuse an imported dataset, there are three options I’m aware of:
Report in the Power BI Service. This refers to using the web interface for creating a new report separate from the original PBIX file.
Analyze In Excel. This refers to creating an Excel report and can currently be used by anyone with read or edit access to the dataset. Hence, very useful for self-service BI purposes.
Power BI Service Live Connection. This refers to creating a Power BI Desktop report. This option can currently only be used by people with edit permissions on the dataset (so not appropriate for broad self-service BI reporting at this time).
Click through for a detailed demonstration of each.
Comments closedSo the magic of Power Query is instantly apparent and tangible to basically any Excel Pro. They can immediately see how PQ will save them oodles of time and anguish.
The benefits of DAX and relationships, by contrast, are less readily-apparent on first glance. Portable/re-useable formulas that enable rapid iteration, the answering of “emergent” questions in near real-time, as well as a “subdivide and segment” capability? Or how about multi-data-table capabilities that provide an integrated and convenient view across many different sources of formerly-siloed data? These concepts are simply alien to the longtime Excel user, even though they are MONSTERS in terms of their biz value (as well as time-savers and anguish-reducers). None of the impact “lands” up front because it can’t adequately be contemplated until you’ve started DOING it.
Rob’s looking at this from the standpoint of an educator helping train people with Excel expertise.
Comments closedThe error message is a little misleading but let’s save the debugging debate for another day. The key observation is “Guildford” data is not available, simply because it comes after “Camberley” in the list. Whilst we want to see errors in a Query, we do not want them causing data loss.
Resolution
As I mentioned at the beginning of this article, using the Remove Errors function would prevent the loss of Guildford data. However, the user needs to handle errors as Unknown Members and conform to a typical Kimball Data Warehouse.
I am sure there are many ways to fulfil the requirement, but here is how I approached it:
Read on for the resolution.
Comments closedMatt Allington shares his thoughts on the recent Power BI licensing changes:
I think the existence of the Power BI Free product has been the root of the problem here. The fact that you could do so much for free (including some sharing) really muddied the waters and has taken the focus away from acknowledging that there needs to be a two tier pricing model for users (free is not a pricing tier). Microsoft is addressing one part of the problem by making it clear that Power BI Free is for personal (non sharing) use. However it has not addressed the second part of the problem being the need for a lower priced offering for users that just consume data in a way I would describe as “low involvement”. Microsoft has taken away the “proxy for a low priced sharing tier” without providing a genuine low priced replacement – this had just made the situation worse, not better and it has upset a lot of people. Power BI Free has been a great product to “try before you buy” but unfortunately its existence prevented Microsoft from realising it was missing a price tier for 2 years! Power BI Free for personal use (no sharing) is an incredibly generous offering from Microsoft. It is a shame that it will need a backlash to fill the real gap – a lower priced tier.
Check out the comments as well. I think Matt has a good point, and my guess is that the Power BI team will make it easier for small to medium sized businesses to use Power BI, but they first wanted to focus on the problem with big customers.
Comments closed