PowerShell – Accessing Power BI Desktop Data and more…

November 18, 2018

Recently I got interested in getting data out of a Power BI Data Model to push into an SQL database.

On my way I discovered a lot of interesting techniques and Tools for Power BI Desktop.

1. First of all you need to know that Power BI Desktop actually is a small Web Server running an SSAS Mini Cube.

2. Next you need to prepare PowerShell to access the PBI Cube.

Solution :

In order to start you first need to start PBI Desktop and open a PBIX file you created before.

Once started you need to find out on which Random Port is was started.

There are 4 way to get the job done.

1. Install Dax Studio which you need to have, no question about this !

    Even if you are not using it for getting the Random Port…

Look in the bottom right corner :

image

2. Command line tool NETSTAT

   Run NETSTAT –b command and look for Power BI

image

In this case it was Port 60520

3. Look for the msmdsrv.port.txt file.

Once PBI is started it will create some folders and files.

You can find those here :

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\

image

If you have multiple PBIX files open you will see more Random created subfolders.

Go to the random created folder and next to the Data Subfolder.

Open the msmdsrv.port.txt file to get the Port number.

image

4. Using the Tabular Editor

But it needs some dependent MS AS AMO Libraries

image

image

Now that you know how to get access to the Web Server Port.

PowerShell 32 Bit

You can now test PowerShell connection to the Power BI Desktop you have open.

But you first need a Connection String to be used in PowerShell

FIRST USE the 32 Bit PowerShell if you are using a 32 Bit Office installation.

Later in this post I will explain how to get it running in a 64Bit PS even if you have a 32 Bit Office installed.

I suggest that you use EXCEL for this to copy and paste it Winking smile

Go to Data and choose From Analysis Services.

image

Follow these steps :

image

image

image

image

Copy it from here :

image

You can also get the Catalog GUID from the folder name where you could find the Port number.

image

Run this PowerShell Script, it uses a DMV Query to get hold of the PBI Cube Properties

CLS

$Port = "60520"
$Catalog = "cff5b9a8-2f87-416c-97fd-8eccd5962d51"

$Query = 'Select * from $SYSTEM.DISCOVER_PROPERTIES'

$connectionString = "Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:$Port;Initial Catalog=$Catalog"
 
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection

$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset)

$dataset.Tables[0] | Format-Table # export-csv $filename -notypeinformation
   
$connection.Close() 

image

To list data from a Table use the EVALUATE command

$Query = 'EVALUATE Tickets'

image


PowerShell 64 Bit

If you are running a 32 Bit Office, you need to install the OLAP Drivers for 64 bit

(will be done when you install DAX Studio).

clip_image002

Or manually register the MSOLAP.DLL from the Power BI install folder.

image

Tools :

Power Pivot :

You can connect to a Power BI Desktop model using Power Pivot for example.

image

image

Dax Studio :

You can run commands directly against your Power BI Desktop.

Use DAX Commands

image

Or use a DMV Query.

image

For more info on DMV Queries see here

Power BI Helper :

image

More Tools you can find here

Enjoy !

Advertisements

PowerBI – Using FreshDesk Web Services API

November 7, 2018

We started exploring the use of the FreshDesk cloud-based customer support platform.

Which is one of the modules of the FreshWorks Suite.

You can start off with a free subscription, which is good enough to start building you BI tool.

image

Once you are happy you can switch over to a paid subscription giving you extra features.

image

One of them is extensive reporting,

After exploring the Power BI integration. I found out that all of the third party solution provided did not work Sad smile

And one if the solutions required that you have a Power BI Pro licence…

SOLUTION :

So I ended up making my own Power BI Dashboard using the FreshDesk REST Web Services API.

See here for the API documentation : https://developers.freshdesk.com/api/

The Web Service API looks very straight forward and works with any REST compatible application.

Implementing this in Power BI, was challenging.

But as you can see here, you can do all you need in your Free Power BI Desktop too.

So this will save you some licenses money too Winking smile

You can start off with a free subscription, which is good enough to start building your BI Dashboards.

image

image

Shows that Power BI is the right marriage for the 2 platforms, O365 and FreshDesk Winking smile

In case you need to run this as offline data source or want to schedule reports.

You can use PowerShell to extract the data, and use this as a data source.

This will save you quite some money uplifting your subscription.

image

Example :

If you have 25 Agents and you need to schedule reports. Your subscription is $19 / user / month more expansive.

This is a burden of an additional $5,000 subscription cost.

Other paid service provider is FreshInsights / Radiare

image

PowerShell

If you run the Invoke-WebRequest command against your URI in Powershell.

Like this :

$Data = Invoke-WebRequest -uri $URL -Headers $Headers -Method GET -ContentType application/json 

image

You can see in the response header information the API Version and the remaining calls you have

In this case 2996 out of 3000 per hour !

This is a limit set by Freshdesk to reduce the maximum API calls per hour.

Steps to get started using Power BI :

1. create a free trial subscription

2. Copy your Web Service API Key

image

3. Open Power BI and do a Get DATA Web Data

image

image

Paste in the web service URL for Tickets for example.

4. Connect to FreshDesk using the API Key and password

image

5. Next built your Queries and transformations to extract the data

image

6. Build your Visuals and Measures

image

Need to get started building your own Dashboard, drop a message here.


SharePoint–Online Publish a Power BI report (Personal Ed.)

January 5, 2018

Ever wanted to publish a Power BI report to SharePoint Online ?

Which is not out of the box functionality (It is for the PRO version of Power BI)

 

Solution :

Step 1. Get the link of the report

In your Power BI environment go to the report to publish –> File –> Publish to Web

image

Next copy the link code ….

image

Step 2. Create a Web Part Page

 

Add a Page Viewer Web Part

image

And add the Report link from Step 1.

 

Step 3. Save the page and see the result

 

image

 

Of course this does not give you all the full features of the Power BI Pro version.

But can be good enough to do some prototyping or publishing BI information.

image

Bonus is the fact that it will update automatically based on the Power BI Schedule frequency Smile

 

PS : Keep in Mind that your Tenant Admin needs to give you permission to Publish to the Web

 

image

 

Enjoy!


SharePoint–Online Publish a Power Pivot Excel file using a SalesForce Datasource

January 5, 2018

I was wondering if it was possible to use the (older) Power Pivot that had a SalesForce Datasource.

And publish it to SharePoint Online ?

Step 1. Connect to SalesForce using Power Query

Once the data source is filtered, formatted, …

image

Safe the Excel file… it stores automatically the data as a Power Pivot Data source.

Step 2. Create a Report Library

In your O365 SharePoint Online create a Report LibrarySs

image

Step 3. Save the file to the Library

image

image

Step 4. Next Create a Web Part Page

image

And add a Business Data WebPart

image

Point to the Excel in the Report Library.

image

Enjoy !


MS SQL – Query & Reporting Tools

November 11, 2017

Getting data out of a Database hasn’t been easier these days. Giving all the tools you have at your disposal…

Giving the fact that all the fuss about BI and Cloud Storage, Big Data etc. We seem to loose feeling with all the o

image

Here are a few examples we can use for simple and complex query and reporting purposes.

1. Query using the MS SQL SSMS

It’s obvious that SQL SSMS offers all the functionality you need to get the data out of the database.

This example shows a combination of Common Table Expression (CTE) Query combined with the PIVOT function, to generate you dataset.

image

2. Using PowerShell – Query

Re-using this quite complex Query using Scripting language is quite Powerful.

image

image

3. Excel – Query

Using Excel combined with MS Query we can re-use the same Query

image

image

image

image

4. Access – Pass-Through Query

Re-using the complex query using MS Access in a Pass-Through Query Statement.

image

image

image

5. MS SQL Reporting Services

Re-using the complex query using MS SQL Reporting Services & Report Builder

image

image

6. MS PowerPivot – Excel Addin

Re-using the complex query using MS Power Pivot – Addin

image

image

image

image

image

7. MS Power BI

Re-using the complex query using MS Power BI

image

image

This is not a limit list of tools you have a hand. There many more which you might overlook …

QlikView Client

MS Power Query

– …

For getting data out of a database you need to the proper tools, but this is not a constraint at the moment.

What is, is being able to manage all these different applications and technologies.

Bottom-line is that one you spend efforts in getting your queries right you can re-use them most any tool that comes around Smile


PowerBI – Number and Currency Formatting

October 31, 2017

After looking for a solution why my Number Formatting did not want to work out…

After creating a new Measure it returned the classing US based formatting.

image

While I wanted to have the EU formatting in my table.

Power BI Desktop Client Regional Setting :

Even after changing the Power BI Desktop Client settings, it did not take effect Sad smile

image

Solutions :

Go to Control Panel –> Region –> FORMAT

image

If this format is not in line with your region it will overrule the Power BI Regional settings ?

image

So after applying the correct region format settings on OS level, everything appeared as expected Smile

PS : These figures are just demo based Winking smile

Enjoy!


PowerBI – Access SharePoint list data

February 5, 2017

As I showed in a previous post SharePoint is different Interfaces you can use to access as a data source.

https://audministrator.wordpress.com/2015/01/26/sharepoint-interfaces/

So I we know it can expose it’s List data using the  REST or oData protocol.

Open PowerBI and go to GET DATA menu.

Choose OData Feed to connect to your SharePoint data source

 

image

Once logged you can choose from your lists

 

image

And play around to make your data visible using all the features from PowerBI Smile

image

Once Finished you can publish your result

 

image

Once published you can reach out on the mobile apps.

 

image