PBIRS Scheduled Data Refresh: Execution History

Scheduled Data Refresh came to Power BI Report Server (PBIRS) with the October 2017 release. I’ve already blogged about the steps required to configure a refresh data plan in PBIRS.

The Scheduled Refresh configuration screen only shows the timestamp of the last refresh run plus the status information. But only for the last refresh cycle!

pbirs_october2017_26

Which is ok if the refresh worked fine, but if there was an error during data refresh you only have time until the next refresh cycle starts to display and further dig into the error.

pbirs_october2017_33

I’ve searched through the PBIRS user interface for additional refresh history but I was not lucky. With that I had a look into the PBIRS meta-database and found the information there!

Refresh History in the PBIRS Meta-Database

CAUTION: The PBIRS meta-database is a system database needed for successfully running PBIRS. Do NOT modify objects in that database – a non-functioning PBIRS could be the result!

The table dbo.SubscriptionHistory contains the history of data refresh actions. A SELECT on this table provides you with the following information:

  • Subscription metadata (SubscriptionID plus Type)
  • Execution runtime information (HistoryID, StartTime, EndTime, Status plus Message)

pbirs_october2017_32

The columns StartTime / EndTime could be used to analyze the historic trend of refresh time requirements.

The column Status contains the current / historic status of a refresh execution run. Based on my research (as of today) the following values are used:

  • 0 = data refresh finished sucessfully
  • 1 = data refresh is in progress
  • 2 = error during refresh

If there was an error during the refresh detailed information can be found in the column Message. In my example (I renamed the source SQL table) some example error messages were : Data source error: Login failed for data source ‘Unknown’ or Several errors occured during data refresh. Please try again later or contact you administrator.

CAUTION: The PBIRS meta-database is a system database needed for successfully running PBIRS. Do NOT modify objects in that database – a non-functioning PBIRS could be the result!

Happy querying the execution log,

Wolfgang

 

Advertisements
Posted in PBIRS, PowerBI, SSRS | Leave a comment

Walkthrough: Scheduled Data Refresh in Power BI Report Server

One of my favorite features included in the new Power BI ReportServer (PBIRS) October 2017 release is the ability to define scheduled data refresh for Import-mode datasets.

I would like to go through a walkthrough example to show you the steps that are required in order to create, publish and configure scheduled data refresh for a Power BI report published to PBIRS (minimum October 2017 release).

Power BI Desktop (optimized for PBIRS)

I assume, you already have downloaded (https://aka.ms/pbireportserver) and installed the PBIRS October Release. Besides the  Report Server you’ll need a specialised Power BI Desktop release (also downloadable from the link above). The PBI Desktop release optimized for PBIRS can be distinguished from the “normal” PBI Desktop release based on addition to the program link in the start menu (“Power BI Desktop (October 2017)”). In addition, the title bar of the application also contains the October 2017 hint which makes it easier to find the right PBI Desktop.

pbirs_october2017_02

Power BI Desktop optimized for PBIRS (start menu entry)

pbirs_october2017_02a

PBI Desktop optimized for PBIRS

Create the Report

In my sample I create a sample report based on a simple table stored in SQL Server. During the Get Data steps I chose Import mode and no data transformation was applied.

In addition to the sales data I imported a system view that contains one field with a timestamp (this view is used to show the latest timestamp the data was imported into the dataset / Power BI ).

Based on those imported tables I created a sample report:

pbirs_october2017_07

Publish the Report

After finishing the report, the next step was to publish it to Power BI Report Server (PBIRS). I think I already blogged about it but again: Publish to PBIRS is not possible with the publish action in the PBI Desktop menu. PBI Reports for PBIRS need to be saved to the report server.

You only need to enter the URL of your PBIRS and choose the folder where you would like to save the report to.

After that we are ready to test the report. Either you use the direct link in the dialog shown above or you navigate manually to your PBIRS URL.

pbirs_october2017_15

Let’s open the report et voila! Here it is! What you eventually can spot in the picture below (it’s marked in yellow) is the indicator for the last import timestamp plus the current time in my test-server. The last data import happend ~5 minutes before the report rendering. If you try to hit the refresh button – nothing will change because that report is based on an import dataset which means that data is only updated during a data refresh cycle!

pbirs_october2017_16

Configure Data Refresh

Navigating back in your browser brings you to the folder content. If you click on the three “…” next to your published report the context menu opens. We need to navigate to the Manage command in order to configure data refresh.

In the Manage section you can either edit the properties of your report, define security definitions or configure Scheduled Refresh and your Data Sources.

My first idea was to “just configure scheduled refresh” was stopped by the following screen. Scheduled Refresh in PBIRS needs stored credentials (either SQL user or Windows user)!!

pbirs_october2017_19

Configuration of stored credentials is done in the Data sources menu.  Enter the credentials for all the data sources in your report (in my case it is only one data source but there can be multiple data sources!).

pbirs_october2017_20

Back to the Scheduled Refresh menu I am now able to configure a New scheduled refresh plan. Within this dialog you can also use existing plan definitions to build new ones plus start an immediate data refresh. In the screenshot below those options are greyed out because no refresh plan existed!

pbirs_october2017_22

The definition of a refresh plan is simple: Enter an (optional) description and define your refresh schedule. You can either use a shared schedule or create a report-specific one.

pbirs_october2017_23

If you are familiar with defining SQL Server Agent Job Schedules the definition dialog looks definitely familiar for you. You can define the schedules based on a repeating time interval or just a one-time schedule.

What is also important to add here: SQL Server Agent is needed for the Scheduled Data Refresh functionality (see next chapter for more details).

What needs to be added here is the fact that data refresh can be speeded up to 1 minute refresh intervals. (Which is much more often compared to 8 times for powerbi.com PRO users).

When you are done with the schedule configuration, save it and the definition dialog is updated with the plan information. The last data refresh timestamp plus the last status is also displayed.

pbirs_october2017_26

Going back to the report we are informed about the latest data refresh timestamp that is almost up-to-date with the system time in my test system.

pbirs_october2017_27

Behind the scenes – SQL Server Agent

As I’ve already mentioned SQL Server Agent is needed for the Scheduled Data Refresh functionality. Behind the scenes PBIRS creates a SQL Server Agent job for every refresh plan. In my example the following Agent job was created:

pbirs_october2017_28

Looking at the properties of that job we’ll see that it’s a “normal” Agent job that does the magic of data refresh (stored procedure [ReportServer].dbo.AddEvent with parameter @EventType=’DataModelRefresh’).

 

Posted in PBIRS, PowerBI, SSRS | 3 Comments

Welcome Power BI Report Server (PBIRS) October 2017 Release!

During the #PassSummit Keynote (Day 1) the new release of Power BI Report Server (aka PBIRS) was announced.

Based on the August 2017 preview the work was continued and many new features are packed into this new release:

  • Scheduled Data Refresh for Import Dataset
  • Direct Query for Power BI Reports published to PBIRS
  • REST API (similar to the one published for SSRS 2017 plus enhancements for PBIRS)
  • Shared Datasets are exposed as OData sources
  • Filter Power BI Reports using URL Parameters

Those are the features described in the announcement post BUT there is more: If you have a look at the “What’s new page” you’ll find more of them (plus some more configuration hints).. 🙂

  • File sizes for uploads/downloads are increased
  • Scale-Out support
  • Administrator settings (which ones are available plus where to configure them)
  • SSAS and PBIRS – how they work together and how to configure memory requirement settings
  • Support for new table and matrix visuals

I’ve already downloaded and installed the october bits. Stay tuned for more info…

Happy Reporting with on-premises Power BI,

Wolfgang

pbirs_october2017_01

Posted in PBIRS, PowerBI, SSRS | 3 Comments

SSIS 2017 – Scale Out – Master and Worker Responsibilities

In my previous post I explained the basics and types of scalability. Two participants are involved in the Scale Out scenario: Master and one or more workers.

Scalability_04_ScaleOut

Master Responsibilities

The master is managing the available workers and all the work that is requested for execution in the scale out topoloy.

  • The master manages a list of (active) workers
  • The master gets the instructions from clients
  • The master knows the current state of work (queued jobs, running jobs, finished jobs, ..)

Workers Responsibilities

Workers are responsible for the “real” work. In contrast to the master, the workers are not directly communicating with clients – all the communication is routed via the master.

  • Workers register themself at the master
  • Workers claim new tasks from the master
  • Workers are working on their tasks
  • During task execution, workers return progress information back to the master
  • After the execution the results are returned (either successful or erroneous executions)
  • Workers typically know their profession (in the context of SSIS executions i.e. some workers are SAP extractors, some are optimized for calculations, ..)

 

Posted in Scale Out, SSIS | Leave a comment

SSIS 2017 – Scale Out (Scalability Basics)

In this post (and maybe some follow-ups) I would like to introduce to you the Scale Out feature.

SQL Server Integration Services (SSIS) are the data integration product within the SQL Server product. Introduced with SQL Server 2005 release it got some love (plus new features and enhancements) during the next few releases:

  • SSIS 2008/R2
  • SSIS 2012 with SSIS Catalog, Project Deployment Model, Parameters, ..
  • SSIS 2014 remained unchanged leading to some discussions about the future of SSIS
  • SSIS 2016 introduced Incremental package deployment, custom logging levels, reusable control flow templates, ..
  • SSIS 2017 continues the story with enhancements in the areas of Integration Services on Linux (yeah), connectivity improvements and integrated Scale Out functionality.

SSIS 2017 Scale Out

In the past, SSIS package executions were only able to run on the server that hosted the Integration Services server itself. With the rising number and requirements of more and more package executions sometimes the resources on the server ran short. Addressing this resource shortage custom scale out functionality was implemented that allowed package executions to be transfered to other “worker” machines in order to distribute execution load. With SQL Server 2017, this functionality is built into an shipped with SSIS 2017.

Before I am diving deeper into SSIS Scale Out I would like to discuss some basic vocabulary in the field of scalability.

What is Scalability?

When talking about scalability we’ll have to distinguish between different types of scalability but first of all: What is scalability? On of the first places I am looking for definitions and more information about topics is wikipedia. The wikipedia entry about scalabilty provides an in depth definition of the term and all it’s different shapes. What I really liked was the following definition:

Scalability is the capability of a system, network, or process to handle a growing amount of work, or its potential to be enlarged to accomodate that growth.

Bondi, André B. (2000). Characteristics of scalability and their impact on performance. Proceedings of the second international workshop on Software and performance – WOSP ’00. p. 195. ISBN 158113195Xdoi:10.1145/350391.350432.

Systems that support scalability are required to support one or more types of scalability:

  • Functional Scalability: Support for easily adding new functionality to the system.
  • Administrative Scalability:  The system should be able to cope with an increasing number of users.
  • Geographic Scalability: Starting with a single location system but leading to geo-distributed applications – geographic scalability is the key for support those new scenarios.
  • Generation Scalability: Scalability in the context of adding new generations of components.
  • Load Scalability: This was actually the type of scalability I was thinking of beforehand. Load scalability defines the ability of a system to handle higher amounts of requests. And load scalability is exactly the type of scalability SSIS Scale Out addresses.

Horizontal versus Vertical Scaling

There are two ways for adding more resources to an existing application to address load scalability.

Just think of a system (represented by the black box) and resource requirements (represented by the light blue part of the box). The system was sized well at the beginning, but over time more and more i.e. SSIS package executions are required in a shorter period of time. With that in mind resource requirements increase and at a certain point in time a limit is reached.

Scalability_01_resourceUsage

Vertical Scaling – Scale Up/Down

In order to cope with this resource requirements one way of solving it is to just add more resources to the system (more CPU power, more RAM, faster disks etc.) This concept is called Scale Up (or Scale Down if you remove resources from the system) or in other words: vertical scalability. Especially in virtual environments that concept is used very often (because of the easy way of adding resources to the virtual server).

Scalability_02_ScaleUp

Horizontal Scaling – Scale Out/In

Horizontal scaling deals with the distribution of load to multiple workers. The initial system gets the duty of acting as a master for other worker nodes.

Scalability_03_ScaleOut

The more power is needed the more workers can be added to this Scale Out topology. Horizontal Scalability gets more power with every worker that is added to this construct. Workers do not need to have the same resources (computing power) and/or system requirements. In the context of SSIS Scale Out, workers can get their special duties assigned (i.e. workers optimized for SAP connectivity others optimized for calculation tasks).

With those basics defined we can now dive deeper into SSIS Scale Out and all the topics involved. Stay tuned for the next SSIS Scale Out post!

References:

Posted in Scale Out, SSIS | 2 Comments

Some Questions and Answers around the Power BI Desktop Store App

Until recently, the only way to install Power BI Desktop was to download and install the .exe installer from powerbi.com. Which was an easy way of a next – next – next setup approach. But: the user needed admin-rights to install and/or update Power BI Desktop.

Which should not pose a problem to most of the users but in some (bigger)  companies admin-rights are not there for everybody. Together with the monthly update releases it was sometimes not easy to distribute the newest Power BI Desktop releases to all users in those companies.

At Ignite 2017, the Power BI team announced (together with other announcements) that Power BI Desktop is now available in the Windows 10 Store. Same binaries, same functionality – with one BIG advantage: no admin rights because the store itself handles the updates (in the background). In addition, there is also a save in download size because only those binaries that are changed get updated.

pbidesktop_store02

Question: How to install the Power BI Desktop Store App?

Question: How about a Side-By-Side installation of the download-install-version and the Store App?

  • Well, on my machine it works. And according to the answers following my twitter question also on others’ machines.
  • As of today, the Store App and  native application have the same binaries version. I will test it in the next month with an auto-update of the Store App versus a non updated native application.

Question: My start menu contains two “Power BI Desktop” links… Which one is the Store App? Which one the native application?

  • When searching for “Power BI Desktop” in the Windows 10 start menu, you’ll get two entries: “Power BI Desktop” is the native application (also marked with Desktop app), whereas the Store App is named “Microsoft Power BI Desktop”

pbidesktop_store04

Question: What about the future: Will the native application be discontinued?

  • Based on my current knowledge, both versions will continue to exist in the future.. no warranty whatever Microsoft decides in the future

Question: Are there any feature differences between the Store App and the native app?

  • Both versions provide the same feature set. I will have to test i.e. specific connectors but basically, they are the same

 

Happy Power BI-ing,

Wolfgang

Posted in PowerBI | 1 Comment

Power BI Desktop – Jetzt als Windows Store App

Power BI Desktop war bis vor kurzem nur als Installationspaket erhältlich.  Was ja an und für sich kein Problem war – jedoch war es durch die monatlichen Updates ab und zu ein Problem die Software für Benutzer ohne Administratorberechtigungen zu installieren. Und genau diese fehlenden Administratorberechtigungen waren sehr oft das Hindernis, dass Firmenbenutzer nicht mit der aktuellsten Power BI Desktop Version arbeiten konnten.

Auf der Ignite 2017 wurde nun angekündigt, dass Power BI Desktop nun auch als Windows Store App verfügbar ist. Hervorzuheben ist hier die automatische Aktualisierung der Software – keine Admin-Berechtigungen. Sobald eine neue Version verfügbar ist, wird diese durch die Standard-Windows-Store Funktionalität ausgerollt. Und dies auch noch ressourcensparender, da der Store nur die wirklich geänderten Binaries neu herunterlädt.

pbidesktop_store01

Frage: Wie finde ich nun die Windows Store App?

  • Windows Store öffnen
  • nach “Power BI Desktop” suchen (oder direkt: http://aka.ms/pbidesktopstore)
  • die Anwendung installieren und denselben Funktionsumfang wie mit der installierten Variante genießen 🙂

Frage: Ist eine Side-by-Side Installation möglich?

  • Auf meinem Rechner funktioniert die Side-by-Side Installation. Ich habe jedoch keine Dokumentation gefunden, ob dies auch unterstützt bzw. in Zukunft auch so sein wird.

Frage: Welcher Link im Start Menü ist die App? Welcher die Install-Variante?

  • Wird im Start Menü nach “Power BI Desktop” gesucht, bekommt man bei einer Side-By-Side Installation die beiden Varianten zurückgeliefert. Die Install-Variante bekommt den Zusatzhinweis “Desktop App”, die Store-Variante ist in der Gruppe “Apps” versteckt.

pbidesktop_store04

Frage: Wird es in Zukunft nur mehr die Store App geben?

  • Nein – Parallel dazu wird es auch (so zumindest ist es aktuell kommuniziert) auch die Installationsvariante geben (z.B. für ältere, “non-Windows 10” Betriebssysteme).

Frage: Gibt es Funktions-/Feature-Unterschiede zwischen Install und Windows Store Variante?

  • Sind mir keine bekannt. Es sollte sich um dieselbe Software handeln.

 

Zum Store-Link:  http://aka.ms/pbidesktopstore

Happy Power BI-ing,

Wolfgang

pbidesktop_store01

pbidesktop_store03

Posted in PowerBI | Leave a comment