Category Archives: Self-Service BI
Tips to limit the number of steps in #PowerQuery – #PowerBI
When returning to some of my first queries built in Power Query, I always get a bit sad.
My old queries contain some unnecessary extra steps that makes it harder to evaluate what is happening in the query and in most cases I haven’t even bothered to rename the steps which makes the queries very hard to read for me and for others.
So, in order to improve here are some tips that can make mine and your queries shorter and easier to read.
Always Rename your Steps
Always spend time to rename your steps and avoid spaces in the step name.
Makes it easier to read and is documentation.
Especially important because the step name does not necessarily describe what is happening.
In this example I used the Remove Other columns function, and the step name refers to that function
– but what is actually happening is I am selecting specific columns – notice the formula bar – Table.SelectColumns
So, rename the step.
Check if you can avoid a column rename step
Using the interface in the Query Editor makes it very easy to get the “Renamed Columns” step but it can sometimes be avoided – For instance expanding a record column – in this example the DimGeography Column
– in this example the DimGeography Column and then double clicking the headers returned will lead into 2 steps but notice the last argument in the formula bar.
That is a list of column names that will be given and by modifying that we can do the expansion and renaming in one step.
Avoid the empty filter steps ie to not filter by (Select All)
When testing my queries, I sometimes filter the result to check the data
And then to clear it I sometimes use the (Select All) to remove the filter
This however leaves a filter step in your query where the Table.SelectRows function just is “each true”.
If you use the clear filter instead
The Filter step will be deleted
Limit the number of Rename Columns
Doing column renaming during the query steps can lead to several column renaming steps.
Try to do only 1 renaming step.
Limit the number of steps with Remove columns
Try to consolidate the column selection or deletion of columns in one step.
And remember the optional parameter in the Table.SelectColumns and Table.RemoveColumns where you can specify what should happen if a field in the column list is missing.
Don’t reorder your columns
You can change the column order in the Query editor but there is no need to do it
The columns will appear in alphabetical order in your data model anyway.
If you have a large table and can’t find the column use the Go to Column to locate it
A good bad example
Yes, this is one of my queries
This is the query after a clean-up – it does exactly the same but in fewer step and with a proper explanation in the step name.
Do you have any tips ?
This was a few tips to limit the number of steps in your query.
Let me know if you have any in the comments.
Stay queryious.
Tips to limit the number of steps in #PowerQuery – #PowerBI
When returning to some of my first queries built in Power Query, I always get a bit sad.
My old queries contain some unnecessary extra steps that makes it harder to evaluate what is happening in the query and in most cases I haven’t even bothered to rename the steps which makes the queries very hard to read for me and for others.
So, in order to improve here are some tips that can make mine and your queries shorter and easier to read.
Always Rename your Steps
Always spend time to rename your steps and avoid spaces in the step name.
Makes it easier to read and is documentation.
Especially important because the step name does not necessarily describe what is happening.
In this example I used the Remove Other columns function, and the step name refers to that function
– but what is actually happening is I am selecting specific columns – notice the formula bar – Table.SelectColumns
So, rename the step.
Check if you can avoid a column rename step
Using the interface in the Query Editor makes it very easy to get the “Renamed Columns” step but it can sometimes be avoided – For instance expanding a record column – in this example the DimGeography Column
– in this example the DimGeography Column and then double clicking the headers returned will lead into 2 steps but notice the last argument in the formula bar.
That is a list of column names that will be given and by modifying that we can do the expansion and renaming in one step.
Avoid the empty filter steps ie to not filter by (Select All)
When testing my queries, I sometimes filter the result to check the data
And then to clear it I sometimes use the (Select All) to remove the filter
This however leaves a filter step in your query where the Table.SelectRows function just is “each true”.
If you use the clear filter instead
The Filter step will be deleted
Limit the number of Rename Columns
Doing column renaming during the query steps can lead to several column renaming steps.
Try to do only 1 renaming step.
Limit the number of steps with Remove columns
Try to consolidate the column selection or deletion of columns in one step.
And remember the optional parameter in the Table.SelectColumns and Table.RemoveColumns where you can specify what should happen if a field in the column list is missing.
Don’t reorder your columns
You can change the column order in the Query editor but there is no need to do it
The columns will appear in alphabetical order in your data model anyway.
If you have a large table and can’t find the column use the Go to Column to locate it
A good bad example
Yes, this is one of my queries
This is the query after a clean-up – it does exactly the same but in fewer step and with a proper explanation in the step name.
This was a few tips to limit the number of steps in your query.
Let me know if you have any in the comments.
Stay queryious.
Look mom, we have a Power Platform Podcast!
Data with the Human Element. Three episodes dropped today!
Raw Data by P3 – “Data With the Human Element”
***UPDATE*** The podcast is now also live on Apple Podcasts
We overhauled our website in April of this year, and daaaaamn do I love it. It’s sharp, it’s modern, it’s just a touch edgy – and that represents us more accurately to first-time visitors than our old site did. Our core mission is to disrupt and revolutionize a stodgy old industry, we’ve proven our approach works, and the redesign reflects that side of us quite well. It’s just good branding and marketing to lean into your differentiating characteristics. No complaints.
But the human element is ALWAYS the most powerful force in any situation – tech included – and we’re doing the community (and ourselves) a disservice if we neglect it. There is SO MUCH VALUE to be “harvested” on the human side of the equation, and some of our most popular posts over the years have always been in that vein.
So it was time to bring the personality back a bit, tucked away on a less-formal “page 2.” But the blogging medium seems so… 2010. So limiting. Less human than actually hearing people’s voices. It was time to rock the mic, and rock it we have.
But do I “got what it takes to rock the mic right?” You decide.
It may have been a mediocre scene in a terrible episode of a terrible season,
but Tyrion was onto something with the opener to that speech.
I won’t steal its thunder…
We have a page for the podcast and everything. And pages for each episode. Let’s let them do their thing.
Spoiler alert though? The guests are ringers, and LaRock is a heck of a fractionally-available co-host.
“But Rob can I get a Power BI word cloud?”
Yeah, absolutely, I’m glad you asked.
Actual Power BI content follows.
Episode 1 Curated Word Cloud
Episode 2 Curated Word Cloud
Episode 3 Curated Word Cloud
Created using the following hand-entered table:
Note the commas – they are important!
And using the free version of the Tag Cloud visual by xViz. This visual let me do sentence fragments as the individual “words,” whereas the other one I tried broke every sentence into individual words and scattered them everywhere.
I just had to set this to “commas only,” and then of course add commas in my text where I wanted them split:
Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate
This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.
I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.
With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link
It is possible !!!
And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here
In my workspace I have a simple report with 2 parameters – Year and Country
In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.
You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.
Next is to design the flow in Power Automate
In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.
Next step is to get the rows from the Excel file – So I use the List rows present in a table
This action will return the three rows and next is to use the Control “Apply to each” to loop through each row
For the output we choose the value from the “List Row…” step
Then we add the action “Export To File for Paginated Reports
This action will run a selected report from a specific workspace
We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.
Add the end of the Action window – we can specify the values for each of the parameters.
OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown
As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”
We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.
Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content
The flow is now ready for testing – Save you flow and hit the test button.
Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.
Hopefully you will see that all steps are successfully.
And we can check our mail box to see the report delivered
The recipients of the e-mail can be sent to any e-mail address.
Can we pay by the hour ?
In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.
You only pay for the time the capacity is on – and the list price per hour is $ 8.
Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.
Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate
This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.
I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.
With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link
It is possible !!!
And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here
How to set it up
In my workspace I have a simple report with 2 parameters – Year and Country
In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.
You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.
Next is to design the flow in Power Automate
In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.
Next step is to get the rows from the Excel file – So I use the List rows present in a table
This action will return the three rows and next is to use the Control “Apply to each” to loop through each row
For the output we choose the value from the “List Row…” step
Then we add the action “Export To File for Paginated Reports
This action will run a selected report from a specific workspace
We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.
Add the end of the Action window – we can specify the values for each of the parameters.
OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown
As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”
We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.
Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content
The flow is now ready for testing – Save you flow and hit the test button.
Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.
Hopefully you will see that all steps are successfully.
And we can check our mail box to see the report delivered
The recipients of the e-mail can be sent to any e-mail address.
Can we pay by the hour ?
In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.
You only pay for the time the capacity is on – and the list price per hour is $ 8.
Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.
How Do You Know Your Power BI Security Roles are Right?
You need insights from your data, but you don’t want the wrong people seeing data that’s confidential or outside their responsibilities. In the Power BI service, there’s a robust way of ensuring that the right people get the data that they need for their purposes: row-level security (RLS). It’s a set of filters that only gives people those rows that they need according to their role. These roles are creating in Power BI Desktop and mapped in Power BI Service to users, or better, security groups. I don’t want to talk about the technical aspects of setting up RLS. There’s plenty of resources online on how to set this up (I’ve learned a lot from Reza Rad, for example). Instead, I’d rather open up a discussion about auditing security in Power BI reports.
If you can ask, how do we know that we are improving, you should also be able to ask how do we know that the security roles are implemented correctly. Data culture is not just for the business, but for the reporting team as well. I haven’t seen much discussion of auditing security roles in Power BI circles, so I’m genuinely curious about how others tackle this issue. Does everyone simply work hard and hope for the best? Or do you restrict everything at the database level and use different apps for different groups instead? There may even be regulatory reasons which require you to restrict it at the database level. But even if you do restrict everything at the database level, you still need to validate that security as well.
Personally, I LOVE Power BI RLS (and folks on Twitter agree). It enables me to carve out the data for those who need it without the report creep which happens with maintaining dozens of siloed reports. To ensure that roles are correctly configured, however, I’ve had to do some significant work. Wrong row-level security can be not only embarrassing, but costly as well. It can expose an organization to legal liability or put the organization at a competitive disadvantage. Because security roles can be high stakes, they need a comprehensive suite of approaches to administer them. This is definitely an area for growth in the Power BI offering.
Here are some ways that I’ve reviewed Power BI security roles:
- an Excel workbook with each role, table, and DAX rule (Excel to the rescue!).
- Double checking in Power BI Desktop that each role is correct by comparing the rules for each role and table to the Excel workbook. Should we take screenshots of each rule?
- As report creator, testing as Role for each role in Power BI Desktop.
- As administrator testing as Role for each role in Power BI Service.
- Create measures specifically for RLS that are put onto a tab in the dataset containing the data (either a hidden tab or a tab on the original dataset). This makes Testing as Role quicker.
- Power BI reports that get data from Active Directory to show what users are in what groups, powered by unique naming conventions to show only Power BI security groups. This makes it easy for any team member to notice anomalies. Example: Red Team PBI role is mapped to Red Team PBI security group.
- Automatic testing with process automation. There are many possibilities with this tactic.
How would I like to verify role security in Power BI?
- View as role in Power BI Service for anyone in a role where there are no filters on any tables. This would enable others who aren’t report authors to check security, making the roles transparent to business users who need to verify the correct roles but aren’t report creators or Power BI administrators.
- Export security roles from Power BI Desktop. This would facilitate rapid review at the time of report creation and updating.
- Administrators extract the security roles from Power BI Service. This would enable auditing of the security roles in place.
The good news is that I’ve figured out how to do #2. I can now export security roles for Power BI Desktop. I have template file called RLS localhost.pbit on GitHub. It works like this:
- Open a report in Power BI Desktop
- Open up DAX Studio (the latest version with External Tools makes this a breeze).
- Run a query to get the catalog_name and look at the localhost number at the bottom of DAX Studio.
- Open up RLS localhost.pbit and put in the localhost number and catalog_name.
- You’ll need to authenticate your access with your Office login.
- Wait for report to refresh.
- Most likely, you’ll want to enable native queries and ignore privacy in the settings to prevent having to ok each native query and to avoid the formula firewall error.
Security role report looks like this:
The report lets me see if the roles make sense and are consistent. On the page shown, there’s one table for Role Name, with the tables that are directly filtered by that role. If tables filtered is less than the max for roles, then conditional formatting shows the gap. The second table lists each table in the model, how many roles reference it, if it has a direct restriction or not, and what tables indirectly filter it. Date table is highlighted to show that it’s not filtered directly or indirectly. Region and Salesperson tables are both filtered directly by three roles each. Sales is filtered indirectly by Region and Salsesperson tables. The cards at the top of the page show number of roles, total tables, and how those tables are filtered.
The report documents security roles for 1-to-many relationships in Power BI models. Many-to-many relationships not supported. Bi-directional filtering and bi-directional security not supported. Inactive relationships not supported. I can finally export all of my security roles from this report, including a local timestamp of when they were extracted. The report uses a method described by Chris Webb: Analysing Power BI DMV Queries In Power BI Desktop. The report uses a live connection and can only be refreshed in Power BI Desktop.
What about you?
How do you review your security roles? How would you like to review them? Are there things you do that I haven’t mentioned? How can we help each other to be more transparent, more accountable, and more evidence based in our reporting practices?
Connect your #PowerBI desktop model to #Tableau Desktop via External Tools in PowerBI
I recently created an external tool to PowerBI desktop that connects your Power BI desktop model to Excel (https://eriksvensen.wordpress.com/2020/07/27/powerbi-external-tool-to-connect-excel-to-the-current-pbix-file/) and then I thought – could we also have a need for an external tool that could open the desktop model in Tableau desktop.
So, I downloaded a trial version of the Tableau Desktop to see what is possible.
And sure, enough Tableau can connect to Microsoft Analysis Services and therefor also the localhost port that Power BI Desktop uses.
We can also save a data source as a local data source file in Tableau
Which gives us a file with a tds extension (Tableau Data Source)
When opening the file in Notepad we can see the connection string and some extra data about metadata-records.
It turns out that the tds file does not need all the meta data record information – so I cleaned the tds file to contain
Opening this file from the explorer will open a new Tableau Desktop file with the connection to the specified model/database/server.
The external tool
Knowing this I could create an external tool the same way as my Excel connector.
First create a PowerShell
OBS – in order to run a powershell script on your pc you need to have to set the execution policy – https://go.microsoft.com/fwlink/?linkid=135170
The PowerShell script
Function ET-TableauDesktopODCConnection
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $ false)]
[string]
$ port,
[Parameter(Mandatory = $ false)]
[string]
$ database,
[Parameter(Mandatory = $ false)]
[string]
$ path
)
$ tdsXml = "<?xml version='1.0' encoding='utf-8' ?>
<datasource formatted-name='LocalPowerBIDesktopFile' inline='true' source-platform='win' version='18.1' xmlns:user='http://www.tableausoftware.com/xml/user'>
<document-format-change-manifest>
<_.fcp.SchemaViewerObjectModel.true...SchemaViewerObjectModel />
</document-format-change-manifest>
<connection authentication='sspi' class='msolap' convert-to-extract-prompted='no' dbname='$ database' filename='' server='$ port' tablename='Model'>
</connection>
</datasource>"
#the location of the odc file to be opened
$ tdsFile = "$ path\tableauconnector.tds"
$ tdsXml | Out-File $ tdsFile -Force
Invoke-Item $ tdsFile
}
ET-TableauDesktopODCConnection -port $ args[0] -database $ args[1] -path "C:\temp"
The script simply creates a tableauconnectort.tds file and stores it in C:\temp – and the xml content in the file is dynamically referenced as arg(0) and arg(1) when the external tool is called from Power BI Desktop.
Save the script in C:\temp and call it ConnectToTableau.ps1.
The OpenInTableau.pbitool.json file
Next step was to create a pbitool.json file and store it in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools
{
"version": "1.0",
"name": "Open In Tableau",
"description": "Open connection to desktop model in Tableau ",
"path": "C:/Windows/System32/WindowsPowerShell/v1.0/powershell.exe",
"arguments": "C:/temp/ConnectToTableau.ps1 \"%server%\" \"%database%\"",
"iconData": "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJAAAACQCAYAAADnRuK4AAAABmJLR0QA/wD/AP+gvaeTAAADRklEQVR4nO3dv27TUBiH4WPEitSRS+iCurO0GzdRiS5sXRhAXZhYEAxd2LoUiZtgaxb2iqWXwFiJCzgsqPRPrMb5Jc1x/TxbqgSi5O2xE3+uSwGAUeo2/QRac3R8cla6bvfqB7XOPr19s7e5Z9S2J5t+AoybgIgIiIiAiAiIiICICIiIgIgIiIiAiEziUMbR8cnZovetXbfTlbJ1dbuUy67W80UfP7XDHk83/QQexPVjW/fd9e7trSGPnxqbMCICItLEJqyeljrv593BivbRap0tfNdwH2hVDj58mfuanH5819R+axMBrduQHdvb80BdredT2zEewiaMiICICIiIgIgIiIiAiAiIiICICIiIgIhM4lDGEA5bDGMFIiIgIgIiIiAiAiISTbf1TRK2ZmWTjQvomyRszaomG61ARAREREBEBEREQESaOMdo7eeFjdBYzguzAhEREBHjHP/8fv/i3i8An3/+1dTmowVWICICIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiSx8Lc3Xjcdk/nJ2VWv+/X103+/51dy/9d61ARAREpIlxjilPHvZpbfKwjxWIiICICIiIgIgIiEgTn8KGWmQAfiz/79gH9a1ARG7UP5arG29qBVqHZAXaP5ydDbj7Tqn16v0qXXdZSln4/eo77HFzE+bqxuNy/djW8MdulVLi98smjIiAiNzchI3w6saT1nULv18l3AfqfQrLPnCT80B2ooczD0STRvlF4jp+a/11juVYgYgIiIiAiAiIiICINPEp7Of29txPQC8vLib7qefZq29zX5M/P1439ZpYgYgIiMjSmzCnMY/LKg5bzGMFIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiAiLSxDhHCwzML8cKRERARKJlu2+SsDUPOdnYN0nYmlVNNlqBiAiIiICICIiIgIg08eWZ88Lucl4YkyAgIgIiIiAiAiJinOOWdf0108fKCkREQEQERERARAREREBEBEREQEQERERARCZxKGPw1Y1v3R7y+Kkd9mgioLVPHjZwdeOhWps87GMTRkRARJrYhK1dA1c3fqxGsZ19SOaBhrEJIyIgIgIiIiAiAiIiICICIiIgIgIiIiAAAAAYjb8VJdQbiRXyOAAAAABJRU5ErkJggg=="
}
Test it
Now restart your Power BI desktop and the external tool should be visible in the ribbon
Then open a pbix file with a model and hit the button.
A PowerShell screen will shortly be visible and then Tableau opens the tds file and now we have a new tableau book with a connection to active power bi desktop datamodel.
And we can start to do visualizations that are not yet supported in Power BI –
How can you try it
You can download the files needed from my github repository – link
Feedback
Let me know what you think and if possible share some of the viz that you make.
#PowerBI – External tool to connect Excel to the current PBIX file
In the July update of the Power BI Desktop we now can add external tools to the ribbon.
If you install the latest versions of Tabular Editor, DAX Studio and the ALM Toolkit these will be added as tools in the ribbon.
But you can also build and add your own tools.
David Eldersveld (link) has written an excellent series of blogposts about using Python as an external tool – link to part one – and this inspired me to give it a go as well.
The official documentation can be found here.
Short description of what an external tool really is
An external tool will point to an exe file and you can supply the call to the exe file with arguments including a reference to the %server% and %database%.
The information about the external tool needs to be stored in
C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools
And name the file “<tool name>.pbitool.json”.
This will give me these buttons in my Power BI Desktop
My idea to an external tool
When I build models – I use Excel pivot tables to test and validate my measures and typically I would use DAX Studio to find the localhost port to setup a connection to the currently open PBIX file.
So, I thought it be nice just to click a button in PowerBI Desktop to open a new Excel workbook with a connection to the current model. That would save me a couple of clicks.
If I could create an ODC file when clicking on the button in Power BI and then open the ODC file (Excel is the default application to open these) my idea would work.
I have previously used Rui Romano’s (link) excellent PowerBI powershell tools – link to github and link his blogpost about analyse in Excel – so why not use PowerShell to do this.
Here is a guide to build your own version
Step 1 Create a powershell script
I created a powershell file called ConnectToExcel.ps1 and saved the file in local folder C:\Temp – you can save this where you want it stored. (Link to sample files last in this post)
The script is a modified version of Rui’s function Export-PBIDesktopODCConnection – thank you so much these.
Function
ET-PBIDesktopODCConnection
{
# modified the https://github.com/DevScope/powerbi-powershell-modules/blob/master/Modules/PowerBIPS.Tools/PowerBIPS.Tools.psm1
# the Function Export-PBIDesktopODCConnection
[CmdletBinding()]
param
(
[Parameter(Mandatory =
$ false)]
[string]
$ port,
[Parameter(Mandatory =
$ false)]
[string]
$ path
)
$ port = $ port
$ odcXml= “<html xmlns:o=””urn:schemas-microsoft-com:office:office””xmlns=””http://www.w3.org/TR/REC-html40″”><head><meta http-equiv=Content-Type content=””text/x-ms-odc; charset=utf-8″”><meta name=ProgId content=ODC.Cube><meta name=SourceType content=OLEDB><meta name=Catalog content=164af183-2454-4f45-964a-c200f51bcd59><meta name=Table content=Model><title>PBIDesktop Model</title><xml id=docprops><o:DocumentProperties xmlns:o=””urn:schemas-microsoft-com:office:office”” xmlns=””http://www.w3.org/TR/REC-html40″”> <o:Name>PBIDesktop Model</o:Name> </o:DocumentProperties></xml><xml id=msodc><odc:OfficeDataConnection xmlns:odc=””urn:schemas-microsoft-com:office:odc”” xmlns=””http://www.w3.org/TR/REC-html40″”> <odc:Connection odc:Type=””OLEDB””>
<odc:ConnectionString>Provider=MSOLAP;Integrated Security=ClaimsToken;Data Source=$ port;MDX Compatibility= 1; MDX Missing Member Mode= Error; Safety Options= 2; Update Isolation Level= 2; Locale Identifier= 1033</odc:ConnectionString>
<odc:CommandType>Cube</odc:CommandType> <odc:CommandText>Model</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection></xml></head></html>”
#the location of the odc file to be opened
$ odcFile = “$ path\excelconnector.odc”
$ odcXml|Out-File $ odcFile -Force
# Create an Object Excel.Application using Com interface
$ objExcel=New-Object -ComObject Excel.Application
# Make Excel visible
$ objExcel.Visible = $ true
# Open the Excel file and save it in $ WorkBook
$ WorkBook = $ objExcel.Workbooks.Open($ odcFile)
}
write $ args[0]
ET-PBIDesktopODCConnection -port $ args[0] -path “C:\Temp”
The script contains a function that creates an ODC file where the Datasource and path of the ODC file is determined by to arguments in the function – port and path, The Script also opens Excel and then opens the file.
The scripts contain a
$ args[0]
This will in the end be the value localhost:xxxxx that will be provided when we click the External tool button in Power BI Desktop – and will make more sense after step 2
Notice that I have hardcoded the path where the ODC file will be stored to C:\Temp.
Step 2 Create a .pbitool.json file
The pbitool.json file is relatively simply
Name is the text that will appear in the ribbon.
Description is the tooltip that appears in Power BI Desktop according to the documentation – but it doesn’t work at the moment.
Path is the reference to the exe file you want to activate – and only the exe file.
Arguments is the arguments that you want to pass the exe file – and here we have the to built in references %server% and %database%. Arguments are optional so we could just start Excel or any other program if we wanted .
IconData is the icon that you want to appear in the ribbon – I found an icon via google and then used https://www.base64-image.de/ to convert it to the string.
In this tool we use the Powershell.exe file that can be called with arguments where we specify the script file that we want to be executed and we pass the extra arguments server and database as well – in my script I only use the %server% reference which will give me the server name and portnumber of the local instance.
It means that when the button is clicked in PowerBI Desktop it will execute
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe C:\temp\connetToExcel.ps1 localhost:xxxxx databasename
The localhost:xxxxxx can is the first argument provided and the value can then be referred to by using $ args[0].
The file must then be stored in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools and in my case I called it OpenInExcel.pbitool.json.
Depending on your privileges on your computer you might be warned that you need administrative rights to save files in that location.
And if you save the script file elsewhere you need to modify the pbitool.json file.
Step 3 – Test it
Now we are ready to restart Power BI Desktop – and
And it does appear
Next – open a pbix file
This will open a Windows PowerShell window and write the server information
And in the background opens Excel and the ODC file – which results in a pivotable connected to the local instance.
With a connection to the localhost:52510
The files
You can download the files needed from here – https://github.com/donsvensen/erikspbiexcelconnector
Feedback
I think the use of PowerShell opens a lot of interesting scenarios for external tools and I look forward to see what other external tools that appear in the community.
Please let me know what you think and if you find it useful.
My session at Power BI Global Virtual Conference – #PowerQuery
Last month I had the pleasure of presenting a session at the free Power BI Global Virtual Conference.
The conference was arranged by Avi Singh – PowerBIPro – link to his YouTube Channel here
My session was about Power Query and how to make your Power Queries more dynamic.
You can download my presentation and demo files via this link – here.
Power BI and Paginated Reports: Better Together
Wise Professionals See the Big Picture
Bringing it Home
Today we’ll be concluding our 3-part series on Analysis versus Reporting, Power BI versus SSRS and Paginated Reports, and looking closely at our habit of using the word “versus” so liberally:
-
Part One summarized the history of the industry, its long-stagnant roots in Reporting, and the recent “triumph” of Analysis.
-
Part Two examined the relative strengths of Power BI reports and Paginated (SSRS) Reports, and why you need both.
-
Today, we’ll explore how you can use both Power BI and Paginated Reports together, as part of a wholistic system.
What Does “Triumph” Mean?
I’d like to start by clarifying what I mean when I say Analysis has “won.” Even today, traditional reporting is STILL the majority of the “biomass” in the BI world. For example, right now around the world, there are probably three people using a Traditional report for every one person using an Analytical one.
So Analysis isn’t dominating usage today, but it IS dominating mindshare. No one is BUYING platforms which are 100% traditional reporting – not anymore. And in fact, lots of people ARE buying pure Analysis platforms like Tableau. Of course, what they SHOULD be doing is buying platforms capable of BOTH, which is kinda the focus of today’s article.
Analysis Will Keep Stealing Usage Share for Awhile
So we have a ways to go before Analysis equals or eclipses Reporting in usage, but the wind has most definitely shifted. Keep in mind that a LARGE percentage of “usage” of Reporting is not “looking at it with our eyeballs” usage, but rather EXPORT usage, which powers ANALYTICAL work in Excel. Below the radar of IT and invisible, and crude compared to the Analysis you can perform in Power BI, but Analysis nonetheless.
Until the world stops using Reporting as a crude form of database access (to power its Excel analysis), the fight is not over. And we have a LONG ways to go to get there. Even today, a client told me a story about a team member who last week printed out a 30-page report and was planning to go line by line through it, re-keying a subset of the data into Excel (manually!) to get a total of how many units of Product X they’d sold in the prior month. Said individual was thrilled to be shown the Power BI report where a single click provided the answer. So we still have some work to do, awareness-wise.
Where Will We Land?
At most, I expect the inverse of today: three Analysis reports for every Traditional report. If we slowly convert the “Traditional reports which pretend to be informative but are really just export sources” into Analytical (Power BI) reports, that should get us there – and really, that’s ALL that we SHOULD do.
And if you read Part Two, you know there are certain kinds of reports that Power BI is “bad” at. We still need SOMETHING for those, and I expect they will still constitute, say, 25% of all reporting usage once we reach equilibrium.
Switching from “Or” to “And”
Part Two covered when to use X versus Y, but even a firm grasp of that leaves something important on the table: what are the ways you can use BOTH in an integrated manner, thus getting a result which is more than the sum of its parts? Ah yes, the “Better Together” cliché! But there is substance here.
Microsoft should be leaning HARD into this aspect of their platform – that it has STRONG products in both worlds, because so few of their competitors have anything to offer in the Traditional space, even as said competitors are already behind in the Analysis space. The combination of the two – when well-integrated, brings at least three very interesting classes of benefits:
-
Cross-report drillthrough in either direction.
-
Both kinds of reports in a single portal.
-
Shared analytical model powering both flavors of reports.
Let’s take these one at a time.
Bi-Directional Drillthrough
This is the most fun, so I’ll cover it first. Consider the following wireframe of a Power BI report:
We’re Going Wireframe Today – No Time for Distracting Myself with Hyper-realistic Demo Building 
I hope it’s easy to imagine the interactive features of the above report. Click on a bar in one of the charts, for instance, and watch the rest of the report filter down to that Category or Customer, etc.
Imagine seeing Cust2 and thinking “hey, why is Customer 2 so low this month? Aren’t they normally buying a lot more from us? Let’s see what they actually bought – show me the invoices!” Well, here ya go…
Select Customer 2’s Bar, Click the Icon/Link
And then you get navigated to a Paginated Report to view the actual Invoices which were sent to Customer 2, because the filter selection you made in the Power BI report is “transmitted” as part of the URL:
Paginated Report Displays the Authentic Invoices
(The system “remembers” which customer you selected in Power BI – by passing it in the URL)
“Wait, Couldn’t I Build That Invoice Viewer in Power BI?”
Yeah, smart aleck, you COULD build precisely THAT one pictured above. But a couple things. One, a more realistic invoice might span multiple pages, with page breaks and intermediate subtotals, and Power BI isn’t good at that paginated stuff. Two, if you want to be absolutely sure you are looking at PRECISELY THE INVOICE which was sent, you would want to go to the source which generates and prints said invoices yes? And since said invoices often need to be pixel perfect, the original source again cannot be Power BI.
“Wait, How Do You Pass ‘Cust2’ in the URL?”
This isn’t a technical how-to article, and I’m trying to stay above the weeds, but here’s a quick series of screenshots which should help:
Format Shape –> Action –> Web URL –> fx –> Choose a Measure
Example of a Measure Which Constructs a URL to a Paginated Report
(Comes from a slightly more complex example than our wireframe demo)
And Now the Reverse Direction!
OK, so you’re looking at the invoice, and you spot the change. “They only bought ONE Flux Capacitor this month! Don’t they normally buy more??”
Aha! Well NOW you’re back to an Analysis kind of task! You COULD thumb backwards through invoices using the “Prev Invoice” link, but if the invoices are multi-page, that’s gonna be tedious, AND it’s hard to spot trends that way. Power BI is REALLY good at this kind of thing.
If only there were a way to seamlessly navigate to a Power BI report, pre-filtered to answering my question…
Ooh! It’s a Link!
Navigated back from Paginated to Power BI, With Product Category=”Flux Capacitor” and Customer=”Cust2”
(A different Power BI report than the one we started on, but of course depending on actual needs, it COULD have been the original)
“OK, Spill It – How’d You Do the URL THIS Time?”
Shockingly similar on the Paginated side (in Report Builder) to the Power BI side, actually…
In Report Builder, Select Text Box Properties
“Go to URL”
Build Yourself an Expression
(Again though I’m taking a pass on the weedy details today – trying to keep it as concise as we can around here)
Pretty Neat Huh?
Let’s see that whole flow one more time, in a more concise manner:
Glorious: You Bounce Back and Forth Across “the Line of Versus” with Agility
***BONUS THOUGHT: As cool as that is, I wonder if even TIGHTER integration makes sense in some cases. The custom visual framework in Power BI, in theory, could support an embedded Paginated Report control right? Would there be use cases for that? Or would the clarity of being in a completely separate report always be preferable? I’d bet that it would at least be interesting in SOME cases, but I need to think about it more. Leave a comment if you have an opinion, please!
Benefit Two: Single Portal
Not as sexy as cross-technology drillthrough, but a real benefit for everyone’s sanity as well as usage: both kinds of reports can be published to a single Workspace:
OMG, BOTH TYPES of Reports in the Same Workspace? THAT’S PETER VENKMAN’S MUSIC!
Back off, man. I’m a data scientist.
Don’t sleep on the little details like this. They make a big difference.
Benefit Three: Powered by the Same Analytical Model
SSAS Tabular (Power BI’s analytical engine) brings MANY advancements relative to its predecessor (SSAS MD), and one of them is the ability to return hyper-detailed rowsets without the performance penalty we suffered in the old days. If you want the data to populate an Invoice, you can fetch what you need with a DAX query and not sit there waiting forever for it to return. In fact, things like the Table visual already do this within Power BI itself.
So if you’ve invested in a robust Power BI analytical model to power your analysis, you might find yourself reaping unexpected Paginated Reporting benefits. If you don’t NEED to go build new, traditional SQL-powered plumbing to power a more traditional report, great! By all means, connect it to your Power BI model (aka Dataset), and off you go!
Your Data Model Can Power BOTH Kinds of Reports
And folks, that wraps up our series! What did you think? Did I leave anything out? Wait, what’s that you say? There IS something I missed? Lemme see…
“Hey Rob, How About a Good Microsoft Rant?”
How about more than one? I mean, I’m so glad you asked, cuz I’ve got two…
Integration Shouldn’t Cost So Much
First, why is it so expensive to get an integrated environment? Best I can tell, you can’t get a Power BI + Paginated integrated system for anything close to the price of getting them separately. 100 users of Power BI Pro would cost $ 1k a month, and an SSRS On-Prem server is a one-time payment of, say, $ 6500. So to run those two – separately – for two years, it would cost you about $ 30k all-in.
But to get them integrated, today you’d have to plunk down $ 5k a month for Premium, and that pushes your 2-year price to $ 120k – 4x having the same capabilities separately. Now, I’m pretty sure I can get benefits 1 and 3 (drillthrough and powering both from a shared data model) at the $ 30k two-year price, so what the extra $ 90k gets me is… benefit two? Side-by-side publishing of both report types in a single Workspace? Steep price jump for just that.
On the on-prem front, you can go Power BI Report Server, but that’s also a premium price point. Enterprise Edition plus Software Assurance is required, apparently, and that’s 100% an Enterprise SKU – well out of the reach of SMB’s, and just as importantly… out of Departmental reach within the Enterprise itself.
In fairness to Microsoft, the pricing for Enterprise customers DOES need to drive their thinking in large part. And that comes with benefits! Most of Microsoft’s data stack is actually quite underpriced for SMB’s and Departments, and we never complain about THAT, do we? At P3 for instance we can run all of our MS data stuff for a FRACTION of what we pay for Salesforce (which we deeply regret getting hooked into, but that’s a story for another day isn’t it)? There’s a lot more value in the MS platform than Salesforce, and yet the prices are reversed.
So this may be a sensible pricing strategy for the Enterprise, but this is one of those cases where it prohibits SMB’s (and again, the Departmental level of the Enterprise, which is crucial for “land and expand”) from playing the game properly.
It’s a massive strength for the MS platform versus the competition, and it seems a shame that Departmental/SMB is accidentally priced out of the most elegant version today.
OMG Naming!
Authentic Action Photo of Microsoft Naming Their Software
Oh, “Paginated Reports,” how I LOATHE thee as a name! This name caused me SO MUCH TROUBLE for a very long time. “How does this relate to SSRS?” is the question which kept plaguing me. Is it a new thing? If someone knows SSRS really well, will this be a foreign experience – a brand new technology – or super familiar? Is it not capable of pixel perfect document generation, and instead ONLY leans into the truly Paginated benefits of SSRS? Ugh.
Here’s what it REALLY is: it’s SSRS technology living within the Power BI environment. You still get Report Builder, and the whole thing still uses RDL. If you knew SSRS, Paginated Reports are for you. Which is good news – SSRS was, and is, a market leader in its space. Super robust and capable. Refined by TWO DECADES of development and experience.
Now, again, I kinda DO get why they’re doing this. Power BI is “the brand” now, and everything needs to line up under that banner. Years from now, this naming scheme will be a good one. “Power BI just has two kinds of reports, great!” But I think they underestimate the utter carnage this wreaks in the meantime, and how many times we’re going to have to explain to people that “it’s just SSRS in Power BI.”
Remember, I’m the guy who tried to rename PivotTables when I worked on Excel at Microsoft. So I’m definitely cut from the same cloth originally – I’m just trying to heal, and to atone for my past sins. But “yeah,” said Rob circa 2004, “PivotTable is such a wacky and scary name which has nothing to do with its primary function, which is to summarize/aggregate data.” I wanted to rename it SummaryTable. Just like I explained above, if you fast forward ten years, that’s a better name. Zero question. But is it worth the transition cost? In the end, wiser heads talked me out of it.
While we’re at it… can we rename Power Pivot and Power Query to be… Power BI in Excel? Because just like we’re explaining Paginated Reports = SSRS in Power BI, we have to explain the Excel relationship over and over and over…
Nah, too sensible.