After 14 years using Microsoft On Premise BI Tools (SQL Server, Reporting Services, Integration Services and Analysis Services) Its time to embrace Business Intelligence in the cloud.
Incremental Refresh came available for Power BI Pro a few months ago but when tested there was am issue. Error Resource Name and Location Name Need to Match. This should have been fixed in April so here is a quick check list of how you approach incremental Refresh
Define your Incremental Refresh Policy
What is your data source?
Are new rows simply added to the dataset in power BI?
Are records deleted?
can old rows be updated?
If rows can be updated, how far back does this go?
How many years worth of data do you want to retain?
Which tables in your data set need incremental refresh?
Can you define the Static Date within the table that will be used for the Incremental refresh?
have you published to P
Each of these points are very important and will establish what you need to do to set up the incremental refresh, from your data source up to power BI Desktop and Service.
Set up incremental Refresh in Power Query Editor. Create Parameters
Go to transform data to get to the power Query Editor (You can either be in desktop or creating a dataflow in Service)
The two parameters that need setting up for incremental loading are RangeStart, RangeEnd
Range Start and Range End are set in the background when you run power BI. They partition the data
Query Folding – RangeStart and RangeEnd will be pushed to the source system. Its not recommended to run incremental processing on data sources that cant query fold (flat files, web feeds) You do get a warning message if you cant fold the query
You cant Query fold over a Spreadsheet. Its possible to query fold over a Sharepoint list. the recommendation is to set Incremental processing up over a relational data store.
For the desktop. allow yourself a good slice of the data to work with. for example, a year, two years worth of data.
Filter the data in the Model
Still in Power Query Editor.
Add your parameters to every table in your data set that requires incremental load
Find your static date. E.g. Order date, Received Date etc
Close and Apply
Define your Incremental Refresh policy in Power BI Desktop
Go to your first table and choose incremental refresh
Example screen shot of an Incremental refresh policy
Store Rows
In the Above example we are storing everything for 5 years. its set to months so the partitions are smaller
Refresh Rows
Our Refresh policy is very simple. Data is inserted. not deleted or imported.
If this was running every single day then you would only need to refresh rows in the last 1 day. However as a just in case 1 month has been used, in case for any reason the job is suspended or doesn’t run.
Detect Data Changes
Detect Data Changes has been used. The months data will only be refreshed if the ImportDate for this record is changed (Or there are new records)
No records are deleted so we don’t need to worry about this
If you want to use Detect Data changes you must have an Update date on your source data. This may impact your data source.
Are you running straight from source into Power BI and there is no Update Date available?
Then you will need to make the decision to have a reporting database layer, Where you can add UpdateDate logic to your table
Is there a possibility that records are also deleted?
You need to deal with this slightly differently
Set Soft Delete in your reporting data
Add isDeletedColumn and update LastUpdatetime and isdeleted to 1 in the warehouse
This will come through to your model as updated and you can filter out all the isDeleted records
Publish the new Power BI Report and Data Flow
You might be thinking at this point, but I dont want the filters that I have set for Desktop to be applied in Service. I want to see all my data in Service
Dont worry, in Service RangeStart and RangeEnd don’t keep the dates specified for the filters in Desktop.
they are set via your incremental refresh policy. So they are set as the partitions for our 60 months (Instead of setting it to 5 years, meaning there is one RangeStart and OneRangeEnd, you get RangeStart for Month one, RangeEnd for Month 1, RangeStart for Month2, RangeEnd for Month2 etc, breaking your 5 years down into much smaller partitions to work with,
Testing Your Incremental Refresh
Make sure before you add the incremental Refresh you have done a full process of your data. Its good to get an idea of the time you are working with
Once installed, connect to the SQL Database that is your data source
So long as you have the profiler extention installed you can Launch Profiler
If you don’t have it, you can download the extension
Once Launched Start a Profiler Session
Go into Power BI service, Datasets.
click on refresh now and then go to data Studio to see whats happening
From Logon to Logout during the run, it took 20 minutes because the entire model is refreshed. obviously it would be really good if we could get the time down using incremental refresh
Before you set up Incremental processing, ensure that the services preceding the Power BI Load have been well tested and signed off.
Once Incremental refresh is in place, you can refresh again and check your findings in Azure DAX Studio.
the current recommendation, due to all the possible data source amendments and issues, its recommended to start with a relational data base as your reporting layer. Then you can Query Fold, Add IsDeleted and Updatedate columns and logic to this data source.
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 .
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.
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.
As ever with DAX, I tend to have to remind myself of the basic every so often, especially when i have bee concentrating on other topics.
We need to remember the following
Model Relationships propagate filters to other tables.
Product can be sold many times. (1 to many)
Have a look at the model in your Power BI desktop file
If you click on the join
You can see Cross Filter Direction (In this case Single) We can Filter the measure within the fact table by, for example, Product Name. But we cant Filter Product Name by, for example Sales amount within the fact table
When you are looking at Measures you basically filter a measure by either an implicit filter or an explicit filter within the DAX.
Confusing? How can the above information not be?
Lets take this a step backwards by Looking at implicit filtering
using the above example we have taken Color from the Product table and Order Quantity (The Metric) from the Fact table
We implicitly Filter Order Quantity by colour. No need to specify anything in DAX
Implicit – Suggested though not directly expressed
CALCULATE
Lets create a measure
Order Quanity of Yellow Products = CALCULATE(SUM(FactInternetSales[OrderQuantity]),DimProduct[Color]=”Yellow”)
So here CALCULATE evaluates the summed value of Order Quantity, against the explicit filter of color = Yellow. So in the above visual, it ignores the implicit value of the Color filter within the visual.
Here is a nice reminder
If you drag a field into your visual its an implicit filter
If you set the filter within your DAX its an explicit filter and it will override what is in your visual
CALCULATE allows you to calculate a value against a context (The filter modification).
Lets change this slightly
Order Quantity of Yellow Products = CALCULATE(SUM(FactInternetSales[OrderQuantity]),ALL(DimProduct))
Now instead of Colour = “Yellow” We are saying Calculate against all Products
This time, note the total matches the total. this is because we are ignoring the colour context and Getting the total of ALL products
* quick sanity check. The visual isn’t quite right. The totals should be the same on the rows and the total column. This must simply be an issue with the visual I used here.
FILTER
Filter basically returns a table that has been filtered. Lets have a look at a FILTER Function used in very much the Same way as CALCULATE above
TOTAL Order Quantity Yellow colour = CALCULATE(SUM(FactResellerSalesXL_CCI[Order Quantity]),FILTER(DimProduct,DimProduct[Color]="Yellow"))
We are again calculating the sum of Order Quantity, with a Filter of Color = yellow. Lets look at how the visual changes
This time, instead of seeing the total for yellow against every other colour quantity, we only as see the measure against yellow.
the great thing about FILTER is that you can have more than one Filter as an OR
TOTAL Order Quanity Yellow & Black = CALCULATE(SUM(FactResellerSalesXL_CCI[Order Quantity]),FILTER(DimProduct,DimProduct[Color]="Yellow"|| DimProduct[Color]="Black"))
Now we can see Yellow OR Black Quantities
how about if we want to see only yellow products in 2014. FILTER comes in useful for this
TOTAL Order Quanity Yellow & Black = CALCULATE(SUM(FactResellerSalesXL_CCI[Order Quantity]),FILTER(DimProduct,DimProduct[Color]="Yellow"),FILTER(DimDate,DimDate[CalendarYear] = 2014))
This time, Not only are we only seeing Yellow product Quantity but the Quantity sold in 2014
FILTER is obviously slower than CALCULATE so if you are only filtering on one thing, go for CALCULATE.
Deny the creation of a resource group without applicationName Tag
Every resource group should have an application name because Resource Groups should be Application Related. However you cant get the applicationName from the Above Subscription.
As a Example, the Resource Group is for HR Performance.
applicationName Tag Configuration
Deny the resource group if its missing the applicationName tag
Steps to apply this policy in Azure
Search for policy and Assign
Go to Assignments. This shows you all the policies or Initiatives that are in place. The Scope for this shows that the all subscriptions are being shown in this list.
Click Assign Policy
Set Scope of Policy
First thing you need to do is set the Scope. We have Management groups in place and as a test, a Personal Subscription is being used
The Resource Group isn’t set because this is just being set at Subscription level
Exclude Resource
You can also exclude resource, and in this case resources from the above Policy. However for this policy we don’t need to do this.
Set the Policy Definition.
There are two types of definitions. Custom and built in
If you look for ‘Tag’ you get a list of the Built in Definitions for tags.
We know that we want to ‘Require a tag on resource groups’ so this built in policy can be selected
Choose the Built in policy and Select
The Assignment Name has been slightly changed to include the tag this will be set for
We are in the Basics tab at the moment
Parameters
Go to the Parameters Tab.
Remediation
Click the Remediation Tab
In order to understand the Remediation tab there are a few specific concepts to Understand
Managed Identity
Managed identities is a more secure authentication method for Azure cloud services that allows only authorized managed–identity-enabled virtual machines to access your Azure subscription.
This means that any service that ‘does something’ to another service can do so automatically using a Managed Identity
DeployIfNotExists and Modify effects
Every single Policy in Azure Policy has a single effect. Effects behave differently if they are for a new, updated or existing resource. The Effects supported are:
Append
Audit
AuditIfNotExists
Deny
DeployIfNotExists
Disabled
EnforceOPAConstraint (preview)
EnforceRegoPolicy (preview)
Modify
The question is. What is the effect of Require a tag on resource groups
Definitions
Go to Definitions in Policy and Search for the Built in Policy.
The effect of your chosen built in Policy the effect is to deny .
You can also see the JSON Definition of the chosen Policy
Because the Policy is deny we don’t need to set up a Managed Identity
Click Review + Create
And Click Create
Definitions
Going back to the policy Definition
You can see that this Policy has been assigned to a Subscription
Checking your Policy has worked
To test the policy create a new resource Group within the selected Subscription without adding any tags
When you get started with Azure, you want to ensure that your resource groups and resources are tagged. this is great for Management of your Azure Subscriptions/
You can categorise all your resources with tags. You can also view Billing by Tags.
When you add a tag into the Portal (Or programatically) you can add in any tag name as you go. Once created they come up in a list and you can choose from them again.
You can use Azure Policies to try and Enforce some Tagging Logic
Azure Policies
Policies allow you to use the built in or custom definitions. the Policies can then be assigned to a Management group, Resource Group or Subscription
Policies are executed when new resources are created. old resources may not be compliant
You can use built in tagging Policies that are already defined within definitions in JSON (Built in)
Or you can add a new definition by adding your own JSON or copying and amending a built in Policy (Custom)
We are going to have a look at some specific examples of how to use Policies for Tagging.
Debbies Microsoft Power BI, SQL and Azure Blog 