Author Archives: Hariharan Rajendran

ByHariharan Rajendran

[First Impression] Automate Power BI Export File with Power Automate

Here, I am covering what are the options available in latest actions in Power BI Connector in Power Automate. Those 2 actions are most wanted from most of the Power BI professionals.

Most of the customers are wanted to subscribe their reports and they want to get the file in their inbox or network shared drive. As of now, we have option for email but not for network shared drive. There was a gap in the product and it got resolved when Power BI team released the REST API support for export the files but we it was possible by creating custom connectors with Azure APP client details in Power Automate. Now, we have direct connector to achieve the same result in an easy way.

You know, I have recently written an article where I compared the Power BI Paginated report with Power BI Report Server Paginated report. I have considered this as an one of the feature comparison since there was no support at that time. I may need to update my post with this latest information.

We had only 2 actions under Power BI but now, we have two more.

Check out the pre-requisites and other details here – https://powerbi.microsoft.com/en-us/blog/power-automate-actions-for-exporting-power-bi-and-paginated-reports-now-available/

Export to File for Power BI Reports supports only PPTX, PDF & PNG as same in the service. We have additional option like Include hidden pages & Bookmarks with limited functionality.

Just tested with One Drive as a destination and it worked well.

Export To File for Paginated Reports – This action has variety of export formats, RLS and Parameter value support.

 

I will be covering the detailed blogpost with all the use cases with this two actions.

Happy Learning!!

ByHariharan Rajendran

Power BI with Teradata Report Considerations

In recent times, I am using Teradata Database and connecting with Power BI Desktop and Report Builder. I want to share my experience so that it might be helpful to others.

My setup – I am connecting the Teradata database through VPN and I don’t have access to use windows authentication to connect with Teradata from my system. We need to use LDAP authentication to connect from system to Teradata database.

Quick Bytes – LDAP is nothing but “Lightweight Directory Access Protocol” and it is helping us to access the directory information services over an Internet Protocol (IP) network. You can learn more information in online.

Power BI Desktop

From Power BI Desktop, we can connect to Teradata either using Windows Authentication or Database Authentication.

If you see my above scenario, i can’t connect with windows or database. Teradata DBA has enabled LDAP for any users who want to connect the database using VPN. LDAP is one of the authentication which is not available as default. We need explicitly make LDAP enable for Power BI Desktop.

Need to install Teradata client driver and then need to run the below command in command prompt.

setx PBI_EnableTeradataLdap true

The will enable LDAP as one of the authentication method in Power BI Desktop as like below.

We can use LDAP to connect with Teradata and can import the tables into Power BI.

Power BI Desktop with Teradata via Direct Query.

As we all know, Direct query is something that will run the query against database when user run the report, query will be triggered and run against database through Gateway. My understanding was, we can use any authentication to connect with database from Power BI Desktop and when we publish to service it is always use the account which we use on the gateway data source.  The below scenario gave me the new understanding on Direct Query with Teradata again via LDAP.

I have used Direct query using LDAP and report and  was working fine in the Power BI Desktop. Once I published the report to Service and mapped with Gateway Service account data source, we got the below error message.

When we see the error message, we can understood something that there is an issue with Gateway service account access but really not.

The file which I use same LDAP with Import mode is working fine on schedule refresh but only direct query is not working in the service. We have done many analysis and finally found that LDAP method which is used on the PBIX file is the problem. It means the Power BI Desktop file with Taradata LDAP is not working in the service with Direct Query.

Finally, we got the database account and it resolved the issue.

Conclusion – We need to make sure to choose proper default authentication method in Power BI Desktop when we deal with direct query in Power BI Service.

If anyone from Microsoft team see this, please let me know – is this a bug or known issue?

Happy Learning!!

ByHariharan Rajendran

Paginated Report works in Power BI Service but Fails in Power BI Report Server

This post explains a tip when you build the paginated report for Power BI Service and Power BI Report Server. Both the version of paginated reports are .rdl files.

Let me start with simple question.

 If you have rdl file published in Power BI service and working there, will the same rdl work in Power BI report server?

Almost, all of us will say YES because we know that there is no difference in RDL file on both the version. This blog post explains, there are certain cases the same RDL will not work in Power BI report server.

I have a paginated report with Oracle as a source and it is working fine in Power BI Paginated Report builder and Power BI Service. I have a data parameter with below name and the same is used in the dataset.

:Start & :End – If you use Oracle in report builder, you need to use “:” for parameters.

The same rdl with Oracle is not working and it ended up with below error in web portal.

Error – An error has occurred during report processing (rsProcessingAborted). Query execution failed for dataset XXXX (rsErrorExecutingCommand).

It shows that there is an error in the dataset. To investigate further, we need to use Microsoft Report Builder (Optimized version of Power BI Report Server / SSRS).

I started to run the query in Dataset – Query Designer and I got the below error.

Error – An error occurred while executing the query. ORA-01745: Invalid host/bind variable name.

The above error conveys something that there is an issue with the parameter / variable which we defined. I have renamed the parameter as :StartDate and :EndDate and it worked.

Published the updated RDL in report server and it worked there also without an issue.

Conclusion – The Power BI Report Builder and Microsoft Report builder is different in-terms of deployment and data source settings also the built-in system codes with data sources. Some of the words are considered as keyword in Microsoft Report Builder but not in the Power BI Report Builder.

ByHariharan Rajendran

Paginated Reports from Power BI Service and Report Server (On-Prem)

This article explains that on-prem report server is the best option for paginated reports as of now as per my experience and I am also recommending you to use the hybrid approach wherever it is possible. You can maintain a hybrid structure like, Power BI Desktop reports in Power BI Service and Paginated reports in On-prem server. This is something that you can prefer where you will get the more stable version of product with more features.

I am sure, Power BI team will make the Power BI Paginated Report as a stable version with tons of feature in future.

I am highlighting few points in which Report Server is getting more score than the Power BI Service.

License – if your organization has Power BI Premium Capacity node then you can get the report server product key and use them for on-prem installation or you can get it if you have SQL Server Enterprise Edition with Software Assurance.

Report Render Time-Out issue  – We often encounter this issue in one of my client. We are using SQL Server and the database and we are handling large tables. If we run the query, it will take at least 5 to 6 mins due to relationship with multiple tables and conditions.

We have done all level of performance optimization but still same performance. If we use the same script in Report Builder it is taking more than 10 mins and same in service taking more than 10 mins and after that it end up with an error. When we check with Microsoft support engineer, they were highlighted that the issue is because of Authentication token expiration but not sure and there is no fix as of now. We tried all the approaches to overcome the issue but finally we end up with Report Server as an alternate solution for these kind of time-out issues. In Report Server we do have control to setup the time-out settings.

Subscription – Our customer had requirement to get the report’s excel export into their email or network drive. Power BI Service has Email option but no network drive as an out of the box feature. We need to use Power Automate to achieve the result which will be difficult for business users. This network file share is one of the default option in on-prem Report Server. Also we do have data driven subscription.

Gateway  – Gateway is one of the component in Power BI Service which helps to connect Power BI reports with on-prem data sources. We always need to map the report with gateway data sources. If you have Power Bi Desktop reports then you can map the dataset once with Gateway datasource and you can replace them every time whenever you do the changes on the report and you no need to map the data source again and again for the same report where as you need to map with data source again and again whenever you overwrite the paginated report (rdl).

Security – In terms of security, we have to leverage only the pre-defined access levels which are Admin, Member, Contributor & Viewer in Power BI Service. These pre-defined access levels are more than enough for most of the cases but on few cases we may need to limit certain access. We have same kind of access levels in report server but we do have control to create our own access with defined controls. It will give more control to manage the access levels.

If you have PII data and you dont want to move the data outside of your network then report server is one of the best option.

Data Source Support – This is one of the feature that I highlight Power BI Report server is best. Power BI Paginated report support only limited data sources where as we have few more additional data sources like ODBC, OLEDB and etc supported in Report server paginated report.  ODBC and OLEDB can cover many data source connections possible with Report Server report. One of the highlight of Power BI Paginated report, it supports Power BI datasets as a source which is not supported in report server.

Management – Power BI Service has workspace concept to maintain the reports for different teams and groups and etc. If you have multiple sections under one main group then we need to maintain multiple workspaces. In Report Server, all are folders, we can create folders, sub folders and can control the access to root folder or sub folder or report.

Additional Features – Power BI Report Server supports below features which is not fully available in Power BI Paginated reports yet.

1. History Snapshot

2. Cache control

3. Central Data Sources and Datasets

4. KPIs

5. Document Map

6. Drill-through reports

7. Linked Reports

Development Tools – Power BI Report Builder is 32 bit and light weight tool and we have same kind of report builder for report server. Both of the tools hanging when we handle report with huge number of rows as a result. If you choose report server then you can use SQL Server Data Tools with Visual Studio and you can create reports and deploy. The development experience will be good when you choose Visual Studio.

Share your thoughts here.

ByHariharan Rajendran

On-Prem SharePoint Folder as a Source in Power BI – Part 2 SharePoint.Contents

Check my previous blog post where I have explained how to connect SharePoint On-Prem folder as a source to access excel or csv files using SharePoint.files function in Power BI Desktop.

On-Prem SharePoint Folder as a Source in Power BI

This article explains how to use SharePoint.Contents function in Power BI to achieve the same function. It is kind of Part 2 version of my previous blog post.

Why you need to care about SharePoint.Contents?

I have used SharePoint.Files on some of my clients to access there excel files in Power BI Desktop, it is working good for few of them but not for others. I got the below error message.

 

I have tried different option to fix the issue, but it was not working so I tried with SharePoint.Contents and it worked well. I may suggest you use SharePoint.Contents first when you access SharePoint files.

Follow the below steps.

Step 1 – Get your SharePoint Site URL where you have your folder.

Step 2 – Go to “Get data” and choose Blank Query.

Step 3 – Click “Advanced Editor”

Step 4 – Get your SharePoint site name and replace it in below script.

let

Source = SharePoint.Contents(“http://<siteurl>.com/sites/<sitename

in

Source

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – It will load all the objects from SharePoint site as like below.

Step 7 – Check for your folder name under Name column and click Table under content column. For example, if my folder name is “Power BI Files” where I have all my excel files are there.. Click the Table and it will expand all the sub folders and then if we click Table again then it will show the list of files from the folder as like below. We need to navigate to multiple levels based on the folder structure.

Step 8 – Filter for .xlsx under Extension column, if you have other documents also placed on the same folder.

Step 9 – Rest of the steps are same as discusses in my Part 1.

The above method is works well when you have multiple files in a folder. It will use the automatically created parameter and function.

What if you want to access only one excel file from the folder? Can I use the same method? Yes. You can use the same method and you can filter only that specific file and follow the rest of the steps.

Sometimes you think that parameters and functions are not required when you just access one single. Yes, you are correct. Use the below Approach to access the single file which will avoid creating and using parameter and function.

Follow the below steps.

Step 1 – Get your SharePoint Site URL where you have your folder.

Step 2 – Go to “Get data” and choose Blank Query.

Step 3 – Click “Advanced Editor”.

Step 4 – Get your SharePoint site name and replace it in below script.

let

Source = SharePoint.Contents(“http://<siteurl>.com/sites/<sitename>/”)

in

Source

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – It will load all the objects from SharePoint site as like below.

Step 7 – Check for your folder name under Name column and click Table under content column. For example, if my folder name is “Power BI Files” where I have all my excel files are there.. Click the Table and it will expand all the sub folders and then if we click Table again then it will show the list of files from the folder as like below. We need to navigate to multiple levels based on the folder structure.

Step 8 – Get the below details.

  • Excel file Name
  • Excel Sheet Name

Step 9 – Open Advanced Editor and you could see few lines of script which you performed above.

Step 10 – Remove the “in” section and add comma (“,”) at the last line of the script then add the below lines. Replace the below red highlighted text into your previous step name. Your step name may be like in the following format #”Path”. Just copy and paste it below script.

#”Filtered Rows” = Table.SelectRows(PreviousStepname, each ([Name] = “YourExcelFileName.xlsx”)),

Navigation1 = #”Filtered Rows”{0}[Content],

Excel = Excel.Workbook(Navigation1 , null, true),

Completions_Sheet = Excel{[Item=”YourExcelSheetName”,Kind=”Sheet”]}[Data],

#”Promoted Headers” = Table.PromoteHeaders(Completions_Sheet, [PromoteAllScalars=true])

in

#”Promoted Headers”

Step 11 – Your final script will look like below. You may have extra lines at the top based on your folder path level.

Let me know if you have any doubts. Happy Learning!!!

ByHariharan Rajendran

On-Prem SharePoint Folder as a Source in Power BI

This blog post explains you the problems and solution with SharePoint on-prem source in Power BI Desktop.

The list of connectors in Power BI Desktop is keep increasing every month. Thanks to Power BI team who working hard to integrate new connectors with Power BI desktop.

Let me explain you the problem or issues that I see every time in forums and community questions.

Problem– Not able to access excel or csv files from On-Prem SharePoint folder. How to access it?

Yes, the question is simple and you may think that we have different methods to access it. Let me specify the methods below and issues.

SharePoint Folder – We can see SharePoint Folder as a one of the source under Get Data which can be used to connect both online and on-prem SharePoint but sometime it is not working properly with on-Prem SharePoint folders.

Have you tried it? If not then try. You may get this below error.

Error – “We encountered an error while trying to connect. Details: “We found extra characters at the end of JSON input'”

Folder – UNC method – This is very good workaround to connect with SharePoint folders. We need take the SharePoint folder Web URL and convert them into local folder type URL by changing the slash (“\”). This works very well in my case. If you are user and you can access your SharePoint folder as windows explorer folder. As you know, you need to choose Folder as a connection type while configuring data source in gateway. It will work when you use your account for configuring the credentials.

In real-time, we will not use individual account on the gateway data source and it will be a service account or the windows account where the gateway clusters are installed. I have received an error when I configure a folder data source with gateway machine account credentials.

Proper access was given to the gateway account but still got the above error message. This might be working for you. If yes, leave your comment below.

Web – If you have single file stored in SharePoint folder then get the file URL and use Web as a source and connect with the excel file. It is very good option when you have single file. How to handle multiple files?

I have tested with multiple clients and above methods are not working because their on-prem SharePoint servers are not a latest version and few other reasons.

How to solve this issue?

I mean how to access the on-prem SharePoint folder files from Power BI Desktop? Solution is simple. Don’t use GUI for this scenario. Use M query function in blank query.

We have below function in Power BI related to SharePoint.

  • SharePoint.Contents
  • SharePoint.Files
  • SharePoint.Tables

SharePoint.Tables is specifically for SharePoint list. We have SharePoint Online List and SharePoint List as two separate connectors. Use one of them and connect with your SharePoint list and check the advanced editor then you will see the SharePoint.Tables function.

Let us come to the topic now. What are the other 2 SharePoint functions and what is the use of those functions and how those function will solve the on-prem SharePoint folder issues?

I will answer for all the questions below.

Let us start with SharePoint.Files function.

Follow the below steps.

Step 1 – Get your SharePoint Site URL where you have your folder.

Step 2 – Go to “Get data” and choose Blank Query.

Step 3 – Click “Advanced Editor”

Step 4 – Get your SharePoint site name and replace it in below script.

let

    Source = SharePoint.Files(“http://<siteurl>.com/sites/<sitename>/”)

in

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – This will list out all the files from SharePoint site.

Step 7 –  You can see the last column folder path and filter your path where you have all your excel files and filter .xlsx and .xls on the extension column based on your file extension.

Step 8 – Choose Content column and right click and remove other columns.

Step 9 – Expand the content column and it will take some time to open your excel file and sheet.

Step 10 – It will create a group with sample file and parameters and the same will be used in the query as like below.

Step 11 – Finally, the table will be loaded with all the columns and you can use it and perform further transformations as per the requirement.

You also can create function and invoke them instead of using automatically created function and parameter.

I will cover the SharePoint.Contents in my next article. Thanks. Happy Learning.

ByHariharan Rajendran

Will Power BI recognize Defined Names in Excel?

Most of you familiar with accessing excel files from Power BI Desktop. Let me as a below question.

Will Power BI recognize “Defined Names” from Excel?

If you take excel expert who use most of the features from excel for analysis, they may create multiple sheets, tables (range of cells) & defined names (range of cells). They may be interested to see all the objects in Power BI. Will Power BI help excel experts to access all the objects?

The above two questions are same but slightly different in context. Yes, is the answer for above two questions.

Let me explain Define Name in Excel first.

Excel – Define Name

Like table, we can define a name for range of cells. It is kind of subset of data from the whole sheet data.

You can find the “Define Name” under Formulas tab.

Select the Define Name, it will ask you to enter the name and range of cells in refer section as like below.

I have defined a name called “Profit” with L column. This will be smaller table with Profit as a column within the table.

You can define multiple names, and all can be referred in your excel calculations. You can check the list of names you defined as like below.

Now, you got a basic idea of Define Name in excel. Refer excel articles to understand more about this feature.

Let us come to our Question again. I said Yes, Power BI will recognize these names. Let me show you.

Power BI

If I connect this excel in Power BI Desktop, you can below objects under Navigation.

  1. Sheets
  2. Tables
  3. Defined Name – as table

In the above screenshot, Country, Sale_Price & Sales are defined names.

All the above defined names are recognized as tables in Power BI. We can use them for report.

Performance

You may get the following question; will it be any performance issue when we use these defined names?

It depends on the scenario. Consider you have a excel with more number of columns and you have defined a name with few columns and measures. In this case, you can consider only the defined name and no need to use the full sheet in Power BI. This will improve the performance.

Try to avoid using the whole sheet and defined names together because defined name is already available in whole sheet and Power BI refresh will take time to refresh as it needs to refresh the whole sheet and defined name.

Happy Learning!!

ByHariharan Rajendran

Automate on-demand Dataset Refresh in Power BI – Self Service BI

This blog post explains how to perform ad-hoc datasets refresh automatically in self-service Power BI model-based organization. Most of the time, Administrators may look for an option to automate this requirement, so this post helps admins who wants to automate the ad-hoc datasets refresh request.

Most of you know about how the self-service BI model works but still I need to explain to make the context clear. In self-Service BI models, administrators will setup the Power BI environment and on-board the users to the power bi platform. In detail, administrators will create workspaces for users and apply security and governance to manage and maintain the environment.

Users will have contributor access to publish the report to their Dev or UAT workspaces. Administrators will help the users to migrate the reports to PROD workspace. Users will not have access to PROD workspace most of the time. User / Business Users will have access to PROD workspace app to see their reports.

In this scenario, Users can ask admin to setup the schedule refresh and datasets will refresh on the set schedule time. In case, if user want to refresh their PROD datasets on-demand or ad-hoc basis then user should request admin to refresh the dataset may be via email.

If admin is available, then manual refresh can happen quickly but if admin is not available or support team is not available for sometime then user should wait until admin refresh the datasets or next schedule refresh time. The turn around time of the request will be longer than expected most of the time. This also another workload to admins to refresh the dataset manually for every user who are requesting to refresh their datasets.

How to automate this ad-hoc refresh? This is will be question for most of the administrators.

This post gives solution with the help of SharePoint and Power Automate.

SharePoint – You can use SharePoint online or on-prem with Power Automate. Power Automate has a default Power BI connector to refresh the datasets by passing the workspace name or id and datasets name or id.

SharePoint Side Steps

You need to create a SharePoint list with below columns.

  1. Power BI Workspace ID
  2. Power BI Dataset ID

It will be difficult to users to enter their PROD workspace ID and dataset ID. They will not have access to their PROD workspace, so admin has to share their workspace and dataset id. As I said, this is automated solution, so this step will not effective way to manage.

But the above are mandatory to pass into Power Automate. To make the process easy to users, admin can create three SharePoint lists.

  1. Power BI Workspace
  2. Power BI Datasets
  3. Power BI Ad-hoc Dataset Refresh – Final

The first 2 lists dependent lists for the 3rd Final List.

Power BI Workspace SharePoint List

This list should have below columns.

  1. Workspace Name
  2. Workspace ID

Enter all the PROD workspace name and ID. Again, this can be automated with the help of Power BI REST API and Power Automate.

Power BI Datasets

This list should have below columns.

  1. Dataset Name
  2. Workspace Name – Lookup column from Power BI Workspace SP list
  3. Dataset ID

Choose the workspace from the drop down and enter the dataset name and ID.

Power BI Ad-hoc Dataset Refresh – Final

This is SharePoint list where you need to provide access to users. This list will have below columns

  1. Workspace Name – Lookup
  2. Dataset Name – Lookup
  3. Workspace ID – Hidden
  4. Dataset ID – Hidden
  5. Status – Default value as “Not Triggered”

I have applied the cascading list option and built the SharePoint list to get the above look and feel (drop-downs). This is possible with the help of script editor webpart.

User can choose their workspace and dataset from the drop-down. Also, admin can enable the security in SharePoint to see only own user entries.

Power Automate

We need to setup the trigger and actions to run the workflow in Power Automate.

Trigger will be from SharePoint – When an item is created in SharePoint list.

Action – it  will be Power BI Dataset Refresh.

By default it will show the list of workspace from your Power BI tenant but here it should be dynamic so you need to choose “Enter custom value” and pass the SharePoint list – Workspace ID value and same on Dataset – Dataset ID Value.

Finally, update the SharePoint list column – Status into “Successfully Triggered”.

That is, it. When user add their entry in SharePoint List then Power Automate will trigger the dataset refresh and update the SharePoint list as “Successfully Triggered”.

The full model screen

Next Step

We still can automate further like send email to users when the status changed on the SharePoint list and we may not sure whether the datasets is successfully triggered or not so we can use Power BI REST API to get the last refresh status and update the same to partner about the status of the dataset refresh.

Share your comments below.

Happy Learning!!!