Ten Power BI Ideas

The following post describes ten ideas that I believe would benefit Power BI in addressing common and impactful scenarios, particularly from an enterprise BI and IT administration perspective.

Each section contains a link to the corresponding idea on the Power BI Ideas forum and thus you can vote for any idea you support to increase its probability of eventually being implemented or accounted for in some other way.

Workspace Deployment Wizard

An app workspace provides one testing area such that that reports and dashboards can be reviewed internally prior to publishing all or some of these artifacts as an app for groups of users to consume.

However, development teams will often require separate workspaces (e.g. Dev and Prod), with each workspace potentially being assigned to a different premium capacity (or shared capacity). Additionally, the source datasets for these reports and dashboards may be different across the two workspaces. For example the development dataset may contain much less historical data and possibly measures that haven’t been validated yet for production use. In this scenario, the reports and dashboard tiles will be binded to the production dataset upon deployment.

Moreover, the development teams require an incremental deployment such that they can choose which reports remain in the development workspace and which reports get deployed to the production workspace similar to the Included in App option within workspaces. The point in sharing these details is that there are many variations of a staged deployment of Power BI beyond simply publishing an app or duplicating a workspace.

In the current state, PowerShell scripts leveraging the Power BI Management module can be developed to meet various needs. However, even though these scripts and commands such as Copy-PowerBIReport and Copy-PowerBITile can be relatively simple to to write for seasoned Powershell and IT admins, this can be a serious roadblock for BI teams who might be new to PowerShell. A BI team could understandably say something to the effect “We already had to learn DAX and some Power Query (M), do we also have to learn PowerShell?” Additionally, the IT admin or PowerShell pro is often simply not available to help the BI team write the script they need to meet their deployment scenario.

The Power BI Deployment Wizard would provide a graphical interface within an app workspace to define all the required variables of the deployment including target workspace, target dataset (or copy dataset), which reports and dashboards to deploy, etc.

Additionally, the deployment wizard would optionally export out a PowerShell script reflecting the defined deployment. Thus, if the scenario reflects a common pattern or standard the team wishes to follow, the script could be reused across other deployments. (Link to vote for this idea)

Report Page AutoFormat

Report authors spend too much time making many small changes to various visuals and elements within their reports. Simple but necessary formatting modifications such as aligning and distributing the visuals, applying common/consistent formats, and re-sizing and re-positioning visuals can be tedious and time consuming.

The Report Page AutoFormat option would be a new icon on the Home tab in Power BI Desktop that would analyze the current layout of the page and make smart guesses in applying a more consistent and overall better layout. In later releases, a dropdown would be added for applying richer autoformat layouts representing alternative and better or best visualization practices. (Link to vote for this idea)

Report Theme Designer

Building a robust report theme file (.JSON) for reuse across many reports should be much easier than it is today. A report author/designer already very familiar with the various formatting options in Power BI Desktop should be able to leverage this knowledge by exporting out a JSON file representing the formats set in the Report Theme Designer pane.

The Report Theme Designer pane would be accessible from the View tab just like the other panes (Field Properties, Bookmarks, Selection, Sync Slicers) and would allow the theme designer to easily choose the colors. font families, text sizes, etc at a granular level. Once complete, the author could export the new report theme (.JSON file) and thus import this theme to other Power BI reports just like any other report theme file. (Link to vote for this idea)

Composite Storage Models

We now have composite models in Power BI, and while this, along with aggregations, is certainly a leap forward that enables many new scenarios and avoids painful tradeoffs between Import and DirectQuery only models, composite storage models may provide a simpler and more cost and resource efficient solution.

This feature would split the storage of an imported table between in-memory (cache) and disk. The dataset designer would be able to set a policy defining the rows (partitions) of the fact table that would be stored in memory similar to an incremental refresh policy. For example, the model could always store the last two years of data in memory but data older than two years would be stored on disk. (Of course most report queries only access the recent data so this is where the most expensive and performant memory resources should be allocated).

The data stored on disk could still be compressed and in columnar format like the clustered columnstore index in SQL database technologies such that the relatively fewer queries against this data could still deliver acceptable performance. Additionally, there could be a licensing option for organizations to purchase fast NVMe Solid State Disks as part of their premium capacity nodes to supplement the vCPUs and RAM of these nodes and these fast drives could be applied for this feature.

This feature would account for scenarios in which any queries against the source system would result in poor performance and it would avoid the additional complexity of models with aggregated tables and their relationships. (Link to vote for this idea)

Capacity level Administration Settings

The team administering Power BI may want to apply a distinct set of administrative settings for a premium capacity which add to or even override the settings applied at the tenant level. For example, an organization may allow custom visuals to be used in shared capacity workspaces but may want to restrict or disable custom visuals from being used on a premium capacity.

As another example, an organization may have one premium capacity node which contains sensitive or protected information and thus wants to disable all of the Export and Sharing options for this capacity such as Export data, Print dashboards and reports, Share content with external users, etc. However, these more restrictive policies should only apply to the workspaces on this one premium capacity node – the organization actually wants to use these features for other content.

In the current state you can only configure these tenant level settings for users and security groups. So if you allow security group A to use a feature, the users in security group A can use that feature across the workspaces in the tenant.

The capacity settings page in the Power BI admin portal should be enhanced to A) allow admins to see the default settings inherited from the tenant level and B) apply settings that are specific to the given premium capacity node. Only Power BI service administrators should be view and edit these capacity-specific settings, not capacity administrators.

In a future release Power BI admins should have a simple summary page in the admin portal that summarizes tenant and capacity settings applied and thus advises of any custom settings applied at the capacity level. This view and feature as a whole would be particularly valuable for larger and more complex and varied deployments involving many premium capacity resources and many distinct teams and applications. (Link to vote for this idea)

Dataset Creator Tenant Setting

A Create workspaces setting exists in the Power BI Admin portal for defining which security groups of users are allowed to create the new app workspaces. While this setting alone is helpful, there should also be a tenant level setting for restricting which users are able to publish or configure a dataset.

As you surely know, a dataset is technically an Analysis Services model which serves as the source for one or many reports in Power BI. Creating an effective and sustainable dataset involves some level of learning and many problems can arise when reports are built against a dataset with various sub-optimal design choices or the anti-patterns I’ve written about previously. For this reason, organizations may want to define a small group of users per team who will learn the essentials of data modeling, DAX, maybe Power Query, and other technical skills and only then be allowed to create datasets.

If a user is not mapped to a security group assigned the permission to create a dataset, the user will receive an error when publishing the dataset or trying to take over or configure a dataset. The user will of course not be blocked when trying to publish a report based on a live connection to a dataset the user has permission to. (Link to vote for this idea)

Pinning Datasets to Memory

In the current state, the Power BI service will evict a dataset from memory if it needs to free up memory for other datasets and if the dataset is relatively idle (not being queried or refreshed). Teams should be able to exclude certain datasets from this behavior to ensure these datasets will always be in memory and thus refreshes and queries will not encounter delays due to loading the dataset back into memory.

For example, a P1 capacity node with 25 GBs of RAM would automatically grant a variable amount of this memory to any datasets marked as ‘priority datasets’ in the capacity settings page. Any other dataset in the capacity would have the remaining memory to work with – the existing LRU (least recently used) methodology for determining dataset eviction would apply to these datasets.

This feature would address scenarios in which a dataset is very important even though it serves a small audience (CEO, Senior Execs) and thus doesn’t receive a high volume of queries. Maybe in more common examples, a team may have a few datasets that they know are the certified datasets supporting production workloads and thus want to ensure these datasets are not evicted. (Link to vote for this idea)

Workspace Contact List Event Notifications

One of the features of the new workspaces is the ability to define a contact list to receive notifications of events occurring in the workspace. This feature should be enhanced to allow workspace admins to select from a menu of events including dataset refresh failures, significant increases in query volume, requests for access to artifacts in the workspace, new datasets added to the workspace, and more. Ideally the admin would be able to associate different security groups or users with different event notifications.

At a minimum, dataset refresh failures should be supported for group email notification. Until this is the case, teams have few other choices besides the custom admin solution I described earlier this year involving ongoing retrieval of dataset refresh history into a Power BI dataset and tying alerts on dashboard tiles to MS Flows and custom group emails. (Link to vote for this idea)

Dataset Perspectives

Perspectives are one of the most valuable features for large Analysis Services models as they expose only the relevant tables and measures to a given user or team. In fact, perspectives are one of the few features exclusive to Standard tier Azure Analysis Services servers – it’s not available to the Basic tier.

The new modeling view in Power BI Desktop supports multiple layouts and many features (multi-select) that make designing large models easier. Perspectives should be added to the new modeling view with a ‘Manage Perspectives’ icon next to the Manage Relationships and Manage Roles icons on the Modeling tab.

In an initial release, the Manage Perspectives dialog should provide the same features as what’s available to Analysis Services projects in Visual Studio. For instance, there should be a table with each column representing a distinct perspective and the modeler can quickly choose which tables and individual measures and columns are included or not in a given perspective. Additionally, also like Analysis Services, the modeler should be able to view/evaluate and toggle between the different perspectives to view the user experience. (Link to vote for this idea)

Power BI Dataset Design App

In the future there should be a dedicated app for designing datasets exclusive of Power BI Desktop. Power BI Desktop’s existing modeling capabilities should be retained but all of this modeling functionality and future modeling investments should go to the new app. (This is similar to what happened with Power BI add-ins for Excel).

As mentioned in the Dataset Creator Tenant Setting idea and many times before, a dataset creator and the dataset creation process is just a very different person and experience than report authoring. There are complexities and limitations with trying to embed enterprise BI modeling features into Power BI Desktop without disturbing the existing experience that serves many report authors.

The new dataset design app would be capable of targeting Azure Analysis Services servers and would contain the very best modeling features from existing designer experiences as well as application lifecycle management (ALM).

The app would enable/disable features based on the target server (PBI or AS? Premium or Shared?) and would expose a new data science and advanced analytics features. Self-Service BI datasets and small scale datasets could still optionally use Power BI Desktop but BI teams and larger scenarios would quickly adopt the new, single BI Semantic modeling tool for Power BI and Analysis Services. (Link to vote for this idea)

Wrapping Up

I hope you found something in this post (or maybe past posts) useful or interesting. It’s been quite a while since I posted anything that didn’t have any code or technical examples included but sometimes it’s healthy to step back and think more broadly. As always feel welcome to share any idea in the comments and click ‘Follow’ if you’d like to subscribe.

This may be my last post for a little while but if you’ll be near Los Angeles I’ll be speaking at the SQL Saturday there on June 15th regarding Power BI administration.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s