Power BI – Page 25

Power BI Custom Visuals Announced

July 10, 2015/0 Comments/in Blog/by Prologika - Teo Lachev

Today Microsoft announced the general availability of Power BI on July 24^th meaning that Power BI goes live on that date. Among the many new features mentioned in the blog, the one that piqued my interest is custom visuals. Developers might recall that SSRS has provided custom report items since version 2005, which I wrote about in the October 2006 issue of the MSDN Magazine. Similarly, now you can implement custom visualizations in Power BI! And, instead of writing .NET code, you can do so using JavaScript. This opens the path to implement custom visuals using any of the JavaScript-based visualization frameworks, such as D3, WebGL, Canvas, or SVG.

This will be huge as it will allow developers to extend the Power BI visualization capabilities using open-source visualization frameworks. Do you need some gadgets, like a bullet graph? With some coding wizardry, you can implement your own, such as by using the D3 Bullet Charts sample. Moreover, to speed up development, Microsoft has published the code of all of the existing Power BI visualizations on GitHub. Once you follow the instructions to install the Power BI Visuals Sample, you can open the project in Visual Studio and open in the browser index.html located in the PowerBIVisualsPlayground project. This allow you to test the visuals. The actual TypeScript code of the Microsoft visualizations is located in the Visuals project.
I think the Microsoft decision to make Power BI open will be a paradigm shift for the BI industry. Besides the Push API which are already published, Microsoft has already indicated plans to make Power BI even more open, such as to allow developers to embed reports in custom web applications. Now, if only Power BI supports on-prem installation …

Cool Visualizations Coming To You This Summer

June 12, 2015/0 Comments/in Blog/by Prologika - Teo Lachev

I welcome every news about better visualizations in Microsoft BI so I rejoiced to read this blog by Ariel Netz, Group Program Manager in the Power BI Designer team.

“The Power BI Designer team is working on our first update for the summer. That update will be significant, and will signal a new phase in our product development. To date we have mostly invested in the modeling and query capabilities, but starting with the first summer release users will see heavy investments in the Data Exploration & Reporting capabilities in the product. While it might be bit premature to go into detail, it’s not premature to provide a teaser… ”

And the teaser screenshot in the blog shows more visualization gadgets with the ability to change chart series colors. I hope they thrown in conditional formatting as well.

Confused which visualization tool to use? You probably are with so many vendors and tools out there. But as I said many times, your focus should be on data integration and not on the front end. If you have a solid architecture and trusted data, it should be easy to plug in whatever visualization tool is the course de jour. Yesterday it might have been QlikView, today Tableau might enjoy the spotlight, but tomorrow it might very well be the Power BI Designer.

May you leave in interesting times – Chinese proverb.

Power BI 2.0 User Provisioning

May 15, 2015/0 Comments/in Blog/by Prologika - Teo Lachev

As a continuation to my “Power BI vNext (let’s now call it Power BI 2.0 to align terminology with Microsoft) SSAS Connector and Security” blog, you might wonder how Power BI provisions users. For example, if a user signs with his business e-mail and a coworker shares BI artifacts with him, what happens when the user leaves the company? Can he still gain access?

As it turns out, when the user signs to Power BI, Microsoft adds the user transparently to the Azure AD (AAD). Syncing AD with AAD is not a requirement. This is why you don’t need to extend your AD to Azure or synchronize it when you want Power BI reports to connect to on-prem Tabular models. If you do not sync your AD with Azure AD and remove user from AD, they continue to exist in Azure AD. If the tenant is a managed tenant (i.e. there is a tenant admin), tenant admin can disable the user in O365 when the user leaves the company. However, if this is an unmanaged tenant (i.e. no admin yet), the company administrator needs to “Take Over” the tenant, as described here. To make this easier, you can do DirSync which will do this automatically or extend your AD to Azure.

Plotting Goal in Power BI

April 23, 2015/0 Comments/in Blog/by Prologika - Teo Lachev

A customer wanted a bar chart where each bar shows the invoiced and realized sales and a marker to show a goal, as sketched below (notice the Budget marker). The chart had to render the data from SSAS.

In SSRS, this of course can be done using a bullet gauge, which I hope one day will make its way to Excel and Power View. Because Excel PivotChart doesn’t support XY Scatter charts when connected to SSAS, the compromise is to use a column chart, such as the one below, where the goal was plotted as a line chart with the line hidden and only markers visible.

The closest Power View compromise would be the chart below. This requires the latest Power View build which is only available in Power BI 2.0. In Power BI, Power View supports a Combo Chart Stacked variation.

Power BI vNext SSAS Connector and Security Reloaded

March 7, 2015/0 Comments/in Blog/by Prologika - Teo Lachev

To follow up on my previous post on the same subject, a customer was eager to jump on the Power BI->On Prem SSAS bandwagon and try the simplified security model. But “omne initium difficile est” (every beginning is difficult). Their SSAS server was installed on a domain acme.com while their e-mail addresses were using a different scheme, e.g. user@contoso.com, although both server and accounts were under the same acme domain. As we’ve quickly found out, Active Directory had an issue with this setup which manifested with the following error in the SQL Server Profiler connected to SSAS.

“The following system error occurred: The name provided is not a properly formed account name.”

If you see this error, follow these steps to confirm the issue:

Remote in to your SSAS server.
Open Command Prompt and enter:
Whoami/upn
If you see that your login domain name is different that the e-mail you use to sign up to Power BI, you’ll have the same issue. For example, your e-mail address might be user@contoso.com while whoami might show user@acme.com.

The workaround is to bridge the two identity schemes by adding an alternative UPN suffix in Active Directory Users and Trusts, as Greg Galloway explained here. This of course will require help from your AD administrator (one of the most important persons to befriend on your BI quest) so some coercing is to be expected.

Thanks to Jen Underwood, Will Thompson, Mini Nair and Giri Nair from Microsoft who helped us troubleshoot the issue.

Power BI vNext SSAS Connector and Security

March 3, 2015/0 Comments/in Blog/by Prologika - Teo Lachev

As you’ve probably heard, Power BI vNext will allow you to keep your data on premises in SSAS MD and Tabular data models while your reports in the cloud can connect to these data models on premises. Currently, the Analysis Services connector support only Tabular models while plans for MD have been announced as well. Currently, SSAS understand Windows security only and you might wonder what needs to be put in place for security to work.

Interestingly, if your users have used their work e-mail addresses to sign up for Power BI and your company is using Active Directory, you don’t need do synchronize your AD with Azure using DirSync. That’s because Power BI will pass the user identity on the connection string using the EffectiveUserName property. Consequently, connectivity and data security will work as usual. On the other hand, if users used an .onmicrosoft.com e-mail address, DirSync is required. Microsoft explains this in more details in the “Why you might need use DirSync to connect to an on-premises Analysis Services Server” document.

While this definitely simplifies the effort to get Windows security working, it doesn’t help if custom security, such as Forms Authentication, is required, such as to integrate Power BI with custom applications. Embedding Power BI reports on custom applications and supporting custom security are on the Power BI top wish list and I hope Microsoft will support this scenario soon as well.

Tabular M2M Relationships on the Horizon

January 4, 2015/0 Comments/in Blog/by Prologika - Teo Lachev

One of the biggest strengths of Microsoft self-service BI is the ability to create sophisticated data models on a par with organizational BI models built by professionals. This fact is often overlooked when organizations evaluate self-service tools and the decision is often made based on other factors but not insightful understanding of the data model capabilities. This is unfortunate because most popular tools on the market don’t go much further than supporting a single dataset. By contrast, Power Pivot allows you to import easily multiple datasets from virtually anywhere and join the resulting tables as you can do in Microsoft Access. This brings tremendous flexibility and analytical power.

Unlike multidimensional cubes, one of the limitations of the Power Pivot and Tabular data models has been the lack of support for declarative many-to-many relationships. The workaround has been using a simple DAX formula to resolve the relationship over a bridge table, such as =CALCULATE (SUM (Table[Column] ), <BridgeTable>) but this approach might present maintenance issues, as you have to create multiple calculated measures to support different slicing and dicing needs. However, as pointed out in my latest newsletter, the upcoming version of Power BI aims to remove adoption barriers and adds new features. And, one of this features, is bidirectional relationships and declarative support of M2M relationship, which Chris Webb already wrote about.

To test the M2M relationship, I attempted to create the same M2M scenario that I used in my book, which models a joint bank account. The corresponding Power Pivot schema is shown below. The CustomerAccount table is the bridge table that resolves the M2M relationship (a customer might have many accounts and a bank account might be shared by multiple customers). The Balances table stores the account balances over time and the Date table lets us analyze these balances over time.

Setting up a M2M relationship in the Power BI Designer is achieved by changing the “Cross filter direction” relationship setting to Both. This setting and bi-directional relationships are described in more details here.

Indeed, creating a report that shows balances by customer resolves the M2M relationship and aggregates correctly.

Unfortunately, attempting to slice the report by Date returns an error in the preview version of the Power BI Designer so the M2M feature is still a work in progress. Brining this further, a useful addition could be declarative semi-additive functions to allow the user to set the aggregation behavior of the Balance measure, such as to LastNonEmpty. Similar to Multidimensional, this will avoid the need for user-defined explicit measures.

Embedded Power View and Pivot Reports

December 13, 2014/0 Comments/in Blog/by Prologika - Teo Lachev

I’ve been pestering Microsoft for years to provide an embedded Analysis Services Viewer control (similar to the SSRS ReportViewer) that would allow developers to embed interactive reports on custom Windows Forms and web applications. And, for years nothing happened, even after Microsoft acquired the Dundas OLAP Chart control in 2008. There are some positive signs on that end lately. Microsoft just rolled out the ability to embed Power View and pivot reports on a webpage or blog. I’m sure there are some scenarios that will be benefit from this feature but this is really not what I want because:

It’s just an URL-based mechanism targeting deployed reports and its customization options are limited to layout adjustments.
It’s not a control that developers can customize, such as to change the connection string in order to pass custom user credentials, replace parameters, etc.
It requires the reports to be hosted in Office 365. Hence, at least for now, this feature can’t be used with on-prem data.

The Office Click-To-Run Setup

February 18, 2014/0 Comments/in Blog/by Prologika - Teo Lachev

As you’ve probably head, Office 2013 supports now two installation options: the traditional MSI-based installation and the new Click-To-Run streaming installation. Chris Webb mentioned about it here and Melissa Coates describes how it works in more details here. The MSI setup is a perpetual one (you pay for a version once and you’re entitled to fixes for it) while the C2R setup is a subscription-based Office 365 setup (you continuously pay for using the software and you’re entitled to fixes and the latest features within the SKU you’re subscribed to). Perpetual installations will get updates (cumulative updates and service packs) just like they’re used to but they are meant primarily to be fixes rather than features. On the other hand, Office 365 subscribers have the benefit of getting fixes and new features as long as their subscription is active. Currently, there is no way to switch your existing Office installation from MSI to subscriber-based or vice versa. You must uninstall Office 2013 and reinstall. Once you do this, you’ll find that there is no difference as far as user experience. C2R still installs Office on the desktop although in different location.

The C2R setup has important ramifications on self-service BI. C2R users will have an always up-to-date service allowing Microsoft to add new functionality to the Office applications at a much faster rate. We’ve already seen this with the synonyms feature that are used for natural queries, aka Q&A (Q&A requires Power BI). Although I’ve initially dismissed the streaming installation, the C2R option now seems very attractive. I’ve already have an Office 365 subscription for e-mail and SharePoint. Shelving a few more bucks to upgrade to Power BI and stay on the latest and greatest seems like a good value proposition because I don’t have to wait for Office.NEXT to get the latest and greatest. More information about Office 365 plans can be found here. As an extension to Office 365, Power BI charges a premium as explained here.

A roadmap of C2R planned features is currently in the works so customers know the details of what’s coming up and when.

Power BI (Part 2 – The Data Management Gateway)

September 10, 2013/0 Comments/in Blog/by Prologika - Teo Lachev

In my previous blog on the Power BI subject, I’ve introduced you to Power BI. In this blog, we’ll take a look at the Data Management Gateway and how to set up data sources to refresh on-premise data. I’ll be quick to point out that the actual data refresh is not in the preview yet.

The Data Management Gateway is implemented as a Windows service which you can download and install on premises. It has two main features:

Enable cloud access for on-premises data sources within your organization
Expose data from on-premises data sources as OData feeds that can be consumed by using Power Query.

You configure the gateway and data sources from the Admin->Power BI section of the Office 365 portal. This starts a simple wizard that walks you through the steps to install, configure, and test the data management gateway. You can set up multiple gateways if needed, such as to bring data from two geographically separated servers.

Once the gateway is installed, you can configure one or more data sources that point to on-premise data, such as a relational database. In the Power BI context, a data source corresponds to a data connection, similar to the ones you set up in Power Pivot. In the configuration process, you specify a data source name, the gateway it’s associated to, and connectivity details. For now, the only connectivity option is to connect to SQL Server but Microsoft is extending the connectivity options with the goal to support all data sources that Power Pivot supports.

The Data Source Usage tab allows you to configure how the data source will be used. If the Enable Cloud Access option is checked, users can refresh Power Pivot workbooks with on-premise data. If Enable OData Feed is checked, the data source will be exposed as an OData feed in Power Query. Thus, the gateway functionality that allows you to expose SQL tables/views as OData endpoints could be used in non-Power BI ways. For example, you could access the OData feed from an Azure service (provided that your service/code supports Org ID authentication).For more information about the latter option is useful and how it can be used when available in the self-service/team BI context, read Access OData Feeds from Power Query Add-in for Excel.

A few more notes:

Currently, The Data Management Gateway is specific to Power BI. You can’t use it to upload data to the cloud for other purposes, such as a replacement for FTP. However, I won’t be surprised if Microsoft would eventually turn it into an Azure platform service.
As far as performance, assuming there are no bottlenecks on the data access side (i.e. running complex queries), in most cases the gateway performance will be a factor of your Internet speed between your corporate network and the Azure data center. To improve performance, the gateway compresses, chunks, and parallelizes the upload of the data. Microsoft will provide more details of how it all works at the PASS Summit (Power BI and the Data Management Gateway session).

For more information about the Power BI data management experience, read Data Management Experience in Power BI for Office 365 Help.

Posts