Microsoft Business Applications Summit 2020 and what is means for Power BI users

The Microsoft Business Applications Summit was held online this year on the 6th of May and as a UK user, that meant an entertaining evening of Power BI and Business applications learning and updates .

https://www.microsoft.com/en-us/businessapplicationssummit

The evening was incredibly well thought out with 30 minute sessions on each of the Power Platform areas.

  • Power BI
  • Power Apps
  • Power Automate
  • Power Virtual Agents

Our attendance was mostly based on the Power BI sessions that night. We wanted to focus on what to get excited about with Power BI and when to get excited about it. However there were also some great overviews of how to use all the applications together which helped us to understand the Power platform as a whole.

Power BI Is split into key area to drive data culture in your organisation

And each of these areas contain some fantastic new updates.

Each area is going to be looked at in a lot more detail in blog posts to follow but in the first instance, lets take a look at all the exciting updates.

Amazing Data Experiences

There are now over 2 million Power BI desktop users. 97% of all Future 500 businesses use Power BI. It is the Leader on the Gartner 2020 Magic Quadrant https://www.informatica.com/gb/magic-quadrant-MDM.html, and on the Forrester Wave https://info.microsoft.com/ww-landing-Forrester-Wave-Enterprise-BI-platforms-website.html?LCID=EN-US

All this comes from providing amazing data experiences to customers.

AI Infused Experiences

The very first AI visual for Power BI was the Key Influencer. Next came the Decomposition Tree and then the Q&A visual. All these visuals have proved big hits with report consumers who get the ability to understand all the factors that drive a metric, and can ask more and more questions against data in their own way.

Lets have a look at some of the Updates, and even more exciting is the new visual coming for Smart Narratives

Key Influencers Update

Key influencers are fantastic and we have been using them the moment they were added into Power BI as preview.

We have used it across lots of projects, For example, Social Media influencers. What influences a negative tweet. Customer Churn is another great use case for the Key influencer

April 2020

Key Influencers now supports continuous analysis for numeric targets

  • May 2020

Binning Support, Formatting options and Mobile Support

  • June 2020

More Visual Updates go into preview and will now be usable for Live Connect

  • July 2020

Counts will go into preview

  • August 2020

All the key Influences improvements should be moving to GA (General Availability)

Power BI Decomposition Trees Update

The Key influencer allows you to analyse a category within your data and discover influences and segments. The Decomposition tree allows a report consumer to analyse a business metric however they want.

  • May 2020

You will be able to conditionally format your visual very soon. Using the above visual, you might have the most engaged businesses in Nottingham, but conditional formatting could show the most percentages of meeting cancellations. We can do conditional formatting on another metric

You will also be able drill through from the decomposition tree visual to more detailed data.

There is a reason why people love this visual and we cannot wait to start implementing these updates into our reports.

  • June 2020

The Decomposition Tree will now be out of Preview and in General Availability

Q&A Visual

We can now include Q&A in the reports as well as just from the dashboards and there are some great new updates for this

  • April 2020

Add Terms within Q&A allow for better synonym matching and Suggest questions will allow you to tailor some ready made questions for your user

  • May 2020

New Q&A Visual Updates (TBA)

  • September 2020

Direct Query will be coming for Q&A Visuals.

New AI Visual – Smart Narratives

  • Available Later this year

We got a sneak peak of the New Smart Narratives visual and it looks so good.

Report authors will be able to add dynamic interactive narratives to reports and visuals. These narratives update when you slice and dice the data.

It automatically does trend analysis

The visual calculates the growth automatically with no user imput required

You can also add dynamic values as part of the narrative and even use Q&A to create the value

This is one development we are really looking forward to.

Power BI End User Personalisation

  • June 2020

Another development that is going to change things for report consumers in a really good way is personalisation


You may love a stacked area chart but Julie in HR May hate them. Consumers can now click on a visual, go to personalise and change the visual to suit their needs better. This visual is saved specifically for that user (As a modified view with a personal bookmark) and its easy to go back to the original visual.

This is currently in Preview so if you want to take advantage of it, make sure you go to Options and Settings > Options > Preview Features

PowerPoint for Data – Onboarding and Lessons Learned

Microsoft acknowledge that PowerPoint has really good on boarding features. Lots of people happily use Powerpoint. They should have the same experience with power BI

All the following updates come from lessons learned with PowerPoint:

  • April 2020

Lassoo Select of visuals and Datapoints. this is great. finally you can lasso (Drag a rectangle around) a number of visuals together in desktop. You can even do this with data points

  • May 2020

Drop Shadows. How to make a great report look even Nicer. Add Shadows to them. Another feature I cant wait to use

Power BI Templates Experience

  • September 2020

Report authors will get lots of help to create report pages with pre-made templates like PowerPoint layouts. Obviously Templates can already be created for Power BI but this will make everything much more intuitive and easy to use.

I’m a big fan of Story boarding in PowerPoint. I wonder if we will see this come into play in power BI?

Modern Enterprise BI

Power BI is no more a business led self service tool. Its can now be used right across your large scale business enterprise. We can now use Power BI as an enterprise scale analytics solution bringing together all our insights to drive actions and improve performance.

There are lots of key points to consider within this Microsoft strategy area. For example:

  • Admin and Governance
  • Lifecycle Management
  • Lineage and impact Analysis

The modern enterprise BI has the most impact when customers are using Power BI Premium capacity nodes. lets have a look at some of these areas in a little more detail, and specifically understand what Power BI License you need to have to make use of these new capabilities.

Power BI Lineage and Impact Analysis

  • April -2020

Lineage and Impact Analysis went into Public Preview in October 2019. We are very much looking forward to looking at this in more detail very soon.

the real excitement is, the ability to incorporate more services within Azure into the Lineage which will make it much more essential when looking at how your data is structured

Within the Power BI service, Change the view to Lineage View

You get little logos to show if your dataflows or data sets are promoted or certified.

Impact analysis is available from your data set. clicking Impact Analysis will allow you to assess the impact of a data set change. How will your changes impact downstream reports and dashboards?

You can also see your visitors and views and even notify people about upcoming changes.

It appears to be available for Pro as well as Premium but as yet, we aren’t aware of any differences between the two.

This will be explored in much more detail in a post coming soon.

Enterprise Semantic Models

Another big game changer for Power BI Users

Again, we are moving away from creating your data set within a power BI pbix file which is only available for the user. Just like Analysis Services Tabular Model, we can now create the model with Power BI, available for everyone to use, From business users, analysts, to Power Users.

The enterprise semantic model comes with some great updates:

Shared and certified Datasets

  • April 2020

When you certified a dataset in Power BI, You are stating that this data set is a single version of the truth. when we connect to a certified dataset the model may contain a large amount of data, and your specific reporting requirements may require you to only select a few tables from the central model.

XMLA Endpoint

  • May 2020

Power BI Premium Only

XMLA Endpoint allows 3rd parties to connect just like you can with Analysis Services models. This is yet another game changer as it allows organisations to create the one version of the truth using power BI.

Previously, this could have been done using Analysis Service, either in the cloud or on premise. Your own centralised Tabular model. this data model could be connected into from various data visualisation tools, and data management tools, e.g SQL Service Management Studio, DAX Studio, ALM tookit etc.

Now with XMLA endpoints open platform connectivity, the datasets you create in Power BI will be useable from a variety of other data visualisation tools, if your users don’t want to use Power BI.

This is excellent for IT Led self service. Your centralised Power BI Team can create the dataflows and models and business users can take those models and run with them. obviously Power BI is fantastic but you don’t lose out on users who absolutely want to stick with the visualisation tool that they know.

This is all about delivering a single one version of the truth semantic data model

Power BI Extensibility

  • Available later this year

This will enable external tool extensibility to unlock additional semantic modelling capabilities.

will all be able to get access to the Power BI Tabular model (data set) in the same way as they would an Analysis Services Tabular model.

This is due out later this year and as yet, its unsure if this is just for Premium or if it will be available to pro users too.

Translations (Available with Power BI Extensibility)

Translations allows you to create multi cultural datasets. These meta data translations are an offering of the analysis services semantic model, and previously locked away in the Analysis Services engine.

The extensibility model for Power BI will soon allow us to finally use Power BI translations within power BI Desktop

Clicking Tabular Editor allows you to connect to your Power BI dataset and use Analysis Services Features. Translations being one of the major draws to Analysis Services Tabular.

This should be available later this year, and will be looked at in much more detail within future posts

Deploy to Workspace Incremental Metadata only deployment

This is a Premium Only service. Imagine that you have implemented your translations and want to publish your new data set.

There are no data changes so you don’t want publish to involve the data. When you publish you will get impact analysis

However, you actually want to do an Incremental meta data only deployment. So instead of simply publish, go to the Settings within the Workspace in Power BI Service.

Go to your Premium tab

And copy the Workspace connection link. this Workspace connection can be used just like an Analysis Services Workspace. You can use this workspace name with the ALM toolkit (Under Extensibility) to look at comparisons and pick and choose what you want to update.

The Power BI Tabular model has been processed in the same way as you would an Analysis model. Thanks to these new External tools we can do so much more with the power BI Datasets.

Composite Report Models

  • September 2020

We have looked at the enterprise Semantic Model from the BI Developer. Now its time to look at what we can do for the data analysis.

Previously, there has been lots of talk about composite modelling

“Allows a report to have multiple data connections, including DirectQuery connections or import”

Composite models allow the developer to created an aggregated data set which allows you to reduce table sizes by having imported data at granular level (So you get the full suite of DAX to work with) and then you can drill down to granular data in direct query mode.

Composite report models are basically composite reports as opposed to composite models. I got a little confused between the two as they are both called composites but they are quite different.

As a data analyst you get data from a Certified data set. this is essentially a Live Query because you are connecting to a Power BI tabular model


these screen grabs are from the Conference. We will be researching this with our own data sets in due course

The analyst will now be able to combine data from multiple data sets and create relationships between them. Composite modelling can be mashed up with local data by the analyst. This will bring so much more power to the analyst.

It will be really interesting to see how this works over the next few months. Again its uncertain if this will be available for Pro users but we will be looking at this in much more detail soon.

Full Application Lifecycle Management

  • Public Preview May 2020

Power BI currently consists of the App Workspace (for collaboration) and Apps for consumers. this gives you your development, test and production environments.

Deployment Pipelines is the next level of lifecycle management. If you use DevOps you have seen and probably used Pipelines for other business requirements. For Premium capacity Workspaces, Pipelines can now be created to deploy to Develop, test and production Environments

This is a fantastic new development for modern enterprise BI. Each Workspace can be compared within Service and allows you to be more agile and responsive to users needs. We are really excited about this one.

Drive a Data Culture with pervasive BI throughout your Organisation

Automatic Performance optimisation with Azure Synapse Analytics

Relates to the Data Stack. Microsoft are working on deep integration with Azure Synape Analytics.

We will be looking at this in more detail later but there are big changes coming:

  • Materialised views to improve performance within the Synapse layer.
  • Useage based Optimisation against Synapse.

Common Data Service

This sits with the Action key point for driving data culture. this is another area that the Microsoft team were very excited about. As yet we are being cautious and want to do some more research around this topic.

You will now be able to direct query the Common Data Service. the CDS ties in with Power Apps and seems to be used very much within that domain. Its worth noting again at this point that Power BI Does not exist alone. It is part of the Power platform.

Internal data is stored in CDS. External data is brought in via connectors. there are 350+ connectors that can be used for External data. However data within the CDS is Smart, Secure, and Scalable.

We will be looking at CDS in much more detail in relation to Power BI

This is just a first high level look at some of the offerings from the Business Applications summit. There are so many great sessions to look at for more in depth details. It looks like an incredibly exciting time to be involved with Microsoft business Apps.

PowerApps in Power BI – Simple Power App to Insert a record into a Table to be used for a report Filter

First of all I would like to big up guy in a Cube https://www.youtube.com/channel/UCFp1vaKzpfvoGai0vE5VJ0w

For being one of my go to’s for anything Power BI.

I had seen in the August power BI release that there was a new PowerApps visual in preview but its Guy in a Cube that gave me the confidence to try it out.

In the September 2019 release its become fully available and I have quite a lot of business logic that I could get off the ground and into my projects using PowerApps

My initial challenge to resolve for the business:

I have worst served customers in my Data

SELECT [Top 20 Worst Served] FROM [fact].[Fact]

  • Which is a 1 or 0

SELECT [Top 20 Worst Served]  FROM [dim].[Customer]

  • Which is a true or false flag

I can then have reports for worst served customers filtered by the above

However, in some cases there may have been an investigation and its been deemed that the customer shouldn’t be in worst served. Note that this information isn’t capturing in the source data set.

They want to be able to immediately say this customer is fine and the results will then update accordingly.

Every time the data is refreshed, these data items get fully refreshed in case they are not worst served any more.

In the current Reports, Filters are set on the page for both the above columns in the dim and the fact

  • Where Dim Customer WorstServed = Yes
  • Where Fact WorstServed = 1

Question 1

  • Do the users want to see an immediate change to the report?
  • Or are they happy for it to come through on the next refresh?

The users want to see the report metrics change immediately based on them adding in information

Quick Steps to adding the POWERAPP into Power BI From Guy in a Cube

  • The Tables that are to be updated need to be in Direct Query Mode
  • The tables connected to these tables  on the 1 side of the relationship, should be in Dual Mode (Which acts as Import or Direct Query)
  • Design your reports in the Desktop
  • Once designed DO NOT ADD THE POWER APP INTO THE PBIX FILE
  • Publish your report
  • In Power BI Service click on edit Report
  • Then in the Visualisations pane, If you don’t have already, Go to the Market Place and choose PowerApps
  • Add the POWERAPP into your report
  • Choose App (Or Create New App)
  • Add in the columns required to use the POWERAPP
  • In PowerApps give the people access who need to be able to update the data

Things to Consider

  • The users want to see the change immediately rather than on the next refresh
  • My report pages are already created and I have many tables (All Imported) and relationships in the model.
  • Dual Storage Mode, I have never used this before. How many tables would need this change in the data source?
  • The PowerApp will be a new enhancement to the reports
  • The PowerApp hasn’t been built yet
  • I am concerned about adding the visual into Power BI Service in Edit mode because the standard way to create reports is in Desktop. I have many more Updates to do after the power App has been added and I don’t know how adding visuals in Service only will work with the ongoing App amendments

Possible Data Solution 1

in the PowerApp, the User Adds in the CustID, (And an automatic date is assigned) these get inserted into the table Staging Worst Served Amendments

Then Dim Customer and the fact table are checked through using the custID and the items are set to ‘No’ and 0 as above (this is clearly the most difficult bits because it’s an update based on the CustID

The next refresh will again change 153 to Worst Served Yes, however an extra bit of script in the Stored Procedure that creates the table will Check the Worst served Amendments table and if there, reset to No.

The above Staging table is only used for the overnight data refresh

Changing Import to Direct Query

To develop against the above resolution, both the fact table and the property table need to be changed to direct import. What do we lose if we do this?

All my DAX functions for time will be lost against my fact table (Year to Date, This time last month etc).

Therefore I really don’t want to lost these DAX queries by changing to Direct Query

Also currently I cant seem to change the setting from Import to direct Query in Properties. Unless this is a bug, It seems you would have to start again with the tables and re import them as direct Query.

Is there another way that the new logic can be added without setting both these tables to Direct Query?

Possible Data Solution 2

Filters in Power BI

  • Where Dim Customer WorstServed = Yes
  • Where Fact WorstServed = 1
  • And Staging Worst Served Amendments CustID is NULL

Issues with the Above Solution

You cant have a Filter for an empty customer ID because this is a Left Outer Join.

There may be other issues. What happens if the user accidentally adds in Multiple custIDs and the relationship changes to Many to Many?

Normally I would deal with this by merging the tables in a left join in Power Query Editor

As a consequence I need to think about another Solution to this problem

Possible Solution 3

I have already checked that you can merge a direct query and and Imported Table. See https://debbiesmspowerbiazureblog.home.blog/2019/09/19/what-happens-when-you-merge-a-direct-query-and-an-imported-table-in-a-power-bi-composite-model/

This means that we can left join the table and filter out the none nulls in the report

Creating the Staging table for the Insert

Top tip is that within the Power APP your tables will need a Primary key or else the POWERAPP will fail

First I need an actual table in my Azure SQL database. In SQL Server Management Studio I created the table via SQL

CREATE TABLE [staging].[WorstServedAmendments](
	[CustID] [varchar](255) NOT NULL,
	[AmendmentDate] [datetime] NULL,
 CONSTRAINT [PK_WorstServedAmendments] PRIMARY KEY CLUSTERED 
(
	[CustID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Creating the Power App

I work with such a great team. Thanks to Liam Chapman and Happy Wired for giving me a heads up on PowerApps and writing the code for me

Log into https://powerapps.microsoft.com/en-us/

I need an update button. A delete button and a text box to add the customer number into.

It would also be good to have a little message to say that the button has been successful

These objects can be added via Controls.

  • Timer1 Relates to a hidden Timer on the page
  • txtUpdateMsg relates to a hidden text box that will appear on update
  • btnDelete relates to the Delete Button
  • btnUpdate relates to the Update Button
  • txtCustID (hidden in image) relates to the textbox where the user will add the customer number
  • lblForTxtBox relates to the description label

Data Source (Start Amending from here)

I’ve connected to my Azure database and I’m connected to my new worst served table

The CustID is a varchar within the SQL Table

Update Button

Lets start with the basics and start with the Update button

OnSelect

Patch('[staging].[WorstServedAmendments]',Defaults('[staging].[WorstServedAmendments]'),{ProNumber:Value(txtProNumber.Text), AmendmentDate:Now()});
UpdateContext({TimerStart:true});

Code

Patch – Modifies or creates one more record in a data source, or merges records outside of a data source

Defaults – Use Patch with Defaults function to create records (Insert a new record into a table)

Value() – This is converting a text value to a number

UpdateContext – Create a context variable to temporarily hold a piece of information. In the above case we are calling it TimerStart and setting it to true. We are basically starting the timer when we click update because we have a timer hidden on the screen

Timer1

On Start we are going to use the Context Variable TimerStart

Hide the Timer because we don’t need to see it

Create a context variable called SuccessMessage and set to true

Create the context variable SuccessMessage and reset it to False

Create another Context variable called TimerStart and set to False. TimerStart was started on Update and now on Time its being turned off again

Delete Button

OnSelect

RemoveIf('[staging].[WorstServedAmendments]',ProNumber = txtProNumber.Text)
UpdateContext({TimerStart:true});

Code

RemoveIf – Remove a record if it exists

UpdateContext – Create a context variable to temporarily hold a piece of information. In the above case we are calling it TimerStart and setting it to true. We are basically starting the timer when we click Delete because we have a timer hidden on the screen in the same way as when we update

txtUpdateMsg

Visibility relates to SuccessMessage context variable. Its visible on timer start (true) and disappears on timer end (False)

What appears when the timer is ON

This is a very basic Power App that will now allow the user to add and remove customers from this table. Save and then Publish the App so it can be used in Power BI

Add the table into Power BI

The new worst served table needs to be Imported into Power BI as a Direct Import so any changes the User makes will reflect straight away in the reports

Just to make sure that everything is OK I’m going to add one Customer into the table before its added just to have a record to work with.

In your Power BI Desktop file, Edit Queries and Choose recent sources if you have already connected to your data source.

Select the Worst Served Table

We can now have composite models where some tables are import and others are direct query.  The new table is added in as a direct query

Close and Apply

Note the message regarding potential risks when you are querying data at source and have other tables imported in memory

Next go into Edit Queries and Merge Table

And merge the customer table with the direct Query table

Click OK.

this connect the table so grab customer ID

This will be added to your customer dimension

Note that so far, DimCustomer hasn’t been automatically changed to Dual Mode after being merged with the direct Query table so we dont need to worry about Dual mode in order to create our new logic.

Close and Apply

back in the desktop, go to Marketplace and grab the Power Apps Visual

The how to guide states to not add the Power App within your Desktop report so Publish the report and lets move to the Power BI Service

Power BI Service, Edit report and Add in PowerApps Visual

On the report you want to update Click Edit Report

The Power App visual will now be available in Visualisations because it was added in the desktop file

Add the Power App in power BI service

In order to test out the new service I’m adding in a blank page

Click on the power App visual and add it to the new blank page

I want to add the CustID and the Date from the worst served new staging table

Then I can choose App rather than create new because I have already published an App

Im choosing my Worst served App and click Add

Ive clicked go to power Apps Studio which opens the PowerApp in Studio and you also have the powerApp in Power BI Service

The very first issue I have is a formatting issue. My Power App is Tiny and unreadable. Time to go back to the power App Studio and change the Settings

PowerApps studio

App Settings – changing the App to Default size 16.9. For the time being Im not going to think about making the app look good. I just want to be able to see the text on the power BI page

  • Save and publish the PowerApp
  • Go back to the Power BI service

Power BI service

I had to delete and readd the PowerApp to get it to refresh.

Its also worth noting that if you dont create your visual to the right size before adding your App, the App will have scroll bars etc and you cant change the size of the PowerApp, only the size of the visual that holds it

The Power App doesn’t look great but it will do to test.

First of all we want to see if it works so add a table with the Worst served Data items

Add a CustID and click Update

It works. Now delete the item just added. Again it works.  This is part one done. Part 2 is that we want it to update worst served Customers from the customers table

How does the Updates Affect the pbix file?

Click Save in power BI service and go back to the power BI Desktop file

The new visuals aren’t there. This is as expected, because they were not created in Desktop.

Imagine that you have created your Power BI App Visual and you still have a list of updates, changes and bug fixes that you want to do within Power BI Desktop

If you update and rebublish you lose the PowerApp Visual

Currently this options is only viable if the PowerApp is the very last thing you do and you don’t need to update the pbix file at all.

As a consequence I don’t think that Editing your reports within Power BI Service is a good idea.

having chatted to a few people on the forums, Editing reports in service is normally done when you need something quickly and you intend to go back and update the pbix file with the amendment.

What happens when you add the PowerApp in Desktop

In Desktop Add the PowerApp and the table to check its working. Then Publish into the Service.

Note the change in how it works. If you Update or Delete, the table doesn’t change until you Click Refresh. If you add it in Service you don’t need to click refresh.

For the time being I’m going to accept this as it’s the only way to move forward and keep working with the pbix file.

I have created an idea on the power BI forum to deal with this issue https://community.powerbi.com/t5/Issues/PowerApp-with-DirectQuery-Mode-Need-to-be-able-to-have-correct/idi-p/791690#M48425

Omitting Worst Served Customers using the new Filter

I have Customer 1000000 and 1000001 in my Direct Query table and they have both been removed from the report

Great, I now have a Power App that can be used to omit records in a report without straight away.

There are some issues to be ironed out but this really opens up what you can do with Power BI

Power BI composite Models

For this example, add a Table in Direct Query mode from SQL Server. Here I’m using my AdventureWorks DB from my local database

Add another file (any Excel file you have) Direct Query is not available for Excel. Before Composite models was available, you couldn’t add Excel if your other table was direct query. Everything had to be the same import mode

Close and Apply

Now Composite model is available you can mix storage models. However you need to be aware that the Direct Query will have actual data within the query (SQL) rather than all the querying being done within Power BI

You can hover over the tables to see what table storage is set for each table

Note that we can see the data for Excel which can only be Imported data

However we cant see the data in the data pane for the Direct Query Fact table

We can go to the model view and connect the tables together

To show this I’m going to add Dim date from the SQL Data base as an Imported table (Remember the fact is a direct query)

A composite model is one where tables have different Storage types. there are now three storage types.

  • import – the Best way to use Power BI because everything is cached in memory
  • Direct Query – As above Power BI will query your data source and your measures and calculated columns will be created within SQL if you are connecting to a SQL database
  • Dual – can be both Import or Direct query. We will look at this in more detail later

live Query is only available using analysis services as a data source. In this case you can only use SSAS and no other data source.

Create your website with WordPress.com
Get started