Some time ago, when Microsoft released the first version of Power BI Rest API I already wrote a wrapper for C# which allowed you to map the object from the API into regular C# objects and work with them locally. However, there have been some major upgrades since then (Actually they were already announced in July 2016 but I did not find anytime to work on this again until now ). Anyway, I just published a new version of my C# wrapper on my GitHub site: https://github.com/gbrueckl/PowerBI.API.Client
To use it, you first need to create an Azure AD application and get an ApplicationID – this is very well described here or can be done directly at https://dev.powerbi.com/apps
A new PowerBI API Client object can then be created using the ApplicationID:
Basically, most of the features described in the API reference are also included in the API Wrapper. So you can now use C# to create your PowerBI model locally and deploy it to the PowerBI service! The only to keep in mind is that the dataset you create via the API can only be sourced by pushing data into it using the Push/Streaming API. As this can be quite cumbersome sometimes, I also added the functionality to publish a whole C# DataTable with basically just two lines of code to publish your reference data/dimensions:
Publish DataTable to PowerBI
// create a regular DataTable – but could also be derived from a SQL Database! DataTable dataTable = newDataTable(); /* populate the dataTable */ // create a PBI table from a regular DataTable object PBITable productsTable = newPBITable(dataTable); // publish the table and push the rows from the dataTable to the PowerBI table productsTable.PublishToPowerBI(true);
This snippet basically deploys the table structure to the PowerBI service and populates it with data from the DataTable:
For your “fact”-data you can also create single rows on your own using the PBIRow-object and publish them manually e.g. for WriteBack-scenarios:
Of course, all the features that were already supported in the first version, are still supported:
Get Embed-URLs of Reports and Tiles
List Reports, Dashboards, Datasets, …
The new version also supports Streaming and PushStreaming datasets in the same way as it does for regular Push datasets. For details on Streaming datasets please take a look at Real-time streaming in PowerBI
I recommend to explore the API on your own by simply building your first PowerBI Push/Streaming Model on your own! For the latest features and improvements please refer to the GitHub repository which will be updated frequently.
Any feedback and participation in the further development is highly appreciated and will be done via the GitHub repository.
In my last post I wrote about how to Debug Custom .Net Activities in Azure Data Factory locally. This fixes one of the biggest issues in Azure Data Factory at the moment for developers. The next bigger problem that you will run into is when it comes to deploying your Azure Data Factory project. At the moment, you can only do it manually from Visual Studio which, for bigger projects, can take quite some time. So I extended and advanced the code from my CustomActivityDebugger. Well, actually I rewrote some major parts of it and moved it into a new GitHub repository: Azure.DataFactory.LocalEnvironment
The new code base now includes the functionality to export an existing ADF project to an ARM template which can then be deployed very easily using Azure standard deployment mechanisms.
So basically, these are the changes and new Features that I made:
Export as ARM template:
Export all ADF objects and properties
Support for configurations
obey dependencies between ADF objects
parameterized Data Factory name
automatic upload of ADF dependencies (e.g. custom activities)
specify the region where ADF should be deployed (ADF is not available in all regions yet!)
Custom Activity Debugger:
simplified usability – just select the pipeline, activity and set the slice-dates
Support for configurations
no need to add any namespaces
no need to add any references
write activity log to console output
General:
Load from the ADF Project file (.dfproj) instead of a whole folder
implemented as Assembly
can be used in a Console Application for automation
will be published via NuGet in the future! (coming soon)
Everything else is described in the Git-Repository itself!
Since the last major update last year, Power BI offers some APIs which can be used to interact with content and also data that is stored in Power BI. Microsoft provides a good set of samples on how to use the APIs on GitHub and also a an interactive APIARY web-UI which you can use to build and test API calls on-the-fly. However, it can still be quite cumbersome as you have to deal with all the REST API calls and the returned JSON on your own. So I decided to write a little C# Wrapper where you simply pass in your Azure AD Application Client ID and you can deal with all Object of the Power BI API as they were regular C# objects.
Here is a little example on how to list all available reports and get the EmbedURL of a given tile using the PowerBIClient:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using pmOne.PowerBI; using pmOne.PowerBI.PowerBIObjects;
Console.WriteLine(“Press <Enter> to exit …”); Console.ReadLine(); } } }
As you can see, its pretty simple and very easy to use, even for non-developers. You can find all the source-code and the sample application for download below. The code as I have written it is very likely not the best code possible, but it works for my needs, is straight forward, simple and saves me a lot of work and time when dealing with the PowerBI API. Also, if the API changes, you may need to adopt the code accordingly. However, for the future I hope that Microsoft provides some metadata so that VisualStudio can build all this code automatically using e.g. Swagger. But for the time being feel free to use, improve or extend my code
Recently I faced the requirement to give specific users of my Analysis Services Multidimensional cube the opportunity to process the cube whenever they want. There are several ways to achieve this: – start a SQL Agent that processes the cube – create some kind of WebService that does the processing – create some custom .NET application that does the processing – run some script (PowerShell, Command Line, ascmd.exe, …)
NOTE: The post describes a solution which requires SSAS Server Admin rights. If you are interested in a solution which triggers an asynchronous processing without requiring SSAS Server Admin Rights please scroll down to the very bottom and download VS_Solution.zip It basically runs the same script but in a separate thread in the context of the service account so the calling user does not need to have any specific rights on the server.
From a usability point-of-view none of the above is really convenient as all options involve some kind of external process or application. In this post I will show how to process a cube using cube actions which can be triggered from Excel Pivot Tables natively. So this is what we want to achieve:
This requires several steps: 1) create custom .Net assembly 2) add that custom .Net assembly to your Analysis Services database 3) add an action to your cube that calls the assembly
Creating a Custom .Net assembly:
First of all we need to create a new “Visual C#” project of type “Class Library”. To work with Analysis Services we need to add two references:
“Microsoft.AnalysisServices” refers to Microsoft.AnalysisServices.dll which can be found in the shared features folder of your installation (default is c:Program FilesMicrosoft SQL Server110SDKAssemblies)
“msmgdsrv” refers to msmgdsrv.dll which can found be in the OLAPbin-folder of your SSAS instance (default is c:Program FilesMicrosoft SQL ServerMSAS11.MSSQLSERVEROLAPbin)
Once these references are added, we can start to create our processing code:
using System; using AdomdServer = Microsoft.AnalysisServices.AdomdServer; using Microsoft.AnalysisServices; using System.Data;
ret.Rows.Add(newobject[] { “S U C C E S S: Object “ + objectToProcessName + ” successfully processed!” }); AdomdServer.Context.TraceEvent(0, 0, LoggingPrefix + “Finished Processing Object “ + objectToProcessName + “!”); } } finally { try { if (server != null) server.Disconnect(); } catch { } }
// if processing was successful a row has been added beforehand if (ret.Rows.Count == 0) { ret.Rows.Add(newobject[] { “F A I L U R E: Error while processing an object!” }); }
There are two things here that are worth to point out. First of all we need to somehow establish a connection to the server that hosts the SSAS database in order to process an object. The first thing that comes into mind would be to create a new connection to the server and run some processing XMLA. Unfortunately this does not work here because as this would result in a deadlock. The assembly will be called as a Rowset-Action which in terms of locking is similar to a query. So if we run separate processing command within our Rowset-Action this processing command can never be committed as there is always a query running on the database. The query (=Rowset-Action) will wait until the function is finished and the function waits until the processing is committed resulting in a classical deadlock!
To avoid this we need to connect to the current users sessions using “*” as our connection string:
//connect to the current session… server.Connect(“*”);
The second thing to point out is the return value of our function. In order to be used in a Rowset-action, the function has to return a DataTable object. The results would then be displayed similar to a drill through and a separate worksheet would pop up in Excel showing the result:
From my point-of-view this can be a bit annoying as after each processing this new worksheet pops up and you loose the focus of the pivot table. So I investigated a bit and found another way to display the output of the processing.
When a drill through / action throws an exception, this will also be displayed in Excel without actually displaying the result of the drill through / action in a separate worksheet. By default it may look like this message:
Well, not really much information here right?
To display more information, e.g. that the processing was successful (or not) we can throw our own exception in the code after processing is done:
I added a separate parameter to control this behavior, in the code above the default would be option 3 – custom Exception.
Once this project is built the DLL can be added to the SSAS database. It is necessary to grant the assembly “Unrestricted” permission set:
The last step would be to create the appropriate cube actions that call the assembly:
It is necessary that the action is of Type=Rowset, otherwise it would not be displayed in Excel! The Action Target is up the you, in this example it will be displayed on every cell. The Action expression is the actual call to our assembly passing the cube name as a parameter. The second parameter controls how the result is displayed and could also be omitted. In this example I added 3 separate actions, one for each result-type (as described above).
And that’s it! This simple steps allow you and your users to process any cube (or any other database object) from within your favorite client application, assuming it supports cube actions.
This is the final result where the three cube actions can be triggered from an Excel pivot table:
The attached zip-file includes everything that is necessary to recreate this example: – SSAS project – ASSP_Processing project
Security is always an important aspect of any BI solution. Especially for big enterprise solutions the security-concept can become very complex. Analysis Services Multidimensional in general offers two option on how to define security: Cell Data Permissions and Dimension Data Permissions. Because of the huge impact on performance Cell Data Permissions are barely used. Dimension Data Permissions are usually the way to go and can cover probably 98% of the security requirements. Though, also Dimension Data Permissions can get quite complex, especially for large-scale cubes with 100+ or even 1000+ users. Just think of an Analysis Services that holds sales data associated to KeyAccounts – further assume that we have 100 KeyAccounts. Each of this KeyAccounts is managed by one user, so we would end up creating 100 roles – one for each KeyAccount and its manager. In terms of maintenance this can get quite complex and in the long run unmanageable.
To address this issue Analysis Services offers the concept of Dynamic Security. Dynamic Security is evaluated at runtime for each user and allows you to consolidate your roles. So for our example from above instead of having 100 different roles we would end up having one dynamic role serving all 100 users. Basically, when a user connects it queries internal data (within the cube using MDX) or external data (using Analysis Services Stored Procedure (ASSP)/Assemblies) to get the security settings for the current user. Especially in combination with ASSP this can be quite powerful as you can use any datasource that you can query using .Net-code to derive your security from.
To make a role "dynamic" you basically have two options: 1) Username() – returns the current users in format "MyDomainMyUser" 2) CustomData() – returns whatever was specified in the connectionstring’s CustomData-property
Both are native MDX-functions and can be used in an expression to build your Allowed Set, Denied Set or DefaultMember.
This works just fine, as long as the expressions returns a valid MDX set/member of course. But what about the Visual Totals – setting? You can only check or uncheck the checkbox and you are not able to use any dynamic expressions. That’s what the UI offers you – no more no less. But there might be some rare scenarios where you also need to make the Visual Totals dynamically based on Username() or CustomData() – and this is what this post is about.
So, as we have already seen above, it is not possible to make the Visual Totals dynamically using the UI. Fortunately there are also other ways besides the UI to work with the cube and modify its structure. I am talking about XMLA (XML for Analysis Services) here, which is the protocol that is used for all communications between a client and Analysis Services. Instead of writing XMLA on your own there is also a programmatically way to do this: AMO (Analysis Management Object). AMO is a .Net library that allows you to integrate calls to Analysis Services within any .Net code. After modifying any object in the code AMO generates the according XMLA for you and sends it to the server which is a much more convenient way than writing plane XMLA on your own.
This means that you can also create/modify your security roles using AMO – this approach is already very well described by Benny Austin in his blog about SSAS: Using AMO to Secure Analysis Service Cube. I recommend reading this article before you continue here.
For this little example I create a slimmed down version of Adventure works. The model contains only the Date and the Product dimension. We will put our dynamic security on the Category attribute of our Product dimension. The model also already contains a predefined empty role called "DynamicVisualTotals" that we will modify later (after the model has been deployed) using AMO. Please note here that AMO only work with the Analysis Service directly (online) but not at design time in Visual Studio. Once everything is deployed this is the AMO code to use to modify the predefined role:
// can be done in the UI oAttrPermission.AllowedSet = "DynamicSecurity.DynamicAllowedSet(CustomData())"; // can not be done in the UI oAttrPermission.VisualTotals = "DynamicSecurity.DynamicVisualTotals(CustomData())";
oDimPermission.Update(); } } } } }
1) connect to server "localhost" 2) connect to database "DynamicVisualTotals" 3) get the Role-object for role "DynamicVisualTotals" from the database 4) get the Dimension-object for dimension "Product" 5) get the DimensionAttribute-object for attribute "Category" 6) get the DimensionPermission that is associated to our Role for dimension "Product" 7) get the AttributePermission that is associated to our attribute "Category"
So far this is straight forward. The AttributePermission object actually represents the UI that we have seen above. It has properties for all the fields and textboxes on the UI. The most important of course are: – AllowedSet – DeniedSet – Defaultmember – and VisualTotals
For the first three I was already aware that they are of type String but the VisualTotals I expected to be Boolean which turned out to be wrong – it is also a String!
Naïve as I am I simply put a MDX expression in there that resolves to "1" or "0" (Boolean, similar to the Checkbox in the UI, it could probably also resolve to "true" or "false" or a Boolean type directly but I did not further test this) – and, it worked like a charm! The MDX expression I used refers to an ASSP called DynamicSecurity which contains a function called "DynamicVisualTotals" that returns "1" or "0" based on what was passed in the connectionstring using CustomData():
exp = newExpression(mdxEpression); s = exp.CalculateMdxObject(null).ToSet();
return s; } } }
(This ASSP has to be compiled and added to the database first to make the Dynamic Security work. This is describe here in more detail.)
And that’s it!
Once everything is done 1) Deploy the solution to the sever 2) (add ASSP if not already included in the solution) 3) Execute the AMO code (first listing) we can use Excel to check the results:
Please note that XMLA always uses the internal IDs which may not be the same as the name of the objects!
In this post I showed how to use AMO and XMLA to modify a security role and use an expression to make the VisualTotals setting dynamic which is not possible using the UI only. This is just a very simplified example but I used this approach on some of my enterprise deployments and it works just fine also for very complex scenarios.
The attached zip-file includes everything that is necessary to recreate this example: – SSAS project – ASSP project – AMO project (and XMLA script) – final Excel with results
). Anyway, I just published a new version of my C# wrapper on my GitHub site: https://github.com/gbrueckl/PowerBI.API.Client
