Posts

Power BI Desktop Supports Direct Connectivity to Tabular

/0 Comments/in /by

As you’ve probably heard the news, Power BI went live yesterday. Among the many new features, Power BI Desktop (previously known as Power BI Designer) now supports live connections to Tabular. This is conceptually similar to connecting Excel to Tabular or Power View to Tabular to create ad hoc reports. However, Power BI Desktop is a standalone and freely available tool that is independent of Office or SharePoint.

When connected directly to Tabular, Power BI Desktop generates DAX queries as a result of report changes, sends them to Tabular and displays the results. Of course, we know we have new visualizations that don’t exist in Excel and on-prem Power View, such as tree maps, region maps, etc.

Upon discovering connectivity to Tabular, Power BI Desktop asks you how you want to connect:

Once you create the report, you can publish the dataset and report to Power BI. As a prerequisite, you need to install and configure the Analysis Services Connector to that the report can connect to Tabular on premises. If the connector is not installed, you will get an error on publishing. Speaking of the connector, at this time it cannot be installed side by side with the Personal Gateway which is used for refreshing data from on-prem data sources. You need to install them on separate machines.

Tabular is the first data source that supports direct connectivity in Power BI Desktop. I’d imagine that live connections to other data sources will follow soon. Direct connectivity is useful to support fast databases where there is no need to import and cache the data. In this case, you’ll need to weight out the pros and cons of direct connectivity vs. data import.

Power BI Custom Visuals Announced

/0 Comments/in /by

Today Microsoft announced the general availability of Power BI on July 24th 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.
3833.custommap.jpg-400x0I 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

/0 Comments/in /by

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

/0 Comments/in /by

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

/0 Comments/in /by

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.

042315_2143_PlottingGoa1

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.

051315_1219_PlottingGoa1

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.

042315_2143_PlottingGoa2

Power BI vNext SSAS Connector and Security Reloaded

/0 Comments/in /by

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:

  1. Remote in to your SSAS server.
  2. Open Command Prompt and enter:
    Whoami/upn
  3. 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

/0 Comments/in /by

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

/0 Comments/in /by

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.

010415_1902_TabularM2MR1

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.

010415_1902_TabularM2MR2

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

010415_1902_TabularM2MR3

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

/0 Comments/in /by

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:

  1. It’s just an URL-based mechanism targeting deployed reports and its customization options are limited to layout adjustments.
  2. 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.
  3. 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

/0 Comments/in /by

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.