Posts

Unblocking the On-premises Data Gateway

/0 Comments/in /by

Scenario: You have configured the Power BI on-premises data gateway for centralized data access and verified that its data sources test just fine. Direct query connections work. However, when you go to Power BI Service and attempt to schedule a data refresh for a dataset, you might find that the data gateway is disabled.

Solution: The most common reasons for Power BI to disable the on-premises data gateway for refresh are:

  1. Unlike the personal gateway, the on-premises data gateway requires you to register data sources. You must go to the gateway properties and create data sources for all data sources used in your Power BI Desktop file. Unfortunately, as it stands Power BI doesn’t allow you to select which data sources in the Power BI Desktop file will be refreshed and which ones don’t require a refresh. It’s all or nothing proposition. So, if one data source is not compatible or can’t be refreshed, the gateway will be disabled.
  2. The connection strings in data sources in the Power BI Desktop file might differ from the settings of the data sources you registered in the on-premises gateway. For example, in Power BI Desktop you might have imported data from a local Excel file. Then, you might have moved the file to a network share and established a gateway data source to point to the network share. Because the connection strings differ, Power BI Service won’t find an on-premises gateway to serve the Excel file and it will disable the gateway for refresh. So, triple-verify the that data sources match.
  3. You might have manually added a table to your model and entered some data using the Power BI Desktop “Enter Data” feature. Because custom tables can’t refresh, Power BI disables the gateway.

012817_0130_Unblockingt1.png

Customer Success Case – ZynBit

/0 Comments/in , /by

One of our customers, ZynBit, made the Power BI blog today! Initially, ZynBit was considering Tableau but abandoned it in favor of Power BI because of the Power BI superior data modeling capabilities and the cost effective licensing model of Power BI Embedded. Prologika helped ZynBit to transition their solution to Power BI, including designing the data model and integrating reports with Power BI Embedded. Read our case study here.

Power BI Subscriptions

/0 Comments/in /by

Today Microsoft released a highly anticipated Power BI feature – subscribed report delivery. Similar to SSRS individual subscriptions, users can go to a Power BI report and subscribe to one or more of its pages to receive a snapshot of the page on a scheduled basis. The following scenarios are possible depending on the report data source:

  • Imported datasets – the subscription follows the dataset refresh schedule. You’ll get an email every time the scheduled refresh happens, so long as you haven’t gotten an email in the last 24 hours.
  • DirectQuery datasets – Power BI checks the data source every 15 minutes. You’ll get an email as soon as the next check happens, provided that you haven’t gotten an email in the last 24 hours (if Daily is selected), or in the last seven days (if Weekly is selected).
  • Live connection to SSAS – Power BI checks the data source every 15 minutes and it’s capable of detecting if the data has changed. You’ll get an email only if the data has changed if you haven’t gotten an email in the last 24 hours
  • Connected Excel reports – Power BI checks the data source every hour. You’ll get an email only if the data has changed if you haven’t gotten an email in the last 24 hours.

011617_1307_PowerBISubs1.png

Power BI subscriptions have these limitations:

  • The only export option is screenshot. You can’t receive the page exported to PowerPoint, for example.
  • Users can create individual subscriptions only. You can’t subscribe other users as you can do with Reporting Services data-driven subscriptions.
  • The Power BI admin can’t see or manage subscriptions across the tenant.

Power BI Reports in SSRS Techinical Preview

/0 Comments/in /by

From the glimpse to the first public preview…it’s great to see one of most requested feature coming to life: ability to render online Power BI reports in on-premises SSRS. Alas, Microsoft is keeping us in suspense and no official date and release vehicles have been announced yet but we can now see and test it using the VM that Microsoft put on Azure (read the Chris Finlan’s steps to get started).

At this point, the integration supports only Power BI Desktop files that connect to Analysis Services (Multidimensional and Tabular). Attempting to deploy models connected to something else or with imported data, doesn’t work and you’ll get an error. Custom visuals and R visuals are not supported yet. For the most part, the integration is limited to report viewing only (similar to what you get if you embed Power BI reports in Power BI Embedded). That’s will be probably fine to start with but it will be nice to have Q&A, Quick Insights, Analyze in Excel, and ability to create custom reports online.

It’s great that Microsoft decided to expose the connection string as a regular SSRS data source. This will allow you to change the credentials settings, such as to impersonate the user when Kerberos is not an option and the SSAS is on another server. When the Power BI Desktop file is uploaded, it’s saved in the report catalog as any regular SSRS report (now referred to a paginated report). This means that you secure and manage Power BI reports the same way you work with paginated reports. Speaking of management, I hope that at some point Microsoft will add support for subscriptions and caching. What’s need is unification among the four types of reports: paginated, mobile, Power BI, and Excel. While waiting, we get a handy bonus feature: ability to add comments to reports, such as to get someone to formally approve what they see on the report.

Everyone is asking about SSRS support for Power BI reports. Progress has been make and I hope it won’t be long before we get the real thing.

Azure Analysis Services

/0 Comments/in /by

Despite the mantra you might hear elsewhere, my experience shows that the best self-service BI is empowering users to create reports from trusted semantic models sanctioned and owned by IT. Most of the implementation work I do involves Analysis Services in one form or another. Analysis Services has a very important role in your BI ecosystem as I explain in the “Why Semantic Layer” newsletter.

Today, at the SQL PASS SUMMIT, Microsoft announced that Analysis Services Tabular is now available as an Azure PaaS service. As a participant in the prerelease program, I had the opportunity to test Azure Analysis Services and this is why I believe you should care:

  1. If you develop cloud-based solutions, you might not have to provision a VM for Tabular anymore. Instead, you can provision an Analysis Services cloud service in seconds, just like you can provision an Azure SQL Database.
  2. You can easily scale up or down Azure Analysis Services, just like you can do this with Azure SQL Database. You can even pause it so that you don’t incur cost.
  3. You don’t have to set up a gateway for SSAS. You can use Power BI Desktop to connect to Azure Analysis Services and deploy the report to Power BI. However, you would need a gateway if your source data resides on premises so that you can process the model with on-premises data. Note that currently you can’t use Power BI Get Data to connect to Azure Analysis Services directly from Power BI. Instead, you must use Power BI Desktop.
  4. The service is highly available by default. SQL Server pros implementing highly available solutions know that this is not easy and not cheap. So, factor in high availability if you find Azure Analysis Services pricing is too high.

On the downside, as it stands Azure Analysis Services uses Azure Active Directory for security and it doesn’t support claim authentication. Power BI users will be able to authenticate but not Power BI Embedded (not yet).

Currently in preview, Azure Analysis Services is a very important addition to the Microsoft Azure BI stack that allows BI pros to implement cloud-based semantic models as they can currently do on premises.

102616_0114_AzureAnalys1.png

Automating Power BI Desktop Refresh

/4 Comments/in /by

Power BI Desktop is becoming an increasing popular tool for self-service reporting. But it has a glaring gap. Unlike Excel, it doesn’t currently support an object model for automating tasks. Yet, there are a variety of scenarios that call for task automation, such as refreshing imported data. For example, one customer wanted to show a Power BI Desktop dashboard on a shared monitor that will refresh itself periodically. In another scenario, an ISV wanted to automate the data refresh because Power BI Embedded doesn’t currently have APIs to support a scheduled refresh.

Currently, there is no supported way to refresh Power BI Desktop files automatically. However, you can try the following approaches at your own risk:

  1. Use the Michal Dúbravčík’s PBIXRefresher script. This is a PowerShell script that opens Power BI Desktop and sends a key to the Refresh button.
  2. Shell out to open Power BI Desktop with the file you want to refresh (pbidesktop.exe <filepath to pbix file>). Then, find programmatically the port that the PBI SSAS listens on (see my “Upgrading Power BI Desktop Models to Tabular” blog on this subject). Then, use AMO or the new Tabular Object Model to send a process script command.
  3. Use the commercial Power Update tool, which is capable of refreshing Excel Power Pivot workbooks and Power BI Desktop files.

A Glimpse of Embedding Power BI Reports in SSRS

/0 Comments/in /by

The first public demo of the highly anticipated Power BI report embedding feature in SSRS 2016 on premises came from Microsoft Ignite. Scroll to the 58 minute in the Ricardo Muti’s “Create a modern enterprise reporting and mobile BI solution with SQL Server 2016” video and enjoy! I expect more details at SQL PASS SUMMIT at the end of this month. Thanks to Dan English for pointing out this video.

100716_0242_AGlimpseofE1.png

Power BI Adds Time Series Forecasting

/0 Comments/in /by

The September update of Power BI Desktop adds one of the most requested features – time series forecasting on single line charts. You can control the confidence interval and seasonality. To use forecasting, make sure that you add a field of Date data type to the chart axis.

100116_2012_PowerBIAdds1.png

Only when a date field is used, then you’ll see the Forecast section added to the Analytics pane.

100116_2012_PowerBIAdds2.png

Power BI Nested Hierarchical Labels

/0 Comments/in /by

In the latest Power BI Desktop update, Power BI introduced a new drill-down option. Previously, after enabling drill-down on a chart and clicking the double-arrow icon, the chart will drill down to the next field in the Axis area (or to the next level of a hierarchy if a hierarchy is added to the Axis area). A student attending my Power BI class asked me this week how to avoid aggregation across all years if the user drills down from Year to the Month level. A few days ago, the only option was to introduce another Month field that has the Year-Month combination, e.g. 2005 July, 2005 August, and so on. Now, with the new drill down style, the chart will nest the hierarchical levels so you don’t have to add a new field. This behavior is similar to how Excel show categories nested in PivotChart.

New Custom Visual Developer Toolset

/0 Comments/in /by

One of the most prominent Power BI benefits is its extensible architecture that allows developers to integrate Power BI with custom apps and extend its capabilities, such by creating custom visuals. Having contributed one of the first custom visuals, the Sparkline custom visual, I can tell from experience that Microsoft does its part to ensure that the submitted visuals meets quality, security, and functionality best practices.

As custom visual developers have probably noticed, the Power BI Dev Tools is deprecated in favor of the new Custom Visual CLI Developer Toolset. The announcement page enumerates the main benefits of the new toolset. Personally, I like that Microsoft has decoupled the tool from dependencies to the “visual framework” that the original Power BI visuals use. This allows developers to use whatever dev tool they like, such as Visual Studio Code or Visual Studio, to code custom visuals. I also like the better integration with Power BI Service for auto-detecting code changes and showing what data has been passed to the visual.

There will be some pains when migrating existing visuals to the new tool. As of this time, there are also missing features so don’t rush yet to the new tool:

  1. Tooltips are not yet supported.
  2. Value formatters, such as to format values shown in tooltips, are not supported so you have to create your own, use third-party libraries, or copy code from this project.
  3. The D3 definitions that the tool installs are different from the ones old tool and the Visual Studio framework used. Moreover, Microsoft indicates that they will stop providing the D3 definitions. Consequently, some D3 features I used resulted in compiled errors so I had to use the old types.
  4. For some reason, checking the “Enable developer visual for testing” checkbox in Power BI Settings didn’t “stick” for me in Chrome so I had to use Edge.

The new Custom Visual CLI Developer Toolset will improve the developer experience for coding custom visuals. Download the migrated Sparkline code that works with the new toolset to understand the changes.

Events

Nothing Found

Sorry, no posts matched your criteria