A Fine Slice Of SQL Server
If you’re using the new Parquet connector in Power BI there’s a chance you will run into the following error:
Parameter.Error: Parquet.Document cannot be used with streamed binary values.
Details:
[Binary]This isn’t a bug or anything that can be fixed, so it’s important to understand why it occurs and what you can do about it.
Click through for the explanation.
Comments closedGilbert Quevauvilliers shows us an example of Object Level Security in Power BI:
My example which I am going to detail below will show you how I will restrict a user from viewing sales data. The same user will be able to see Quantity amounts. This becomes really powerful because not all users need to see all the data.
My goal here is to show you how to the basics on how to use Object Level Security. Yes, there are more advanced options to configure a combination of Row Level Security and Object Level Security.
By using Object Level Security, it means that I can now have a single model which can be used for Financial and Non-Financial reporting.
Read on for an example.
Comments closedStefania Leone, et al, announce general availability of the Power BI connector for Databricks:
We are excited to announce General Availability (GA) of the Microsoft Power BI connector for Databricks for Power BI Service and Power BI Desktop 2.85.681.0. Following the public preview, we have already seen strong customer adoption, so we are pleased to extend these capabilities to our entire customer base. The native Power BI connector for Databricks in combination with the recently launched SQL Analytics service provides Databricks customers with a first-class experience for performing BI workloads directly on their Delta Lake. SQL Analytics allows customers to operate a multi-cloud lakehouse architecture that provides data warehousing performance at data lake economics for up to 4x better price/performance than traditional cloud data warehouses.
This is easier to work with than the Apache Spark connector and it looks like it should be faster than that connector as well.
Comments closedGilbert Quevauvilliers knows that time is a flat circle:
I have seen in the past Week-on-Week comparisons but one of the challenges is what happens when it overlaps years. Especially at the start of a year like it is now Feb 2021, the users want to compare week-on-week for the past 3 months.
My challenge was to find a way to have a continuous week number over multiple years.
Click through to learn how.
Comments closedDavid Eldersveld gives us ways to go beyond simplistic tables in Power BI:
Conditional formatting for a table or matrix is an easy way to keep the grid and display raw numbers while providing visual cues that our brains process faster than reading numbers. Background color, font color, icons, and data bars help jazz up bland tables. It’s an easy compromise that brings some data visualization to table detail.
Click through for more examples of this.
Comments closedBrent Powell has a new series for us:
Between the Power BI PowerShell modules and the Power BI REST APIs administrators have a rich set of tools to efficiently administer Power BI environments. Custom administrative and monitoring solutions based on these technologies have been featured on this blog before but today we will start the first of a two-part series highlighting simple script examples that Power BI administrators can use to address common scenarios.
The six PowerShell script files (.ps1) for today’s examples are available in my GitHub repo.
Check out those scripts, as well as Brent’s walkthrough of each.
Comments closedPatrick LeBlanc explains why star schemas are so important for Power BI:
Are you using a flat table in your Power BI report? You may be missing out! Patrick explores what a STAR SCHEMA is and why you should be using it.
Click through for a video, as well as some guidance. That Kimball fellow sure was a sharp one, he was.
Comments closedStephanie Bruno solves an interesting problem:
In my case, colleagues are required to develop reports every quarter with the most updated data we have and with a lot of commentary included. For example, they create the same 60-page document every quarter with the same tables and charts, and then modify the narrative depending on the data. For this reason, paginated reports may not be the right solution because the narrative varies so much. They also have a very particular format for the charts and tables that is hard to reproduce in Power BI. Finally, the data may be changing up to the day before the report is due. Their process in the past was to export the data they needed from Power BI, open it in Excel, copy and paste to another file where they had all of their charts built, export/copy/paste from more visuals, tweak the charts, then copy and paste the charts into their Word document. Then work late into the night doing this a few more times as the data is updated.
To help free them from this tedium, we worked out a new process to get their Word and PowerPoint files automatically updated in the format they required, using our good friend, “Analyze in Excel.”
Click through to learn how.
Comments closedReza Rad shows an interesting use case of Power Query Dataflows:
No matter what caused the data source to be slow (the old technology, performance issues, slow connector, limitations, etc), it will cause the data refresh of the Power BI dataset to become slow. Even if you have an incremental refresh setup, it might not still help much, because sometimes the query folding doesn’t happen. Slow refresh time will not only be bad for the service, but it will be also bad for the developer who has to wait a long time for the data to be available after each refresh.
Read on to see how you can use Dataflows to speed up refresh times (though not speeding up the slow data source itself). Reza also has a video on the topic.
Comments closedMarc Lelijveld doesn’t want to wait for everything to reload:
If you want to refresh a Power BI dataset, we all know where to find the refresh button in Power BI Desktop as well as in the Power BI Service. By clicking it, you will trigger the entire dataset to refresh. But sometimes it is more convenient to trigger a single table to refresh. If you want to do this, you can do a simple right-click on a table in Power BI Desktop, but how does this work in the Power BI Service? In this blogpost I will describe how you can trigger a single table refresh in the Power BI Service over XMLA endpoints. Please know, this does require Power BI Premium (either Premium per User or Premium Capacity is fine).
Click through to see how.
Comments closed