A Fine Slice Of SQL Server
Patrick LeBlanc has a new Guy in a Cube video:
In this video, Patrick looks at the new Power BI Secure Embed. If you have been using Publish to Web internally, you need to look at this feature as it is the perfect replacement that adds a secure option for easy, frictionless, embedding.
The video weighs in at about 7 minutes; check it out.
Comments closedMarc Lelijveld shows us how to break up a single Power BI desktop file into several:
Normally a Power BI Desktop file (PBIX) contains your queries, data model, and reports (visualization). Looking at a multi-file strategy, we split this up into two (or more) files.
The first file only contains the queries and data model. The second file is directly connected to the first one (direct query) and reads the data model. The file it selves includes all report content like visualizations, booksmarks and everything related to that. By working this way, you will be able to build multiple reports based on the same dataset.
Click through for a demonstration.
Comments closedReza Rad gives us the value proposition behind Power BI Dataflow:
If you don’t have an account in Azure or you don’t have a subscription that you can use for Azure Data Lake, No need to worry! You can still use Dataflow. The whole process of storing data into Azure Data Lake is internally managed through Dataflow. You won’t even need to login to the Azure portal or anywhere else. From your point of view, in the Power BI website, you create a dataflow, and that dataflow manages the whole storage configuration. You won’t need to have any other accounts or pay anything extra or more than what you are paying for Power BI subscription.
Click through for use cases and some tips.
Comments closedKara Annanie shows how you can R integration in Power BI to push Plotly visuals to users:
In the example, above, we’ve created a line chart visualization using Plotly and we’ve decided to put labels on the graph, but only on the first and last points of the line graph. This graph would be particularly useful to show 13 months of data overtime, where the left-most label shows January of last year, for example, and the right-most label shows January of this year, for example. The user could still view the trend across the year between both January data points.
Click through for a pair of videos and some notes on how to get started.
Comments closedChris Webb notices a new column property in Power BI Desktop:
I was, naturally, curious about what it did and I couldn’t find any documentation so I did a bit of investigation of my own and asked a few people at Microsoft.
It turns out that it is primarily intended for validation purposes, so that if you know a column should never contain a null value and then, at a later date, a null value does appear in that column then you’ll get the following error when you try to refresh a table in Import mode:Column ‘MyColumn’ in Table ‘TestTable’ contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table
Read on for more information. It looks like it works just as you’d expect.
Comments closedMarco Russo takes us through some of the more common Power BI error messages around writing DAX:
The message should help the author fix the code, but sometimes the text suggests a possible action without describing the underlying issue. The goal of this article is to explain the more common DAX error messages by providing a more detailed explanation and by including links to additional material. If some terms are not clear, look at the linked articles or consider some free self-paced training such as Introducing DAX.
Click through for several examples.
Comments closedSo how does this relate to error tables? Like most well-documented APIs, the U.S. Census Bureau API has a page devoted to listing and describing all the possible response codes that can be returned by their service. I take this information and build an internal table within the query that defines and describes these response codes in my own words. I’m now able to throw custom messages that make the difference between a
400response code and a404response code more obvious.For example, in the code below, I use the
Error.Recordfunction to create individual records that allow me to catch these unsuccessful requests and throw my own custom error messages to the user. I then create an extra field in each record called ‘Status’, which maps each HTTP response code returned by the API to a corresponding error message of my choosing:
There’s a bit of work, but the end result is a fairly simple explanation for end users.
Comments closedMarc Lelijveld takes us through the different ways we can embed Power BI dashboards:
In the end of 2018 Microsoft announced a great new feature which allows you to secure embed Power BI content to client applications or sites and keep security still in place. Actually, it almost works the same as the publish to web feature, but then users have to log-in in the embedded frame before they see the content.
Not only to the security to access the report is in place now, also all other security features will still work. This means that Row Level Security can be still in place in case when you are using secure embedding. A lot of people online are really enthusiastic about the new features. Simply because it gives you the ability to embed your content on your website, web portal or wherever you want, without risking security issues.
Click through for discussion on the four techniques.
Comments closedKellyn Pot’vin-Gorman hits the Go Faster button:
The gist of this kit is that it is a database repository as part of the sp_BlitzFirst to collect monitoring alerting and performance metric data. Once you’ve set this up, then you can use a Power BI desktop dashboard as an interface for all that data.
Now this is an awesome way to introduce more DBAs to Power BI and it’s a great way to get more out of your metrics data. The challenge is, it’s a lot of data to be performing complete refreshes on and the natural life of a database like this is growth. The refresh on the static database I was sent by Tracy, once I connected my PBIX to the local db sources, took upwards of an hour to refresh. Keep in mind, I have 16G of memory, 32G of swap and have upped my data load options in Power BI quite high.
Kellyn walks through the things she did to improve performance as a starting point, so check it out and be aware that there’s even more that can be done.
Comments closedWhy Python?
I prefer it to R mostly because I don’t have to create the csv-file(names) in advance before I import data to it. This is particularly important for scenarios where I want to append data to an existing file. The key for this task is NOT to use the append-option that Python offers, because M-scripts will be executed multiple times and this would create a total mess in my file. Instead I create a new file with the context to append and use the Import-from-folder method instead to stitch all csvs back together. Therefore I have to dynamically create new filenames for each import. So when the M-Python-scripts are executed repetitively here, the newly created file will just be overwritten – which doesn’t do any harm.
Click through for the code as well as a few caveats.
Comments closed