A Fine Slice Of SQL Server
Devin Knight continues his Power BI custom visuals series with the Route Map visual:
In this module you will learn how to use the Route Map Power BI Custom Visual. The Route Map uses latitude, longitude, and time to show the trajectory of an object on a map.
For a certain class of dashboard, this is quite powerful.
Comments closedUst Oldfield shows how to use Hive via Azure HDInsight as a data source for Power BI:
As Hive is part of the Azure HDInsight stack it would be tempting to select the HDInsight or Hadoop connector when you’re getting data. However, note HDFS in brackets beside the Azure HDInsight and Hadoop File options as this means that you’ll be connecting to the underlying data store, which can be Azure Data Lake Store or Azure Blob Storage – both of which use HDFS architectures.
But this doesn’t help when you want to access a Hive table. In order to access a Hive table you will first of all need to install the Hive ODBC driver from Microsoft. Once you’ve downloaded and installed the driver you’ll be able to make your connection to Hive using the ODBC connector in PowerBI.
Read the whole thing. Connecting to Hive is pretty easy.
Comments closedOnce 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 closed