{"id":91314,"date":"2021-06-14T19:55:05","date_gmt":"2021-06-14T19:55:05","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=91314"},"modified":"2024-09-03T20:15:20","modified_gmt":"2024-09-03T20:15:20","slug":"power-bi-jupyter-notebooks","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/power-bi-jupyter-notebooks\/","title":{"rendered":"Embed Power BI in Jupyter Notebooks"},"content":{"rendered":"

Microsoft recently announced the ability to include Power BI<\/strong> reports inside Jupyter<\/strong> notebooks. After overcoming the dazzle of this exciting feature, what comes to my mind is: \u201cWhy do we need this?\u201d<\/p>\n

I\u2019m far from being a Jupyter<\/strong> notebook expert, but as far as I know, they are used for interactive analysis. Why, in the middle of an interactive analysis, would I need to get a Power BI Report<\/strong>?<\/p>\n

Even if the Power BI Report<\/strong> is not exactly what I need, I could continue the analysis in Power BI. <\/strong>Why should I move it to Jupyter<\/strong> and make this kind of integration with an existing report?<\/p>\n

I decided to look for more opinions and asked this question on Twitter. This was the result:<\/p>\n

\"Tweet<\/p>\n

The question has one \u201cLove\u201d<\/em>, three retweets (it was not from my mom; she is still learning about Twitter). This probably means there are more people with the same question but no reply. Others have the same question, and no one has the answer yet.<\/p>\n

The best way to discover what we can be done with it is to investigate the feature.<\/p>\n

Choosing the tool<\/h2>\n

As I explained, I\u2019m not a Jupyter<\/strong> expert. Where could I create a Jupyter<\/strong> notebook?<\/p>\n

I chose to use Visual Studio Code<\/strong> to create the Jupyter<\/strong> notebook. Experts in Jupyter<\/strong> notebooks might use other tools, so I\u2019m unsure if VS Code<\/strong> is the best tool to deal with data, but it works.<\/p>\n

Once deciding to use Visual Studio Code, the Jupyter<\/strong> extension must be installed. It\u2019s easy to find the Extensions on the left toolbar and check for Jupyter<\/strong>.<\/p>\n

\"Jupyter<\/p>\n

Once the extension is installed, you can use the Command Pallet to create a new empty Jupyter<\/strong> notebook:<\/p>\n

\"The<\/p>\n

\"Create<\/p>\n

This Jupyter<\/strong> extension in VS Code<\/strong> was released in April of this year and was demonstrated during the great Scott Hanselman keynote<\/a> at Build<\/em>. You can read more about the extension<\/a> as well.<\/p>\n

Installing the client<\/h2>\n

This feature is enabled by a new PyPI<\/strong> library called Power BI Client<\/strong>. It\u2019s very easily installed using PIP, and the Visual Studio Code terminal supports PIP.<\/p>\n

Navigate to View->Terminal<\/em> or Terminal -> New<\/em> Terminal in the VS Code<\/strong> menu to open the terminal and use PIP.<\/p>\n

\"install<\/p>\n

The Power BI Client<\/strong> is an open-source library<\/a>. This is very helpful, because the source code provides a reference to use the Power BI<\/strong> api, and you could develop any feature missing from this library.<\/p>\n

Building the notebook<\/h2>\n

Add a code block to the notebook. By adding the following code, you import the libraries used for this example. Here\u2019s the code:<\/p>\n

from\u00a0powerbiclient\u00a0import\u00a0Report,\u00a0models\r\nfrom\u00a0powerbiclient.authentication\u00a0import\u00a0DeviceCodeLoginAuthentication\r\nimport\u00a0pandas\u00a0as\u00a0pd\r\nif\u00a0sys.version_info[0]\u00a0<\u00a03:\u00a0\r\n\u00a0\u00a0\u00a0\u00a0from\u00a0StringIO\u00a0import\u00a0StringIO\r\nelse:\r\n\u00a0\u00a0\u00a0\u00a0from\u00a0io\u00a0import\u00a0StringIO\r\nimport\u00a0matplotlib.pyplot\u00a0as\u00a0plt<\/pre>\n
Go ahead and execute the code block. The next action is to authenticate with Power BI<\/strong>. There are four authentication options, and all these options make me think this library can have many other uses than embed Power BI inside Jupyter<\/strong> notebooks.<\/p>\n
Authentication options<\/h3>\n
Two of the four authentication options will require interactive actions by the user on the app, while the other two will not require any interactive action.<\/p>\n
Interactive Flow:<\/strong> This one starts an interactive authentication process opening a browser window for authentication. Once the authentication is done, the notebook receives the authentication token back from the browser window.<\/p>\n
Device Flow:<\/strong> This is the one to use for this example. It starts an interactive authentication process, but the process keeps no relation between the notebook and the browser window. In the notebook, it provides a link and a code. You need to access the link in any browser window you like, input the code, and authenticate. The website will provide the authentication token back to the notebook. Only after this process, the execution can continue.<\/p>\n
Service Principal Flow:<\/strong> This authentication method is basically an App authentication, usually used by services and applications. An App<\/strong> is registered in Azure AD<\/strong> and generates a client_id<\/em> and secret for the authentication of a client app.<\/p>\n
Master User Flow:<\/strong> In this one, you have a username and password, and you will use them for the authentication.<\/p>\n
The authentication code block<\/h3>\n
The import needed for the authentication was already included on the first code block, so this second one has only a single statement:<\/p>\n
#\u00a0Initiate\u00a0device\u00a0authentication\r\ndevice_auth\u00a0=\u00a0DeviceCodeLoginAuthentication()<\/pre>\n
After the execution of this cell, you will see the URL and code needed for authentication:<\/p>\n
\"Interactive<\/p>\n
Open this URL on the browser, copy the code and complete authentication. Only after that will the execution continue.<\/p>\n
Power BI object names<\/h3>\n
The next step is getting the report. This and the next few steps will need to use the name of the Power BI<\/strong> objects. The problem is that the name of the objects is not as apparent as one would expect and may be more difficult to find.<\/p>\n
Let\u2019s start with the workspace name and the report name, because we need to use both to retrieve the report.<\/p>\n
Workspace name<\/h3>\n
Navigate to Power BI online found at https:\/\/app.powerbi.com<\/a>. Navigate to one of your workspaces. Once accessing the workspace, the name is the GUID in the URL<\/em>. The workspace is also called \u201cGroup\u201d. It\u2019s important to keep this in mind this so you can recognize the code.<\/p>\n
\"Navigate<\/p>\n
Report Name<\/h3>\n
In the same way as the workspace, the report\u2019s GUID<\/em> appears in the URL<\/em> when launching a report.<\/p>\n
<\/p>\n
Variables cell<\/h3>\n
Now create a cell to set these values in variables, so they can be used again whenever needed:<\/p>\n
group_id=\"af793584-24cf-4df0-bb78-cd5dd118bb8d\"\r\nreport_id=\"c059c83d-41ee-4f11-9aaa-4c1ef8bdbf14\"<\/pre>\n
Embedding the report<\/h3>\n
After finding the name of the workspace and the report, our next code cell can already embed the report.<\/p>\n
First, we recover the report object. Many methods are available over the report object, but if our objective is only to embed it, we can just let the object do the work:<\/p>\n
report\u00a0=\u00a0Report(group_id=group_id,\u00a0report_id=report_id,\u00a0auth=device_auth)\r\nreport<\/pre>\n
The image below is a very simple report embedded in a Jupyter<\/strong> notebook.<\/p>\n
\"A<\/p>\n
Beyond the report<\/h2>\n
The question remains: Why do you need a PBI report inside a Jupyter<\/strong> notebook?<\/p>\n
If you can have access to the data, on the other hand, everything changes. You could retrieve the data from Power BI<\/strong> and do customized work with it, allowing Power BI<\/strong> analysts and Jupyter<\/strong> analysts to work on the same set of data. It would be way better to have direct access to a Power BI<\/strong> Dataset<\/strong> or Dataflow<\/strong>. However, what you can do with the Power BI Client<\/strong> is access the visuals and then get the underlying data from the visual as I\u2019ll demonstrate in the subsequent sections.<\/p>\n
If the purpose is to get the data, access a report, report page and visual to get the underlying data and be able to make further analysis with the data is a considerable work-around for this task. IMHO, I consider the word work-around very soft for these situations, that\u2019s why we, Brazilians, are introducing into Europe the word \u201cGambiarra\u201d.<\/p>\n
Page and visual name<\/h3>\n
Once again, discovering the page and visual name can be a challenge. This time, it\u2019s even a bigger challenge than before, because these two the names don\u2019t appear in the URL<\/em>.<\/p>\n
To solve this problem, the Power BI Client<\/strong> enables retrieving this information from the list of pages of the report and list of visuals in a page.<\/p>\n
Important:<\/strong> the methods to retrieve data from the report only work if the report is embedded on the page. The last statement from the previous code cell, executing the object Report to embed the Power BI report, was really needed. This is a limitation, and I will talk more about this later.<\/p>\n
Pages list and page name<\/h3>\n
Begin with the list of pages in a report. A new code cell is needed for that. The methods for this task are available from the report object.<\/p>\n
pages\u00a0=\u00a0report.get_pages()\r\npage_name=pages[0][\"name\"]\r\npages<\/pre>\n
\"code<\/p>\n
The first surprise is with the page: The name on the report is only the displayName<\/em>. The real name of the page is something completely different, as you can see in the JSON returned.<\/p>\n
The code defines a variable with the actual page name. The display of the page list is only for interactive view.<\/p>\n
Visuals list and name<\/h3>\n
Now that you have the page name, you can retrieve the list of visuals in the page.<\/p>\n
visuals\u00a0=\u00a0report.visuals_on_page(page_name)\r\nvisual_name=visuals[0][\"name\"]\r\nvisuals<\/pre>\n
\"code<\/p>\n
The name of the visual is even more unpredictable than the name of the page. Once again, the script defines a variable to contain the name of the visual<\/p>\n
Finally, the data<\/h3>\n
Joining the report, page and visual, now you can retrieve the data and transform it into a Pandas<\/strong> dataframe<\/strong>.<\/p>\n
The data returns as a big comma-separated string. The Read_CSV<\/strong> method requires a file or a buffer to transform a CSV<\/strong> in a dataframe<\/strong>. The STRINGIO<\/strong> method creates a buffer for a simple string, allowing conversion of the string to a dataframe<\/strong>.<\/p>\n
The code cell will be this:<\/p>\n
exported_data\u00a0=\u00a0report.export_visual_data(page_name,\u00a0visual_name,\u00a0rows=1000,\u00a0export_data_type=models.ExportDataType.UNDERLYING.value)\r\nexported_data=StringIO(exported_data)\r\ndf=pd.read_csv(exported_data,sep=\",\")\r\ndisplay(df)<\/pre>\n
\"data<\/p>\n
Besides the parameters we already know, there are two additional ones:<\/p>\n
Rows<\/h3>\n
This parameter defines the number of rows to be recovered. That\u2019s not good at all. The parameter is optional, but the default value is 10. This means we will need to know how many records there are in the underlying data, and that\u2019s impossible.<\/p>\n
Although it\u2019s possible to recover the data and make transformations on the data, this parameter makes the feature very limited.<\/p>\n
Export_Data_Type<\/h3>\n
This parameter supports two values:<\/p>\n
ExportDataType.SUMMARIZED:<\/strong> Returns the data summarized by the visual according to the visual work.<\/p>\n
ExportDataType.UNDERLYING:<\/strong> Returns the underlying data in relation to the visual.<\/p>\n
Transforming the data<\/h3>\n
From this point and forward, you can transform the data using function on Python<\/strong>, Pandas<\/strong> or other libraries. I made some examples of these possible transformations:<\/p>\n
df[\"Year\"]=\u00a0pd.DatetimeIndex(df['OrderDate']).year\r\ndf[\"Month\"]=pd.DatetimeIndex(df['OrderDate']).month\r\ndf2=df.groupby('Year')[\"SalesAmount\"].sum()\r\ndf3=\u00a0df[df.Year==2013].groupby('Month')[\"SalesAmount\"].sum()\r\ndf3<\/pre>\n
This code cell makes two different transformations:<\/p>\n