A Fine Slice Of SQL Server
Gilbert Quevauvilliers shows how to remove all characters either before or after a delimiter:
This is an example of what my column name looked like below.
Date[Yr-Mth]What I wanted this to look like is Yr-Mth]
Below is the Syntax I used to get this working.
Click through to see how Gilbert was able to do this and subsequently how to get rid of the trailing bracket.
Comments closedYou’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:
The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.
Read on to learn what the issue is and one potential workaround.
Comments closedIf 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 closed