Posts

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.

Integrate SSRS 2016 Reports with Power BI

/0 Comments/in /by

As I said in the “Microsoft Unveils BI Roadmap” blog, SSRS will play an important role of the on-premises BI portal. With this post, I’ll start covering the new SSRS 2016 features. One of the Power BI features announced at the PASS Summit was the forthcoming integration between SSRS 2016 and Power BI that will allow users to pin SSRS report items to Power BI dashboards. You can test pining reports items to Power BI with SQL Server 2016 CTP 3.0 and later. The newly released CTP 3.2 also features the new sleek SSRS portal (we’ve come a long way!). The items you can pin currently include charts, gauge panels, maps, and images. Pinning a report item takes a few clicks:

  1. Open the report and click the Power BI button (a new toolbar button in SSRS 2016). If this is the first time you do this, you’ll be asked to authenticate with Power BI and grant SSRS special permissions it needs for pinning. Then, SSRS switches to a special page mode that shows which items can be pinned.

  1. Click the tile you want to pin.
  2. In the “Pin to Power BI Dashboard” window, select the dashboard you want to pin the report to and the update frequency that specifies how often Power BI will query the report for data changes.

And, the report item is pinned to the dashboard as a regular tile. You can click the tile to navigate back to the SSRS report.

As you can see, Power BI is becoming a focal point for cloud hosting of the main MS report artifacts: Power BI reports, Excel reports, and SSRS reports.

The One and Only Power BI Book

/0 Comments/in , /by

To me, Power BI is the most exciting milestone in the Microsoft BI journey since circa 2005, when Microsoft got serious about BI. Power BI changes the way you gain insights from data; it brings you a cloud-hosted, business intelligence and analytics platform that democratizes and opens BI to everyone.

I’m happy to announce my latest (7th) book – Applied Microsoft Power BI. Currently, the one and only book on Power BI. Some people discouraged me to write this one. After all, trying to cover a product that changes every week is like trying to hit a moving target. However, I believe that the product’s fundamentals won’t change and once you grasp them, you can easily add on knowledge as Power BI evolves over time. Because I had to draw a line somewhere, “Applied Microsoft Power BI” covers all features that were announced at the PASS Summit 2015 and that were released by December 2015.

The book has four parts for each of the four user types: information worker, data analyst, pro, and developer. Information workers will learn how to connect to popular cloud services to derive instant insights, create interactive reports and dashboards, and view them in the browser and on the go. Data analysts will discover how to integrate and transform data from virtually everywhere, and then implement sophisticated self-service models. The book teaches BI and IT pros how to establish a trustworthy environment that promotes collaboration and how to implement Power BI-centric solutions for descriptive, real-time, and predictive analytics. Developers will find how to integrate custom applications with Power BI, embed reports, and implement custom visuals to present effectively any data.

The book is making its way slowly through the retail channels but it should be available on Amazon (in both paper and Kindle formats) and with other retailers before the holidays. I’ll drop a note and update the book page once the book is available for purchase. Meanwhile, visit the book page for more information about the book, source code, and a sample chapter (Chapter 1 “Introduction to Power BI”).

I predict that 2016 will be the year of Power BI and I hope that this book will help you bring your data to life!