Posts

Power BI Embedded

/0 Comments/in /by

Embedding reports is an extremely popular scenario for ISVs and developers coding external (customer-facing) applications. As I wrote a while back in my “Power BI Embedded Dashboards Without Authentication UI” blog, Power BI supports REST APIs that allow developers to embed dashboards and reports. However, these APIs don’t support custom security so you have to provision users with Power BI. Furthermore, a hybrid architecture (reports definitions in the cloud and data on premises) requires Power BI Pro license for each user. This pricing model could quickly become overly expensive if you have to onboard hundreds of users.

Power BI Embedded, available for preview on April 1st, aims to remove these obstacles. Designed as an Azure service, it doesn’t require changes to the application security. For example, if your application uses Forms Authentication, users can still continue logging in using a user name and password. The application then calls the Azure APIs to obtain an authorization token that is passed onto Power BI. Once the user is authenticated, the app uses the Power BI REST APIs to embed Power BI content. The other benefit from the Azure integration that the application developer no longer have to work with OAuth API to handle security, as explained in more details here. Power BI Embedded also introduces a new licensing model, where you’re priced per the number of dashboard and reports views that your users render instead of by user. Notice that the licensing terms state that “you may use the Power BI Embedded service within an application you develop only if your application (1) adds primary and significant functionality to our [Power BI] service and is not primarily a substitute for any Power BI service, and (2) is provided solely for external users. You may not use the Power BI Embedded service within internal business applications”.

On the downside, the preview doesn’t support refreshing imported Power BI Desktop models. As far as direct connectivity, the preview is currently limited to Microsoft Azure data sources that support basic security (Azure SQL, Azure SQL DW, and HD Insight Spark). So, no support for SSAS yet as SSAS is not available (yet) as PaaS. This limitation also prevents implementing multi-tenant solutions (a must for most ISVs), where the user is authorized to see only a subset of data. Microsoft has provided a sample ASP.NET MVC app and excellent step-by-step documentation to help you get started. Below is a snapshot of the app, which I customized to display embedded custom reports that are demonstrated in the Prologika Power BI showcase.

Power BI Embedded is the missing piece that many ISVs need to integrate interactive Power BI reports and dashboards in their offerings. Although still lacking in features, Power BI Embedded has a bright future.

040716_1237_PowerBIEmbe1.png

 

Power BI SandDance Visual

/0 Comments/in /by

One of the announcements from the Data Insights Summit was the SandDance custom visual. Originating from Microsoft Research and coded in SVG, it not only allows you to visually explore data in versatile ways but it also demonstrates how far your custom visuals can go. This is a super visual that combines multiple visualizations, including column chart, grid, scatter chart, density chart, stack chart, and squarify chart (similar to Treemap) visualization. It also demonstrates animations and storytelling with data. You can test the visual outside Power BI with some predefined datasets by going to https://sanddance.azurewebsites.net. Or, you can download it from the Power BI Gallery and try it with your data in Power BI Desktop and Power BI service.

When you compare visualization tools, pay attention to how open their capabilities are. Power BI provides several extensibility features. Custom visuals let any web developer extend the Power BI visualization features with “widgets” that can leverage popular visualization frameworks, such D3.js and SVG. Do other vendors let you do this?

Power BI Measure Dimensions

/0 Comments/in /by

I had an inquiry about how to implement in Power BI/Power Pivot/Tabular something similar to the new Level of Detail (LOD) Expressions feature in Tableau 9.0. A more generic question would be how to turn a measure into a dimension so that you can analyze your data by the distinct values of the measure. Now, in my opinion, most real-life requirements would go beyond just using the distinct values. For example, the first sample Tableau report demonstrates a banding example. But what if you want to group measure values into “buckets”, e.g. 1, 2, 3, 4, 5-10, 10-20, etc?

Fortunately, DAX has supported powerful expressions since its very beginning. To make the code more compact, the example below uses DAX variables, which were introduced in Power BI Desktop and Excel 2016. However, you don’t have to use variables if you use a lower version of Excel. The sample model (attached) has three tables: FactInternetSales, DimCustomer, and DimDate. The scenario is to analyze data by a NumberOrders band that discretizes the number of orders the customers have placed over time. The trick is to add a calculated column to DimCustomer which has the following DAX formula:

NumberOrders =
VAR SumOrders =
CALCULATE ( DISTINCTCOUNT ( FactInternetSales[SalesOrderNumber] ) )
RETURN
SWITCH (
TRUE (),
SumOrders <= 4, FORMAT ( SumOrders, “General Number” ),
SumOrders >= 5 && SumOrders <= 10, “5-10”,
SumOrders > 10, “More than 10”
)

This expression defines a SumOrders variable whose expression calculates the number of orders each customer has in FactInternetSales. Because the grain of FactInternetSales is a sales order line item, we need to use DISTINCTCOUNT. Then, for more compact syntax, instead of using a bunch of nested IF functions, we use the SWITCH function to evaluate the value of the SumOrders variable and create the bands. This is where the variable comes handy. If you don’t have a variable, you need to create a separate measure for CALCULATE ( DISTINCTCOUNT ( FactInternetSales[SalesOrderNumber] ) ) and use this measure so you don’t repeat the same formula in the SWITCH statement. Variables also has the advantage of evaluating the expression once so the expressions that reference them should perform better.

Now we can have a report that uses the measure dimension, such as to show sales by the number of orders.

measure_dimensions

 

Power BI Hybrid Architecture

/0 Comments/in /by

A unique and tremendously useful Power BI feature allows you to implement hybrid solutions where your data remains on premises (SSAS, SQL Server, Oracle, and other data structures) but only report and dashboard definitions are deployed to powerbi.com. This hybrid architecture gives you the best of both worlds: cutting edge cloud-based visualizations with on-premises security, scale, and single version of truth. Since no cloud application can connect directly to your network, a connectivity software, called Power BI Enterprise Management Gateway, acts as a bridge between powerbi.com and your data sources.

I’m wrapping up a Power BI Hybrid Architecture proof of concept (POC) for a large organization and I want to recap some important lessons learned:

  1. I’m really astonished by the roundtrip speed! It’s remarkable that the round trip completes so fast given that powerbi.com (a data center in Virginia for East Cost I believe) connects to a data center in Atlanta. Granted, not much goes across the wire. Power BI sends a DAX query and SSAS sends summarized results. Still, latency is not an issue. As another customer puts it, “the Power BI reports have better performance than our on-premises SharePoint reports”.
  2. Power BI Enterprise Gateway uses the Azure Secure Service Bus relay connection. What this means for you is that you don’t have to open any incoming ports on your company’s firewall. When the gateway establishes an outbound connection, it scans the available outbound ports. If the only port open is 443 (HTTPS), then it uses this port. Because port 443 might be heavily used, you can open outbound ports 5671-5672 and 9350-9354 for better performance. However, I didn’t see any performance degradation with port 443. This is another remarkable performance observation given that this organization has thousands of computers that use this port when connecting to Internet.
  3. The only setup snag I encountered was this organization uses a proxy that is configured for Basic Authentication only and gateway failed to connect on a first try. I had to create a .NET assembly that returns the credentials of an account with permissions to the proxy using the module configuration element. If your organization doesn’t use a proxy or it does, but the proxy supports NTLM security, you won’t have this issue.
  4. The gateway can be installed on the same machine as the data source or another machine. I recommend installing it on a different machine for an additional level of security. Since one gateway can access multiple data sources, a dedicated gateway server makes more sense. Currently, the gateway service is not fault tolerant but you can install another gateway instance on another machine that services the data sources that need to be highly available.
  5. By default, the gateway runs under a NT SERVICE\PBUEgwService account that the gateway setup creates. You can use another Windows account of course. It needs to have a “Log on as service” right and it needs to be a member of the Performance Log Users local group. The gateway should have no issue running as another low-privileged account, such as LocalService.
  6. Besides the setup requirements and limitations when connecting to SSAS, creating a data source requires hardcoding the credentials of a Windows account that has admin rights to SSAS to delegate the user identity via EffectiveUserName so that SSAS data security works. This might present an issue if your organization has a password expiration policy for service accounts (typically this is not the case bus this organization has to comply to regulations and customer service contracts). I suggested to Microsoft to support another option that would allow connecting to SSAS using the gateway service account via NTLM security. This would allow you to use a managed service account for the gateway service whose password expiration is managed by the domain controller.

powerbi-hybrid

Gartner Positions Microsoft as a Leader in BI and Analytics Platforms

/0 Comments/in /by

The title says it all! Note that the evaluation was done in July-August 2015. Some of the missing points Gartner points out in the report have been implemented already, such as custom visuals So by now the bubble should have moved up toward the ability to execute J

Beyond Power BI, I found the following Gartner’s strategic observations interesting:

  • By 2018, data discovery and data management evolution will drive most organizations to augment centralized analytic architectures with decentralized approaches.
  • By 2017, 75% of IT organizations will have a bimodal capability; half will not handle this well, by either remaining over-controlling or completely ignoring the risks of more agile approaches.
  • By 2017, highly featured and easy-to-use freemium and low-license-cost business intelligence (BI) offerings will accelerate user adoption deep into the enterprise beyond traditional BI users.

Want to learn Power BI? We’re still accepting students for the online class starting Monday.

Class

Type

Mentor

#Date

Price

 
Applied Power BI

Online

 Teo Lachev 2/8-2/9 12:00-4:00 ET

$699

Register

 

Prologika Power BI Showcase

/0 Comments/in , /by

Microsoft launched a Power BI Partner Showcase section to help you “find the right solution for your organization” from certified partners like Prologika. I’m excited to announce the first Prologika Power BI Showcase! It’s based on the work we did for an insurance company. This solution transformed the organization’s data into a key strategic business asset, empowering employees like never before. It currently includes over 300 performance measures that can be analyzed across various dimensions, enabling business to collaborate and share insights with rich data storytelling. The solution delivers a “single version of truth” approach for reporting, and empowers business users to build customized reports and analyses using various tools.

“We are extremely excited for the actionable intelligence and foresight this new tool will bring to our organization”
Director Supply Chain Analytics

Visit the solution page to learn more about how we did it, watch a short video, and even try the interactive reports! Have questions? Contact me to today to find how Power BI can change your business!

Improve your Power BI skills – Five days left to register!

/0 Comments/in , /by

Call/email us if you have any questions about the class. Registration closes on January 31st.

Join Teo Lachev as he presents his online Applied Power BI class. Power BI is a Microsoft cloud-hosted BI platform that opens BI to everyone without reliance on IT. And most of it is free! This class is designed for business users and data analysts. You’ll learn:

  • What is Power BI and why it’ll disrupt the way you analyze your business.
  • How to get instant insights from cloud services, such as Google Analytics, Dynamics CRM Online, Salesforce, and many others.
  • How to create sophisticated self-service data models whose features are on a par with models built by BI pros.
  • How to clean and shape dirty data.
  • How to create insightful interactive reports and dashboards.
  • How to share BI content with other users and view it on mobile devices.

No travel, no hotel expenses, just 100% content delivered right to your desktop! Our intensive online classes teach you the skills to master Microsoft BI to its fullest. Use the opportunity to ask questions and learn best practices. 

Applied Power BI Training in February

 

teo1

NEW! Applied Power BI Online Training Class
Date: February 8 – February 9, 2016
Time: Noon – 4:00 pm EDT; 9 am – 1:00 pm PDT
Delivery: Online presentation with access to recorded content

8 hours for only $699
For more information or to register click here!

Solving the ACE Driver Bitness Madness

/0 Comments/in /by

Scenario: You have installed Office 32-bit (I’m yet to see a company that installs 64-bit as IT loves default paths). Then, you install Power BI Desktop 64-bit. Upon trying to connect to some Office file, such as Access or Excel, you get a compatibility error. You try to install the 64-bit Microsoft Access Database Engine 2010 Redistributable but then you are greeted with “You have a 32-bit version of Office installed”.

Solution: Install the 64-bit Redistributable with the passive overwrite:

  1. Download the 64-bit Microsoft Access Database Engine redistributable.
  2. Open Command Prompt to the download location and execute the following command:
    AccessDatabaseEngine_X64.exe /passive

You can use a similar procedure to force the 32-bit driver if you have Office 64-bit.

Online Applied Power BI Class Forming

/0 Comments/in /by

 

Your 2016 resolution: bring your data to life! Don’t know where to start? I can help wherever you are! The first online run of Applied Power BI class is scheduled for early February. No travel, no hotel expenses, just 100% content delivered right to your desktop! Our intensive online classes teach you the skills to master Microsoft BI to its fullest. Use the opportunity to ask questions and learn best practices.

 

NEW! Applied Power BI Online Training Class
Date: February 8 – February 9, 2016
Time: Noon – 4:00 pm EDT; 9 am – 1:00 pm PDT
Delivery: Online presentation with access to recorded content

 

For more information or to register click here!

Power BI is a Microsoft cloud-hosted BI platform that opens BI to everyone without reliance on IT. And most of it is free! This class is designed for business users and data analysts. You’ll learn:

  • What is Power BI and why it’ll disrupt the way you analyze your business.
  • How to get instant insights from cloud services, such as Google Analytics, Dynamics CRM Online, Salesforce, and many others.
  • How to create sophisticated self-service data models whose features are on a par with models built by BI pros.
  • How to clean and shape dirty data.
  • How to create insightful interactive reports and dashboards.
  • How to share BI content with other users and view it on mobile devices.

.

Getting Rid Of Custom Visuals

/0 Comments/in /by

Scenario: You might have imported a custom visual in Power BI Desktop, tested it, and decided not to use it. However, even if your reports don’t use the visual anymore, Power BI will still prompt you if you want to enable custom visual with “This report contains a custom visual not provided by Microsoft…”. This is a security warning to avoid malicious code because custom visuals are deployed in Javascript.

Currently, there is no way in Power BI to disable this prompt. To make things worse, neither Power BI Service nor Power BI Desktop have a feature to get rid of the custom visual once it’s added to a Power BI Desktop file.

Solution: Here are the manual steps are followed to get rid of custom visuals in Power BI Desktop file for good:

  1. Copy the Power BI Desktop (*.pbix) file. Rename the file to have a zip extension, e.g. from Adventure Works.pbix to Adventure Works.pbix.zip.
  2. Unzip the file.
  3. In the uncompressed folder where you unzip the file content, navigate to the Report folder, and open the Layout file in your favorite text editor.
  4. At the top of the file content, find a resourcePackage string that includes the visual name (you could search for the name of the visual to locate it). For example, the resourcePackage element might look like this for the Sparkline visual:
    ,”resourcePackages”:[{“resourcePackage”:{“name”:”Sparkline1444636326814″,”items”:[{“path”:”icon.png”,”type”:3},{“path”:”Sparkline.js”,”type”:0}
  5. Carefully, delete this entire string but make sure that you don’t end up with two commas or you miss a comma after the deletion.
  6. While you’re in the uncompressed file content, delete also a folder that has the same name as the visual. Strictly speaking, this step is not needed to avoid the prompt but it’s a good idea to clean up all visual files so that you don’t distribute the visual Javascript source.
  7. Zip the entire content again. For some obscure reason, besides getting rid of the visual, in my case compressing the file reduce the PBI Desktop file size almost twice! This reduces the time to upload the visual to Power BI Service.
  8. Rename the file back to the original file name without the zip extension.

Now when you deploy the PBI Desktop file to Power BI and view its reports, you shouldn’t get prompted anymore.