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, …


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


Step 3. Save the file to the Library



Step 4. Next Create a Web Part Page


And add a Business Data WebPart


Point to the Excel in the Report Library.


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


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.


2. Using PowerShell – Query

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



3. Excel – Query

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





4. Access – Pass-Through Query

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




5. MS SQL Reporting Services

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



6. MS PowerPivot – Excel Addin

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






7. MS Power BI

Re-using the complex query using MS Power BI



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

PowerPivot – Connect to an SSRS Report

September 17, 2014

SSRS is a very nice reporting platform that comes along with MS SQL.

Using the free Report Builder client you can make some very nice reports and publish or schedule them to run on your intranet.

But what if you want to reuse valuable information in the reports ?

Well we can you use MS PowerPivot to access the data over the internet on you client.

Open PP and choose the in the menu From Report.

Next point to the URL on you Reporting Intranet or SharePoint if you are running in mixed mode.


When you click next it will grab the TABLIX data grid in your report.


As easy as that.

This is how the bits and pieces fall together in your MS BI environment. Smile

PowerShell – Run Assembly that is not registered in the GAC

September 5, 2014

As demonstrated in previous posts you can easily compile your own .Net Assemblies.

Using AutoIT to create a Wrapper for compiling an EXE or DLL using .NET.


Or  your own COM objects

But now it is time to see once we compiled the Assembly.dll, and how we can run it in PowerShell. Without registering it the GAC !

Here is some example code to create a .Net assembly.dll

Public module HelloWorld

 Sub main()
 Console.WriteLine ("Hello World using Visual Basic Code!")
 Msgbox("Hello World from My DotNET Assembly !")
 End sub
End module

Save this code as a Hello World.vb file.

If you compile it using the above Wrapper, choose the Class Library button to generate the DLL.

Once this is successfully processed you will see the Assembly.dll that is created.


Using general .NET practice you would need to register the DLL to the GAC in order to access the Methods and Properties. But for scripters like us this is not practical Winking smile

So we want to distribute the DLL across PC’s and Servers by just copying the file around.

So this example shows you have to access the DLL from within PowerShell.

It pops up a message box and wrote something to the console


By the way since PS v3.0 the IDE you can use the intellisense feature on you newly created Assembly.dll



# Notice the double backslash !
$filename = "C:\\_\\Apps\\Hello World.dll"
$assembly = [Reflection.Assembly]::Loadfile($filename)

echo '--'
echo $assembly.gettype()
echo '--'
echo $assembly.fullName
echo '--'
echo $assembly.gettypes() | ?{$_.IsPublic} # List Public Functions
echo '--'

# Call the Class / Methods

# Clean-up
rv assembly, filename

As simple as that, isn’t that great ! 🙂

So one more missing link to make the circle round. What if we could access the .NET Assembly from within Autoit running this PS code that would be just great.

Coming soon stay tuned !


PowerPivot – Accessing meta cube data using the DMV commands

August 17, 2014

If you are a bit familiar with MS Analysis Services Cubes and MDX queries.

Well PowerPivot uses a built-in cube to store the data.

Well we can use the Dynamic Management Views commands to see some of the inner-side of the cubeSmile

Here we go :

1. First step go to the Excel and connect to a database source like MS SQL or SharePoint

2. Next create a PowerPivot Data Set of the data source and create a list or a Pivot Table of that data source.

3. Go to Data -> Connections –> Properties


To see the connection properties

Connection string : Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue


4. Go to a blank sheet and create a new Connection from there.


Go to Properties


5. Create new Connection settings using some DMV commands


select * from $system.discover_schema_rowsets

This will list all the available commands


Some more examples here below :

select * from $system.MDSCHEMA_CUBES

This will list all PowerPivot Cubes in your Excel file.


select * from $system.DBSCHEMA_CATALOGS


select * from $system.discover_storage_tables


select * from $system.discover_object_memory_usage


Read more how to format this nice example above, here :

Chris Webb has more interesting tips on his blog.


If you install the Excel OLAP extension.

You can access the MDX for reading the data from the PP Cube.




It seems that you can get access to the cube as well using MSOLAP data source using the AMO library and scripting.

I will try to publish an example later on Smile