Category Archives: Self-Service BI
Quick #PowerQuery trick – Get duration days between two dates
Just a quick tip that you might not be aware of in the Power Query Editor.
If you select two columns in the view and on the Add Column tab, select the Date button – you can select to Subtract Days
This will give you the number of days between the dates in the selected columns
Use the formula bar to rename the new column by modifying the step
Hope this can help you too –
5 reasons to use aggregations in Power BI
A lot has already been written on aggregations and it is one of the most exciting features of Power BI in a while. The guys at SQLBI did a session on it and my colleague Phil Seamark wrote an amazing host of articles with many details and tips and tricks on it. So why another article? Most people think aggregations is only applicable to petabyte scale data sets. That doesn’t have to be the true though, in this blog post I wanted to give my take on it and why it might be applicable to your current, smaller, model.
Here are 5 reasons you want to look at using aggregations:
- This is the obvious one. The data you want to analyze or report on doesn’t fit in memory. Imagine your users want to be able to see the details of every transaction and you have billions of rows. With aggregations you can store the aggregated values by product \ date etc in memory. You then leave the underlying transactions in the data source. However the magic here is that your users won’t even notice as it is one model and report.
- You want your data to be “real time” but not use DirectQuery all the time. When you are using DirectQuery for 100% of your queries are running against your data source. This might get you into performance and concurrency problems. With aggregations you can load a small set of aggregated data in memory that you can update and reload every minute or so. That aggregated data can take care of the majority of queries but will still be quick to load. You will have to be careful to make sure the data is in sync.
- Potentially you want to save cost. By determining what data is needed for 95% of the reporting cases you can trim down the data stored in memory. When data is loaded in memory it cost money when using Premium. Of course by offloading some queries to the data source you are adding load there too. So it depends on the ROI if this one is worth it.
- You want to optimize certain calculations. With aggregations you can use aggregation tables for DistinctCount calculations and use DirectQuery for lowest level calculations. Not just DisctinctCount will work but many other calculations too. This might be interesting to investigate but your mileage might vary. The team told me at one point we might have Aggregations over VertiPaq. When this happens performance optimizations using aggs will be even more interesting.
- Now the final one is to go read these blogs by Phil Seamark and try to understand what he is doing there. They show so many new ways This will allow you to solve many interesting scenario’s and allow you to”
I hope this has given you some idea’s for using aggregations even when you don’t have petabyts of scale.
What’s new for SQL Server 2019 Analysis Services CTP 2.3
We find great pleasure in announcing the public CTP 2.3 of SQL Server 2019 Analysis Services. New features detailed here are planned to ship later in Power BI Premium and Azure Analysis Services.
Calculation groups
Here is a question for seasoned BI professionals: what is the most powerful feature of SSAS multidimensional? Many would say the ability to define calculated members, typically using scoped cell assignments. Calculated members in multidimensional enable complex calculations by reusing calculation logic. Unfortunately, Analysis Services tabular doesn’t have equivalent functionality. Correction: it does now!!!
Calculation groups address the issue of proliferation of measures in complex BI models often caused by common calculations like time-intelligence. Enterprise models are reused throughout large organizations, so they grow in scale and complexity. It is not uncommon for Analysis Services models to have hundreds of base measures. Each base measure often requires the same time-intelligence analysis. For example, Sales and Order Count may require:
- Sales MTD, Sales QTD, Sales YTD, Sales PY, Sales YOY%, …
- Orders MTD, Orders QTD, Orders YTD, Orders PY, Orders YOY%, …
As you can see, this can easily explode the number of measures. If a model has 100 base measures and each requires 10 time-intelligence representations, the model ends up with 1,000 measures in total (100*10). This creates the following problems.
- The user experience is overwhelming because must sift through so many measures
- DAX is difficult to maintain
- Model metadata is bloated
Calculation groups address these issues. They are presented to end-users as a table with a single column. Each value in the column represents a reusable calculation that can be applied to any of the measures where it makes sense. The reusable calculations are called calculation items.
By reducing the number of measures, calculation groups present an uncluttered user interface to end users. They are an elegant way to manage DAX business logic. Users simply select calculation groups in the field list to view the calculations in Power BI visuals. There is no need for the end user or modeler to create separate measures.
Time-intelligence example
Consider the following calculation group example.
| Table | Time Intelligence |
| Column | Time Calculation |
| Precedence | 20 |
| Calculation Item | Expression |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Here is a DAX query and output. The output shows the calculations applied. For example, QTD for March 2012 is the sum of January, February and March 2012.
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
"Current", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "Current" ),
"QTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "QTD" ),
"YTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "YTD" ),
"PY", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "PY" ),
"PY QTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "PY QTD" ),
"PY YTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "PY YTD" )
),
DimDate[CalendarYear] IN { 2012, 2013 }
)
Sideways recursion
Some of the calculation items refer to other ones in the same calculation group. This is called “sideways recursion”. For example, YOY% (shown below for easy reference) refers to 2 other calculation items, but they are evaluated separately using different calculate statements. Other types of recursion are not supported (see below).
DIVIDE(
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="YOY"
),
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="PY"
),
)
Single calculation item in filter context
Here is the definition of PY YTD:
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR(DimDate[Date]),
'Time Intelligence'[Time Calculation] = "YTD"
)
The YTD argument to the CALCULATE() function overrides the filter context to reuse the logic already defined in the YTD calculation item. It is not possible to apply both PY and YTD in a single evaluation. Calculation groups are only applied if a single calculation item from the calculation group is in filter context.
This is illustrated by the following query and output.
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
//No time intelligence applied: all calc items in filter context:
"InternetTotalSales", [InternetTotalSales],
//No time intelligence applied: 2 calc items in filter context:
"PY || YTD", CALCULATE ( [InternetTotalSales],
'Time Intelligence'[Time Calculation] = "PY" || 'Time Intelligence'[Time Calculation] = "YTD"
),
//YTD applied: exactly 1 calc item in filter context:
"YTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "YTD" )
),
DimDate[CalendarYear] = 2012
)
A calculation group should be designed so that each calculation item within it presented to the end user only makes sense to be applied one at a time. If there is a business requirement to allow the end user to apply more than one calculation item at a time, multiple calculation groups should be used with different precedence.
Precedence
In the same model as the time-intelligence example above, the following calculation group also exists. It contains average calculations that are independent of traditional time intelligence in that they don’t change the date filter context; they just apply average calculations within it.
In this example, a daily average calculation is defined. It is common in oil-and-gas applications to use calculations such as “barrels of oil per day”. Other common business examples include “store sales average” in the retail industry.
Whilst such calculations are calculated independently of time-intelligence calculations, there may well be a requirement to combine them. For example, the end-user might want to see “YTD barrels of oil per day” to view the daily-oil rate from the beginning of the year to the current date. In this scenario, precedence should be set for calculation items.
| Table | Averages |
| Column | Average Calculation |
| Precedence | 10 |
| Calculation Item | Expression |
|
|
|
|
Here is a DAX query and output.
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
"InternetTotalSales", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "Current",
'Averages'[Average Calculation] = "No Average"
),
"YTD", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "YTD",
'Averages'[Average Calculation] = "No Average"
),
"Daily Average", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "Current",
'Averages'[Average Calculation] = "Daily Average"
),
"YTD Daily Average", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "YTD",
'Averages'[Average Calculation] = "Daily Average"
)
),
DimDate[CalendarYear] = 2012
)
The following table shows how the March 2012 values are calculated.
| Column name | Calculation |
| YTD | Sum of InternetTotalSales for Jan, Feb, Mar 2012= 495,364 + 506,994 + 373,483 |
| Daily Average | InternetTotalSales for Mar 2012 divided by # of days in March= 373,483 / 31 |
| YTD Daily Average | YTD for Mar 2012 divided by # of days in Jan, Feb and Mar= 1,375,841 / (31 + 29 + 31) |
For easy reference, here is the definition of the YTD calculation item. It is applied with Precedence of 20.
CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))
Here is Daily Average. It is applied with Precedence of 10.
DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))
Since the precedence of the Time Intelligence calculation group is higher than the Averages one, it is applied as broadly as possible. The YTD Daily Average calculation applies YTD to both the numerator and the denominator (count of days) of the daily average calculation.
This is equivalent to this calculation:
CALCULATE(DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate)), DATESYTD(DimDate[Date]))
Not this one:
DIVIDE(CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date])), COUNTROWS(DimDate))
New DAX functions
The following new DAX functions have been introduced to work with calculation groups.
| Function name | Description |
|
Returns a reference to the measure currently in context. |
|
Returns a string containing the name of the measure currently in context. |
|
Returns a Boolean indicating whether the measure currently in context is one of those specified as an argument. |
SELECTEMEASURENAME() or ISSELECTEDMEASURE() can be used to conditionally apply calculation items depending on the measure in context. For example, it probably doesn’t make sense to calculate the daily average of a ratio measure.
With ISSELECTEDMEASURE():
IF (
ISSELECTEDMEASURE ( [Expense Ratio 1], [Expense Ratio 2] ),
SELECTEDMEASURE (),
DIVIDE ( SELECTEDMEASURE (), COUNTROWS ( DimDate ) )
)
ISSELECTEDMEASURE() has the advantage of working with formula fix up, so measure-name changes are reflected automatically.
Power BI implicit measures
Calculation groups work with query scope measures, but not inline DAX calculations. This is shown by the following query.
DEFINE
MEASURE FactInternetSales[QueryScope] = SUM ( FactInternetSales[SalesAmount] )
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
//YTD applied successfully to model measure:
"Model Measure", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "YTD"
),
//YTD applied successfully to query scope measure:
"Query Scope", CALCULATE (
[QueryScope],
'Time Intelligence'[Time Calculation] = "YTD"
),
//YTD not applied to inline calculation:
"Inline", CALCULATE (
SUM ( FactInternetSales[SalesAmount] ),
'Time Intelligence'[Time Calculation] = "YTD"
)
),
DimDate[CalendarYear] = 2012
)
Power BI implicit measures are created when the end user drags columns onto visuals to view aggregated values without creating an explicit measure. At time of writing, Power BI generates DAX for implicit measures written as inline DAX calculations. This means implicit measures don’t work with calculation groups. To reserve the right to introduce this at a later date, a new model property visible in TOM has been introduced called DiscourageImplicitMeasures. In the current version, it must be set to true to create calculation groups. When set to true, Power BI Desktop in Live Connect mode disables creation of implicit measures.
DMV support
The following Dynamic Management Views (DMV) have been introduced for calculation groups.
- TMSCHEMA_CALCULATION_GROUPS
- TMSCHEMA_CALCULATION_ITEMS
OLS
Object-level security (OLS) defined on calculation group tables is not supported in the current release. They can be defined on other tables in the same model. If a calculation item refers to an OLS-secured object, it will return a generic error on evaluation. This is the planned behavior for SSAS 2019.
Planned for a forthcoming CTP
We plan to introduce the following items in a forthcoming SQL Server 2019 CTP.
- MDX query support with calculation groups.
- RLS is not supported in CTP 2.3. The planned behavior for SSAS 2019 is that you will be able to define RLS on tables in the same model, but not on calculation groups themselves (directly or indirectly).
- Dynamic format strings. Calculation groups increase the need for dynamic format strings. For example, the YOY% calculation item needs to be displayed as a percentage, while the others should probably inherit the data type of the measure currently in context. We plan to introduce dynamic format strings in an upcoming SQL Server 2019 CTP.
- ALLSELECTED DAX function support with calculation groups.
- Detail rows support with calculation groups.
Limitations of CTP 2.3
CTP 2.3 of SSAS is still an early build of SSAS 2019. It being released for testing and feedback purposes only, and should not be used by customers in production environments. This applies to models with or without calculation groups.
New 1470 Compatibility Level
To use the new features, existing models must be upgraded to the 1470 compatibility level. 1470 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.
Differences between calculation groups in tabular and calculated members in multidimensional
Calculated members in multidimensional are a little more flexible and enable a few scenarios beyond calculation groups, but they come at the cost of added complexity. We feel calculation groups in tabular provide a great deal of the benefits, with significantly less complexity.
Single calculation-item column
Calculation groups can only have a single calculation-item column, whereas multidimensional allows multiple hierarchies with calculated members in a single utility dimension.
A DAX filter on a column value implicitly filters the other columns in the same table to the values of that row. Without introducing new semantics and complexity, multiple calculation-item columns in a single table would filter each other implicitly, so are disallowed. If you have a requirement to apply multiple calculation items at a time, use separate calculation groups and the Precedence property shown above.
Recursion safeguards not required
MDX supports recursion although there are known performance limitations. Quite often the same query results can be achieved using MDX set-based calculations instead of recursion.
The right-hand side of MDX-script cell assignments to calculated members created by the Business Intelligence Wizard for multidimensional include a reference to the real member from the attribute hierarchy. This is required to safeguard against recursion.
Since DAX doesn’t support recursion, so we don’t need to worry about this for calculation groups. The complexity bar is kept lower. If we ever decide to support recursive DAX in the future, we could perhaps introduce an advanced property to indicate that a DAX object is enabled for recursion, and only then require such safeguards to be in place.
Calculation items cannot be created on other column types
Multidimensional allows creation of calculated members on attribute hierarchies that are not part of utility dimensions. For example, a Northwest Region member can be added to the State hierarchy to aggregate Washington, Oregon and Idaho. This is useful for custom-grouping scenarios but can increase the likelihood of solve-order issues.
Calculation items cannot be added to other column types. This keeps semantic definitions simpler. As we enhance calculation groups in the future – for example, if we introduce query-scoped calculation groups – we will take care to learn from the solve-order lessons of the past and strive for consistent behaviors.
Calculation groups are currently engine-only features. SSDT support will come before SQL Server 2019 general availability. In the meantime, you can use the fantastic open-source community tool Tabular Editor to author calculation groups. Alternatively, you can use SSAS programming and scripting interfaces such as TOM and TMSL.
Pace of delivery
We think you will agree the AS engine team has been on a tear lately. This is the same team that recently delivered, or is currently working on, the following breakthrough features for Power BI.
- Arguably the biggest scalability feature in the history of the AS engine: aggregations
- Policy-based incremental refresh
- Opening the XMLA endpoint to bring AS to Power BI
Calculation groups is yet another monumental feature delivered in a relatively short period of time. It demonstrates Microsoft’s continued commitment to enterprise BI customers.
Download Now
To try SQL Server 2019 CTP 2.3, find download instructions on the SQL Server 2019 web page. Enjoy!
What’s new for SQL Server 2019 Analysis Services CTP 2.4
We are excited to announce the public CTP 2.4 of SQL Server 2019 Analysis Services. This public preview includes the following enhancements for Analysis Services tabular models.
- Many-to-many relationships
- Memory settings for resource governance
Many-to-many relationships
Many-to-many (M2M) relationships in CTP 2.4 are based on M2M relationships in Power BI described here. M2M relationships in CTP 2.4 do not work with composite models. They allow relationships between tables where both columns are non-unique. A relationship can be defined between a dimension and fact table at a granularity higher than the key column of the dimension. This avoids having to normalize dimension tables and can improve the user experience because the resulting model has a smaller number of tables with logically grouped columns. For example, if Budget is defined at the Product Category level, it is not necessary to normalize the Product dimension into separate tables; one at the granularity of Product and the other at the granularity of Product Category.
Tooling
Many-to-many relationships are currently engine-only features. SSDT support will come before SQL Server 2019 general availability. In the meantime, you can use the fantastic open-source community tool Tabular Editor to create many-to-many relationships. Alternatively, you can use SSAS programming and scripting interfaces such as TOM and TMSL.
New 1470 Compatibility Level
To use many-to-many relationships, existing models must be upgraded to the 1470 compatibility level. 1470 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.
Memory settings for resource governance
The memory settings described here are already available in Azure Analysis Services. With CTP 2.4, they are now also supported by SQL Server 2019 Analysis Services.
QueryMemoryLimit
The Memory\QueryMemoryLimit property can be used to limit memory spools built by DAX queries submitted to the model.
Changing this property can be useful in controlling expensive queries that result in significant materialization. If the spooled memory for a query hits the limit, the query is cancelled and an error is returned, reducing the impact on other concurrent users of the system. Currently, MDX queries are not affected. It does not account for other general memory allocations used by the query.
The settable value of 1 to 100 is a percentage. Above that, it’s in bytes. The default value of 0 means not specified and no limit is applied.
You can set this property by using the latest version of SQL Server Management Studio (SSMS), in the Server Properties dialog box. See the Server Memory Properties article for more information.
DbpropMsmdRequestMemoryLimit
The DbpropMsmdRequestMemoryLimit XMLA property can be used to override the Memory\QueryMemoryLimit server property value for a connection. The unit of measure is in kilobytes. See the Connection String Properties article for more information.
RowsetSerializationLimit
The OLAP\Query\RowsetSerializationLimit server property limits the number of rows returned in a rowset to clients.
This property, set in the Server Properties dialog box in the latest version of SSMS, applies to both DAX and MDX. It can be used to protect server resources from extensive data export usage. Queries submitted to the server that would exceed the limit are cancelled and an error is returned. The default value is -1, meaning no limit is applied.
Download Now
To get started with SQL Server 2019 CTP 2.4, find download instructions on the SQL Server 2019 web page. Enjoy!
Future Analysis Services posts on Power BI blog
The Analysis Services Team Blog has served as the medium for many historic announcements and updates in Microsoft BI over the past 10 years. Well, Microsoft is moving off MSDN and TechNet blogs, so this will be the last post on this blog site. The site, including all historical posts, will remain online as a read-only archive.
‘So where will new Analysis Services blog posts be published?’ I hear you ask. We have made it clear that Power BI will be a one-stop shop for both enterprise and self-service BI on a single, all-inclusive platform. Power BI will be a superset of Analysis Services, so there is no better place to announce future Analysis Services features than, you guessed it, on the Power BI blog. The Analysis Services engine is at the foundation of Power BI and powers its datasets, so this just feels right.
Please stay tuned to the new Analysis Services category on the Power BI blog for future Azure Analysis Services and SQL Server Analysis Services announcements:
https://powerbi.microsoft.com/blog/category/analysis-services/
Happy New Year from Power BI Desktop!
Happy New Year!
In honor of the new year, our team put together this fun video where we reveal what makes Power BI what it is today! Enjoy!
This month we will have a Power BI Report Server update only, but starting February, Power BI Desktop will continue our monthly ship cadence.
Looking forward to another great year!
On-premises data gateway December 2019 update is now available
We are happy to announce that we have just released the December update for the On-premises data gateway (version 3000.21.18)
- December Version of the mashup engine
- GA of Kerberos based Single Sign-On (SSO) for
- SAP BW Message Server
- Oracle
GA of Kerberos based Single Sign-On (SSO) for SAP BW Message Server and Oracle
You have been able to use Single Sign-On, leveraging Kerberos, when connecting to some data sources including SAP BW Application Server in DirectQuery mode from Power BI. You can now also connect to SAP BW Message server using Single Sign-On leveraging Kerberos.
Once you install the latest gateway, browse to the manage gateways page. Select the intended gateway and click on “Add data source”. When you select SAP BW Message Server data source type, you should see the SSO for Kerberos option(highlighted below) now.
Similarly we have also enabled SSO leveraging Kerberos for Oracle also.
December version of the mashup engine
This month’s Gateway update also includes an updated version of the Mashup Engine, which will match the one released as part of the Power BI Desktop December update This will ensure that the reports that you publish to the Power BI Service and refresh via the Gateway will go through the same query execution logic/runtime as in the latest Power BI Desktop version.
Please continue to send us feedback for what new capabilities you’d like to see in the future.
Power BI and the year 2019 in review
As the end of the year closes I was reminiscing on what a huge year it has been for Power BI. I work mostly with large organisations so my view will be slightly skewed towards that.
For me 2019 has been the year where Power BI got massive adoption as the standard BI platform in an organisation, it went from self serve to also contain corporate BI. These two pictures from one of Arun’s keynote presentations shows it nicely. In 2015 we were all about Data Exploration for self serve analytics.
If you now look at that picture in 2019 we have added so many elements to Power BI to make it truly a complete and Unified enterprise BI platform:
This also lines up nicely with some of the amazing features that shipped this year. Let’s take a look at what I think are some of the most important features we shipped. Yes all of these are from this year ….
- XMLA endpoint for Datasets. This feature is currently in preview. It will allow users of Power BI premium to leverage most of the functionality of Analysis Services directly in Power BI. This brings enterprise level models to Power BI and allow both IT driven models and self service models. Today you can only do Read but soon Write will be enabled too completing the story.
- Calculation groups. This is by far the most important modelling feature from the last few years. It will allow any modeler to create reusable measures and reduce the amount of measures from hundreds to tens. Marco and Albert explain it nicely too.
- BYOK. This might not seem that important for most of us but when you work in the financial sector or law firms this is key. It allows you to encrypt data at rest with your own key from a key vault.
- Workspace v2. The most important Power BI feature if you ask me. This came up in every large enterprise discussion. The new workspace v2 has two major advantages over the previous version. Most importantly it doesn’t rely on Office 365 groups anymore. This means when you create a group in Power BI it doesn’t show in SharePoint or wherever else. Big IT departments argued a lot over this :). Now it just lives in Power BI by default (you can still connect them). The second one is the appearance of a “viewer role” where you can give access to someone in a group and he can only view the reports in the groups (also RLS works).
- AI features in Power BI. There have been a lot of AI features integrated into Power BI this year. It gives the power of AI into the hands of business users. There is no need for them to actually need to understand, build and maintain the actual algorithms. A few examples are the AI visual Distribution Changes analyzes what makes a distribution look different and the Decomposition Tree (my personal favorite :)) enables users to drill into any dimension to understand what is driving a key metric. With a press on the button the AI features behind Power BI can figure out what an interesting data split is. Very cool.
The other important area here is the ability to use your organisations AI models, this has been made so it can be easily consumed by business users. Watch this space! - Shared datasets and certification. Another great feature that allows you to create a dataset, certify it, and share it with the rest of your organisation. These shared datasets are then easily discover able. In Power BI desktop you can create the report and save it to any workspace (does not have to be the same one as the dataset lives in).
- Power BI lineage. Understanding where data comes from has always been a big ask and it is finally here. With this feature you are able to see where the data in your report comes from with a press on the button. There are also API’s available for organisations to retrieve the data for your own usage.
- Data protection. Another big one. With the increase focus on data privacy and leaks any feature that allow an organisation to manage and keep track of their data is being used, without putting it all in a safe, is incredible. The new data protection features allow organisations to label their data, create governance around it and monitor it.
- Tailoring help for Power BI users. This one one was welcomed warmly by the Power BI center of excellence at my customers. It allows organisations to add their own help sites and contact information to Power BI so they can streamline it for their organisation. Saves calls to the organisations IT team :).
- Service mail to tenant admins. Power BI now also provides incident notification so you can optionally receive emails if there’s a service disruption or degradation. While Power BI’s 99.9% service level agreement (SLA) makes these occurrences rare, it is great you now can get an email without having to get notified by your users. Another time saver for the team managing Power BI.
- Bonus: Power BI activity log. I already started this blog post when this huge feature came in. Now you can use Power BI directly to track user and admin activities. Before this feature you had to use the unified Office 365 audit log and get the right access from the Office team (which was hard). Now you can retrieve the data, as a Power BI admin, through PowerShell and store it wherever you want. Then use that to gain the insights you want and keep the data as long as you want.
- Double bonus: Azure Synapse Analytics. One of the most interesting releases of the last year has to be Azure Synapse Analytics. Of course it is still early day but the signs are very good and it gets received with enthusiasm. I also see great collaboration between the Power BI and Azure Synapse team to make the combination work smoothly. In my daily work I see more and more organisations who are moving their data to Azure. Once the data is there they want to be able to use it and gain insights out of it. That is what Azure Synapse promises. I would recommend anyone following Power BI to keep an eye out here.
Now for the new year I expect more of a continuation of this and some nice surprises as well :). It will be another great year for Power BI!
Introducing the Power BI Activity Log
We are excited to announce the availability of a new Power BI API called the activity log, which enables Power BI service admins to track user and admin activities within a Power BI tenant. It is comparable to the unified Office 365 audit log in the sense that both logs contain a complete copy of the Power BI auditing data, but there are also several key differences that are particularly important for Power BI service admins, as the following table reveals.
| Unified Office 365 audit log | Power BI activity log |
| Includes events from SharePoint Online, Exchange Online, Dynamics 365, and other services in addition to the Power BI auditing events. | Only includes the Power BI auditing events. |
| Only users with View-Only Audit Logs or Audit Logs permissions have access, such as global admins and auditors. | Global admins and Power BI service admins have access. |
| Global admins and auditors can search the unified audit log by using Office 365 Security & Compliance Center, the Microsoft 365 Security Center, and the Microsoft 365 Compliance Center. | There’s no user interface to search the activity log yet. |
| Global admins and auditors can download audit log entries by using Office 365 Management APIs and cmdlets. | Global admins and Power BI service admins can download activity log entries by using a Power BI REST API and management cmdlet. |
To distinguish the Power BI-specific log from the unified audit log, Power BI chose the name activity log, but the Power BI auditing data within both logs is identical. In this way, global admins and auditors can continue to use the Security and Compliance Centers for all their auditing needs, while Power BI service admins now have a straightforward way to access and download the data they need to monitor the actions of their users and administrators in their Power BI tenant. You could call the activity log an audit log as well, but different names help to avoid confusion.
So, what can you do with the Power BI activity log? For starters, you can write an administrative application based on the Power BI REST APIs to export the activity events into a blob store or SQL database so that you can build a custom usage report on top of the exported data. In the ActivityEvents REST API call, you must specify a start date and end date and optionally a filter to select activities by activity type or user id.
Because the activity log may possibly contain a large amount of data, the ActivityEvents API currently only supports downloading up to one day of data per request. In other words, the start date and end date must specify the same day, as in the following example. Make sure you specify the DateTime values in ISO 8601 compliant UTC formats.
https://api.powerbi.com/v1.0/myorg/admin/activityevents?startDateTime='2019-08-31T00:00:00'&endDateTime='2019-08-31T23:59:59'
If the number of entries is large, the ActivityEvents API returns only around 5,000 to 10,000 entries and a continuation token. You must then call the ActivityEvents API again with the continuation token to obtain the next batch of entries, and so forth, until you have retrieved all entries and no longer receive a continuation token. The following example shows how to use the continuation token.
https://api.powerbi.com/v1.0/myorg/admin/activityevents?continuationToken=’%2BRID%3ARthsAIwfWGcVAAAAAAAAAA%3D%3D%23RT%3A4%23TRC%3A20%23FPC%3AARUAAAAAAAAAFwAAAAAAAAA%3D’
As a general rule, regardless of the number of entries returned, if the results include a continuation token, make sure you call the API again with that token to retrieve the remaining data, until a continuation token is no longer returned. It can happen that a call even returns a continuation token without any event entries. But don’t let the absence of activity events distract you. Just keep calling back with that continuation token. Here is how you can code against the continuation token that is returned in the response:
while(response.ContinuationToken != null)
{
// Store the activity event results in a list for example
completeListOfActivityEvents.AddRange(response.ActivityEventEntities);
// Make another call to the API with continuation token
response = GetPowerBIActivityEvents(response.ContinuationToken)
}
completeListOfActivityEvents.AddRange(response.ActivityEventEntities);
It might be easier to download activity events by using the Power BI Management cmdlets for PowerShell, which include a Get-PowerBIActivityEvent cmdlet that handles the continuation token for you automatically. The Get-PowerBIActivityEvent cmdlet takes a StartDateTime and an EndDateTime parameter with the same restrictions as the ActivityEvents REST API, meaning the start date and end date must reference the same date value because you can only retrieve the activity data for one day at a time.
The following screenshot illustrates how to download all Power BI activities in a sample tenant for December 1st. As the results indicate, the script downloaded almost 19,000 entries, which the command then converted from JSON into .NET objects for straightforward access to individual activity properties. The script could now process each object individually in a foreach loop.
As mentioned, you can filter the activity events by activity type and user id. The following screenshot demonstrates how to download only the events data for the ViewDashboard activity. For additional information about supported parameters, use the command Get-Help Get-PowerBIActivityEvents.
And that’s it for a quick introduction of the Power BI activity log. If you are a Power BI service admin, we hope this feature gives you more visibility across your Power BI tenant and more flexibility to build admin solutions that help increase your productivity. Future improvements might include a user interface for searching the activity log in the Power BI Admin Portal with the capability to download the results into a .csv file as well as additional filtering capabilities in the ActivityEvents REST API and Get-PowerBIActivityEvent cmdlet. So, stay tuned for even more capabilities empowering Power BI service administrators.
Power BI Mobile “new look” is now GA and includes your activity feed
Three months ago, we introduced the “new look” for Power BI Mobile as a preview and asked for your feedback. And your feedback was awesome! You told us that you love the new look and find it easier for finding content and navigating in the app.
Today, the new look for Power BI Mobile is generally available (GA) and becomes the default look for all users. You no longer need to turn it on in the app settings (and, you also can’t turn it off). Now when you open Power BI mobile app, you’ll land on the new home page, where you can easily find and access the most recent and frequent items you usually use in Power BI.
Introducing the new activity feed
We’ve also completed the activity feed, which is part of this release. On your home page, tap the activity tab to see your feed of notifications and updates about your data. This feed helps you stay up to date with all the activity and updates for your favorite, recent, and frequent items.
- See when report and dashboard data is refreshed: We often heard feedback that it wasn’t easy enough to see when the data shown in a report or a dashboard was last updated . In the activity feed, you can see right off when an item was last refreshed, so you’ll know to open that report or dashboard to check out the new data.
- Join a conversation: In your feed, you’ll also see when there are active conversations and comments related to you or to your reports and dashboards. Want to join the conversation and add your input? Just tap on the item in the feed.
- View data alerts: We’ve also added to the feed notifications created from the data alerts you configured. Now, you’ll know right away when your KPI reaches its threshold and you can and open the KPI directly from the feed for more details.
- Checkup app updates: You’ll also see on the feed when a creator publishes new content into an app you’re using, and you can easily open that app from the feed and see what’s new.
Notice that we aggregate items, so all data updates coming from the same app or workspace will be grouped together. Use the arrow to expand and see more items. the most recent items will always be at the top of the list. will always show the most recent items.
Additional improvements with the new look
We’ve added all your connected accounts to the side-panel. Now it’s much easier to switch between your Power BI service account and your Report Server accounts more easily. Just tap on your image or avatar to open the side-panel and manage your accounts.
We hope you’ll continue to enjoy the Power BI mobile app and its new look!
Please continue sending us your feedback and don’t forget to vote for other features you’d like to see in the Power BI Mobile app.
If you still haven’t installed the Power BI Mobile app on your mobile device ,get it today from App store, Google Play and Windows store.