I wanted to have a look at a specific set up we have in regards to our Power BI Workspaces and apps
We have Power BI Premium P1 Node
We use dataflows
We use a Deployment Pipeline for Dev Test and Production Premium Workspaces
We have a scenario where we have a sub workspace with a smaller number of reports than the main reports
Lets have a look at the master Workspace that contains all our dataflows, datasets and reports live connected to the dataset
And for all three areas there is also an app to test usability for Viewers (and for the viewers of all the reports in Prod
Current issues with Deployment Pipelines and dataflows
The great thing about pipelines is that we don’t have to keep copies of Test and Prod reports. There are just Dev Pbix files. However, for Production. How do you know that your Production reports are using the dataflows connected to the Production SQL database?
The issue at the moment is that there is a bug that leaves the development data source in with the Production data source for the data flow. You can see this if you go into Lineage View
In the Prod Pipeline we have set the reports to use Prod dataflow and updated the user and password but from here how do you know that this has worked?
The best way I have found to do this is to export the dataflow Json … Export to Json
And look at the file in Notepad and check the source Source = Sql.Database(\”prd-
Its showing as production so I can be happy that the production reports are against the production database
The Sub Workspace
So we have a model for a production app to sit on where users can view all the reports.
However we have another cohort who can only see the reports B and not report A or C.
We can only have one app per workspace so we cant create another app with just one report.
The answer here is to create a sub workspace and republish the Production report across to this new workspace.
The question here is….. Can the sub workspace be a none premium workspace if we are live connected to a dataset in a Premium workspace?
Lets find out by first assessing the situation we want to set up
Process to move the production reports in the deployment pipeline to another workspace
We want to publish the production report to a new workspace (With just the one report in it)
And as a further complication, We want to live connect to a dataset in a Premium workspace and recreate a report in a Pro Workspace.
Not only that, but the Pbix file is set to dev not Prod and there is no production pbix file.
First of all in your Production Workspace go to your report (content) Click … and Save a copy
And Here is the Lineage in Power BI
So how do we check that this report is connected to Dev data flow not prod dataflow
At this point I would probably recommend having something in SQL to connect to like a view or a table containing the datasource name to test with. This would be really handy.
We have to assume that this is the case because it comes from Production workspace which is definitely connected to Prod its good. Also, You could check the data between dev and Prod at this point too.
To test, an app was published and the data was exactly the same as the report in the master workspace.
The good thing about this set up, you only have one copy of the data in master. You don’t have to have the same data duplicated in another workspace, taking up more space in the Power BI Premium Node.
And because its such a small workspace, we don’t have to create it in Premium so we don’t need our Power BI Admin to set it up as a Premium workspace
Now you have to think about Licensing. Everyone who needs access to the report in the Pro workspace now needs a Pro account themselves. Great if you have a P3 License which gives everyone free Pro accounts.
But what if you don’t have everyone on Power BI Pro licenses. Or you have external users and you haven’t given them a Pro license?
Lots to think about here with this Mix of Premium and Pro. this might then be a case of going to your Power BI Admin and asking them to switch the Workspace to Premium simply due to the Licenses against the report viewers.
This is the 6th year of Power BI. Power BI has always been about driving data culture and has been rapidly evolving throughout its data journey.
Power BI is top of the Gartner Magic Quadrant and growing exponentially across users and business. Lets have a look at where we have come from and where we are going.
The Power BI Journey
Power BI started by wanting to empower Business Analysts. The next step was to empower IT departments
At the beginning of the journey the two targeted user groups wanted different things.
Business Analysis want quick BI with no red tape. IT wants governance, control and repeatability. By 2018 Microsoft was starting to think about both of these user groups and how Power Bi could work with each use case.
These use cases split into Enterprise and Self Service BI.
Microsoft moved to bring both Enterprise and Self Service together. Power BI was proving so popular it seems the best development was to bring enterprise reporting and semantic models into power BI
The following licensing was created to allow for this economic disruption
Built on Azure and deployed in over 50 data centers. Power BI used the cloud to bring about economic disruption
The cloud also offers a great economic model (Economies of Scale). Originally we had free and Pro for our business analysts. Premium became available in 2017 as an enterprise solution.
Enterprise And Self Service Converged
By 2018 with power BI, Microsoft aimed to
Empower every individual
Empower every team
Empower every organisation
Moving on to 2019 and 2020
Power BI increases its market share considerably and with every month of development comes huge changes to the product. Its now becoming a game changer for both self service and enterprise use.
Information workers need more, and power BI gave us more with inbuilt AI capabilities.
Key Driver Analysis
Key influences Visual
A great visual to look at the influences on your data. I have used this, for example to look at what drives negative tweets for a company. It also segments your data into understandable groupings
This visual provides textual analysis of your whole report or specific visuals. It will update the narrative dependant upon what the user selects.
This is a great visual for users who need to see descriptions along with the visuals to get a better understanding of the narrative.
Root cause Analysis
This is fantastic for users who want to create their own journey through the data. Select a metric and then allow the user to choose how they want to slice and dice the information. AI will find you categories with the lowest and highest values against the metric
Q&A allows users to ask natural language questions, and Power BI will bring back the best visual that answers that question. developers can put together lists of possible questions for the user to choose from.
Powerpoint for Data
To make Power BI easy to use Microsoft decided to match experiences where ever possible to Powerpoint and Excel. This leads to massive adoption because people find it easy to use. they have already seen and used the ribbon in lots of other Microsoft products, take up is therefor a good option.
Moving to 2021
2021 brings to the table a new licence that gives smaller businesses the ability to buy in Premium licenses at a user level rather than pay for a Premium node.
This can work out much cheaper for smaller companies so you don’t have to miss out on Premium services.
AI Capabilities for Business Analysts
Again, our information workers require more from the service
Power BI has the most complete AI capabilities in any BI product. Along with the visuals mentioned above, the Business Application Summit introduced us to Sentiment Analysis and Key phrase extraction in more detail.
Azure has given us cognitive services for a while. For example, I used Azure to get tweets via Logic Apps and assign sentiment scores to each tweet.
With Premium, we can do this within Power BI. You will now be able to run data items in Power BI through sentiment analysis and assign scores. Are those reviews coming through from your customers positive, negative or neutral?
Key Phrase Extraction
Another fantastic cognitive service that I used to do pre moving the data into Power BI. Now you can get a list of key phrases from your data, and put them into a word cloud. All available within power BI
Automated Insights (Coming Soon)
Brand new Automated Insights are coming…
Automate insights will give you Anomalies, KPI Analysis and Trends out of the box.
Anomaly Detection will automatically find anomalies in time series data and also attempt to explain these anomalies. Along with this you can also see trends in your data and get explanations on these trends. Trends and anomality’s gives you a holistic overview of your data
And KPIS look at our Goals which is new in Premium. We will look at this new feature later.
Automated insights proactively prompt us for time critical information, but you can get insights without being prompted by clicking the Automated insights button.
It works without any set up or configuration (Apart from KPIs because this needs Goals to work with.
AI for Data Scientists
Extend with Azure ML
Create ML Models
Python and R Integration
Going beyond on the power BI Journey (What is coming)
Insight to Action through Power Platform
Power Apps has been available for some time to embed into a Power BI Report but there has been a lot of work done in this area to make it work better within the power BI suite.
I have used a Power Apps Visual to allow users to send data back to a database, which would then display within Power BI as it was a direct connection.
New features include Power Automate within Power BI. You can now add a button in Power BI that will trigger Power Automate. I cant wait to look at some use cases for this new service.
One question I have is regarding Logic Apps within Azure. Logic Apps is the same service as Power Automate and I want to know why the decision has been made to call the same product two different things. And if Logic Apps can stand in for Power Automate in this situation.
Power BI Goals is in Public Preview and available for Premium users
Goals is a new Power BI artefact that you can set up on your metrics that matter the most to you. You can assign data from reports to update your goals and users can drill through into your reports for further analysis.
Another fantastic new service, its a shame its only offered to Premium users. Thankfully Premium per user allows buy in to these new services for smaller companies.
My one concern is if you are using Premium Pipelines to host Development, Test and Production workspaces.
We are still waiting for dataflows to be added to this feature, its doubtful whether goals will be included when introduced.
This would mean that you cant move your goals from dev through to production so hopefully I am wrong about this.
Real Time Analytics
Power BI Streaming Dataflows
Streaming dataflows should be coming later in 2021 as preview.
Streaming dataflows is a brand new concept in power BI, and for the first time will offer the ability to combine multiple data sources into a streaming dataflow.
This is again another Premium feature
Front line workers
Insights on the Move
There have been significant improvements to the mobile apps which are getting great reviews from both Apple and Google stores.
Empowering Every Team with Teams
Great Teams use data and we can embed Power reports in teams channels so we can add Power BI reports to meetings and provide links within chat.
Microsoft have put a lot of work into bringing the Power BI Service into Teams for much better collaboration. They want Power BI to be as fundamental to what you do as your calendar, meetings or chat.
It’s recommended to make sure that you make sure the Power BI App is always available in your tools panel
One issue I have with Power BI in teams is having to navigate away when you are using chat. If I am looking at a document in Teams and I see that someone is chatting to me, it can be annoying to have to leave the document to see what else is happening. Hopefully they sort this functionality out.
Power BI In the real world with HoloLens
Straight out of science fiction. The HoloLens 2 allows you to augment Power BI Analytics over your real life journeys. This gives you Analytics where ever you go.
I would need to think about how and when you would need this but they gave a great example of walking through an office and having virtual tv points up on the walls.
New Announcements for Power BI analytics
In part two there was lots of exciting announcements of new features coming to Power BI
Drill down and drill through on the tooltip
Power BI has given us a few ways to do drill down and drill through.
For drill through, You could right click on a visual or even create a button to click to drill through on the highlighted item.
Drill down was available as an icon at the top of a visual
But very soon it should be even easier to establish that these options are available. Power BI Will soon have both Drill through and Drill down available on the tooltip.
Paginated report within your Power BI reports. Vertical Headers. etc
Build a Paginated report table in report builder with vertical headers, grouping, nesting etc and then add this visual to your Power BI report
New Standard Table visual in Power BI
These new options will be added to the table visual. This is the current experience
But soon we will be able to add sparklines within the table, both column and line sparklines.
This is one of the biggest requested additions so its great to see that we will soon have sparklines on the standard table visual.
Buttons Bookmarks and Navigations
I have been using Power BI for a few years now and the one thing I have always disliked is the workaround feel of creating button navigation. Having to layer buttons or visuals over the top of other eachother and hiding and unhiding where appropriate. It was time consuming and overly complex.
Microsoft are working on new navigator features to set up new custom navigation. This is one of my favourite new features.
Have a few bookmarks on a page and want to switch to different bookmarks. Then use Bookmark navigation. Just drag it in and you will get configurable navigation on all your bookmarks.
How about Page Navigation. Same again. Simply drag page navigation onto your pages.
This is fantastic and you can even use custom navigation so users can only navigate to specific pages . It would be great if you could add images to these buttons too but lets see how things develop.
Paginated reports inside Power BI.
Smart Narratives going into General Availability
Anomaly Detection going into General Availability
Automated Insights is shipping later this year
Data set hub to recommend promoted and certified datasets
Excel – Connect to data sets using Get Data in Excel
Coming soon, the ability to create Power BI Apps and turn it into a Teams App, bringing Teams and Power BI together
Quick Create from Sharepoint lists
Live connected to the datasets in order to connect to multiple datasets being updated.
Governance and Scalability
Microsoft Information Protection
MIP is becoming an even more important part of security in Power BI and across all Microsoft services. This is a complex area and requires much more research. Including what Information protection service is best for you. We will look at this in more detail in another post.
Its important to note that there are additional costs per month to this service.
No matter what the data volume is, your queries should always run fast
Based on the existing aggregations feature. If a user happens to drill down to detailed reports the system will automatically reverts to direct query, rather than the imported data.
Previously, this had to be set up by the developer but moving forwards the aggregations will be created for you.
Coming later this year and brings together Power BI and Azure Synapse Analytics (If using the Azure Warehouse as the underlying database)
Large Models Feature
Another Premium feature. Used with incremental refresh a dataset can grow up to 80% of the premium capacities total memory.
Used for fast real time analysis.
Partitions are created by year and your current year will be set against real time data. This is such an exciting announcement. I cant wait to get started with it. (Premium only)
Azure Stream Analytics is used for this. (See Power BI Streaming Dataflows)
The Microsoft Business Applications summit was again, full of really exciting new innovations.
However, Its becoming really clear that those on pro licenses are now really missing out on features like Goals, streaming dataflows, machine learning, etc.
At the beginning of this journey, the big factor for moving to Premium was simply to give viewing access to many more people, along with the security that power BI provides. This is simply not the case any more.
Thankfully Premium per user licenses gives smaller companies a way to move to Premium at lower cost. the barrier to entry has been significantly lowered and it seems that Microsoft expect us to move to those licenses if we don’t want to get left behind.
it should also be noted that those that have Power BI Premium through their E5 licences can simply pay the difference and move up to the Premium per user licenses.
Another slight negative was the fact that there are issues with features already available that were not dealt with at the conference. The big one for us at the moment is the fact that Premium Pipelines simply don’t work with dataflows.
When the recommendation has been to use dataflows to separate this job out from the pbix file, it seems a real shame that they have not acted accordingly to get this into Pipelines (Maintain Development, Test and Production areas)
Another feature of Power BI that is lacking is source control. there are hopefully some new features coming to configure with DevOps. It is to be assumed that any changes to this will be Premium only.
With this in mind, its still exciting to see all the new updates, and the fact that they are tying Power BI in much more strongly with the other Power Platform offerings.
What was your favourite announcement from the Microsoft Business Applications Summit this year. I would love to hear your thoughts.
Currently Premium Gen 2 is in Preview (Jan 20201) but it looks really exciting for those with Premium capacity. Lets have a look at Gen 2 and see how it differs to Gen 1
With Premium Gen 1 we are bound by the number of vCores and by the memory we have. Lets take the P1 SKU as an example
P1, P2 P3 P4 = Are all Premium SKUs. Each one has more vCores for better performance
SKU = Stock Keeping Unit
vCores = Virtual Cores is a puchasing model which gives you more control over compute and memory requirements.
When it comes to Premium, reports would slow down if there were two many queries running. And if people were using many models at the same time you would have to wait for your time slot.
As you can see, there is only 25 gig memory across the data sets Once a report isn’t being run and used any more, that memory gets dropped and is added back into the pool.
Report users and report developers are also fighting with report refreshes.
Premium Gen 2
Gen 2 is not in generally availability yet but if you have Premium you can switch to Gen 2 in preview.
In Power BI Admin (as the Power BI administrator or Global Admin)
Go to the Capacity Settings and switch Gen 2 from disabled to enabled.
You can also go back to Gen 1 if you need to but if you do make sure you flag up any issues you are having.
lets have a look at the model compared to Gen 1
The end users encounter the throttling and performance issues with Gen1 because they physically only have 4 backend vCores. Now with Gen2, Autoscaling allows you to deal with spikes. This is not available yet but will be coming. This is helped by the fact that there are other vCores that can be called on.
If you do come up to the 4 core Limitation it will, or may lend you a vCore so you don’t see impact for your end users
Previously our admins had to deal with this kind of problem but this will really help automate these kind of issues
Data Sets can go over the 25 gig memory capacity. Previously Premium was 25 gig for all the data sets. Now Data sets are gated individually.
This is a fantastic update. We don’t have to worry about the collective size of all our data sets.
Previously there was a maximum of 6 refreshes at any one time. Otherwise you can get throttled.
With Gen 2, refreshes get spread out over a 24 hour period and don’t impact other queries from users. refreshes just run
The looks great. People are seeing refreshes of an hour and a half coming down to 10 minutes.
Capacity Usage Metrics
This is coming soon and will have a breakdown of items.
Its a little annoying when you have set up gen 2 and want to view the metrics to see how everything is working but currently can’t.
With Gen 2 we will also be able to work against a chargeback model. This means that we can spread the costs of Premium between distinct areas of an organisation dependant upon their usage.
Again the workload settings aren’t fully functional at the moment but more will be coming.
For example for data set workloads we can specify minimun refresh intervals and execution intervals. we can detect changes in our metrics.
We don’t have settings for dataflows and AI yet.
Why go with Premium Gen 2 Checklist
End users see faster reports
Refreshes, we now don’t have refresh bottlenecks and we remove refresh failures due to throttling
Premium per user
Improved metrics will be introduced soon
Proactive admin notifications
Why it may be worth waiting until Preview becomes GA
It looks like people are having some issues with dataflows and there is already be a known issue about this
It looks like this might be fixed quickly, and once dataflows are OK, it seems like. A workaround is to move your dataflows out into another Workspace and then back in but hopefully this will get much better.
Is Premium Gen 2 going to be the same price as Gen 1?
Is there any way to find out how many dataflows you have if dataflows are an issue?
Will we still give great functionality to the Power BI Pro users?