Using the On-Premise Gateway to connect to your AX2012 data to Power BI Portal

PowerBI has been around for a long time by now, so there are tons of information out there on how to connect your data sources to the powerful PowerBI Portal (www.powerbi.com). Now, getting all the moving parts to connect properly might have been difficult at times, but I'm making this post to just reassure you it is currently very easy to set up.

Before I begin, I just want to add a precaution:
Consider the implications around security and performance when setting this up.

I prefer to use a common service (or setup) account for this, and not my own consultant login. This makes it a little easier if someone else needs to step in and maintain the setup. Furthermore, it allows for the customer to lock down the credentials after I've completed the setup.
As for performance, you should pay attention to how data refresh adds load to your servers, both the one hosting the gateway itself, the server hosting the data source (SQL Server and/or Analysis Services). You don't want to cause a full system overload while pulling data from your sources.

I will use the standard Dynamics AX SSAS OLAP as an example, but the point here is less the data source, and more how easy it is to connect to the PowerBI Portal.

Before we begin, I want to list some prerequisites, or at least how I would set it up:

• You are using a dedicated setup account and this account is a domain user
• You are local admin on the server where you plan to setup the gateway. Basically, your setup account is listed in the Administrators Group (under Computer Management, Local Users and Groups, Groups, Administrators).
• You have access to the SQL Server Analysis Services (SSAS) with your setup account. Check by right-click SSAS instance, choose Properties and look at the list of users under Security.
• You have a user who is Global Admin in Azure AD. This could be the setup user, synced to Azure AD from the On-Premise domain, but it's not necessary. The point is this user will have access to setup things on PowerBI which currently requires Office 365 Global Admin rights. This may change in the near future, hopefully.

Given all of the above, you'll simply start by logging on the PowerBI portal using the Office 365 Global Admin user, and download what's called the "Data Gateway". The download link is in the top and takes you to the download page. Press Download and get the latest and finest version.

When you run this installer, it will ask you to login using the Office 365 Global Admin user (which will have access to register the gateway). Also, I am using the "Enterprise Gateway" option when installing. This allows me to schedule refresh from data sources based on SSAS.
The gateway has its own set of prerequisite software, so have a look at those before you begin.

When the gateway is installed successfully, it now can be utilized to connect to ANY of the SSAS instances on the domain, given the network traffic is allowed and you connect with a user who has access to the SSAS instance. So your LIVE, TEST, DEV, and so on. How cool is that?

Next you would use the PowerBI Admin Portal to configure the Gateway and add your data sources.
Head over to the Manage gateways and click "Add Data Source".

Fill in the form. Notice I am using the name of the server where SSAS is running and the name of the SSAS instance. I also use the domain user who has access to the SSAS Server itself. I also put in the name of the OLAP, Dynamics AX Initial.

The data source should connect and confirm everything looks good for you to connect the data source and whatever it contains. Great!
A lot of people get here fine, but the next part is something which was added just recently, well actually some months ago in the 2016 April update.

Why is this update important?

Given the scenario where you're trying to connect some on-premise SSAS with PowerBI in the cloud, who's to say you're fully synchronizing on-premise Active Directory with Azure Active Directory? What if your local domain doesn't map the users perfectly with the usernames in Azure AD? This is where the "Map User Names" comes into play. We can actually add string replace rules to the usernames, so if your users are not perfectly mapped between Azure AD and On-Premise domain, you can still get this to work.

So in this example, I will assume the On-Premise domain is using a different domain name compared to the one used by Office 365 and Azure AD. On-Premise I imagine CONTOSO is actually fully qualified as contoso.eu.local, while in the cloud users are using contoso.com.

Click the Data Source you need to be mapped. Right now, these settings are not shared across data sources, but hopefully they will add further administrative improvements to this.
Open the list of Users and look at the bottom for the Map User Names button.

This will slide in the setup for mapping of user names.

Notice in my example I am replacing the long username for the powerbiadmin@contoso.com with service-account-with-access-to-ssas@contoso.eu.local. So anytime I am logged in at the PowerBI portal with this powerbiadmin-user, and I try to access the data sources through the gateway, the user principal names will be "washed" through the mapping, and "magically" the credentials for that user will work On-Premise because the local domain sees a user it recognizes. Furthermore, I added another example of a user who locally is represented by u12345@contoso.eu.local, while in Azure AD is actually tommy@contoso.com. So if this user also tries to update or refresh data sources, the credentials will work locally.

What next?

Well, you can click "Get Data", select "Database" and choose "SQL Server Analysis Services" and simply pick your preferred cube from one of your datasources and click "Connect". With the new dataset in place, you can schedule a refresh outside regular business hours. Like this:

A couple of follow-up questions:

Q) What happens if I map two completely different users, who actually both exists both in Azure and On-Premise?
A) You're the admin and while there are no features to prevent potential illogical mappings, you can map yourself into complete chaos - at your own or someone else despair.

Q) Do I need to map all users like this? 
A) Since the mapping is a simple string replace, you can replace similar parts of the username. Like replacing "@contoso.com" with "@contoso.eu.local". If you're lucky enough, this will be enough to fix most usernames. Also consider there may be a number of users who only will load the Reports, but who do not need access to actually reload the datasets with fresh data from the data sources. Surely, those users do not need to be mapped.

Q) How much time does it take to set this up?
A) With some practice, and if the users are setup with permissions like described in the beginning of this post, I bet you can get this up, connected and working within the hour. The rest will be waiting for data to come through so you can start fill your beautiful reports and dashboards with powerful data.

Q) What if it fails horribly and I get stuck? :'-(
A) Use the community forum and make sure to tag your question with "BI".

Login failed error while processing OLAP cubes

I was requested to help with solving a processing error from SQL Server Analysis Services today.

While processing the engine fails to retrieve data from the Dynamics AX transaction database and throws the following error:
Login failed for user "DOMAIN\SERVERNAME$". Reason: Could not find a login mathcing the name provided (CLIENT: )"
(DOMAIN refers to the actual Active Directory Domain Name and SERVERNAME$ refers to the name of the server.

From the error it seems like the machine account is unable to login. Now in my scenario the SSAS service runs on the same server as the SQL Server Engine and the instance with the Dynamics AX database. In addition the SSAS service is NOT running using a dedicated Domain Service Account, but rather a local service account. The error then becomes sort of misleading if you read off the account name, because it is actually referring to machine account name.

The solution is simple, though.

Open the SQL Server Configuration Manager, find the Analysis Service you are using when processing the OLAP.

Open the properties and copy out the "Account name" which the service runs under. Normally this would be something like "NT Service\MSOLAP$INSTANCE_NAME" (INSTANCE_NAME refers to the name of the SSAS instance).

Now open SQL Server Management Studio, open Security and Logins. Add a new Login and paste in the Account name from previous step as the Login name.

Before you save this new login, open "User Mappings" and find the Dynamics AX database you are trying to use as source for your OLAP. Tick the checkbox in front of the database name and grant the login "db_datareader" role membership.

Now save the Login and jump back to the processing step and try run it again.

Setting up scheduled processing of cubes in AX2012

Here is a quick guide on how you can easily and quickly setup scheduled processing of the cubes for Microsoft Dynamics AX 2012. The same approach works for other Analysis Databases, but sometimes it helps to put things in context in order to make it easier for you AX geeks out there.

I'm assuming you have setup and configured the standard Dynamics AX Analysis Database. Preferably also run a manual processing and analysed any errors or warnings. My example is from AX 2012 R2 on SQL Server 2012, and I just want to have it process without any error logging.

Start off with logging on to the Analysis Instance using SQL Server Management Studio. Find the "Dynamics AX initial" OLAP Database. If you have created multiple partitions, they will have their separate OLAP Database.

Right click and choose Process to open the processing dialog:

In the process dialog click the "Change Settings..." button:

A new windows will pop up. Head over to the tab "Dimension key errors" and click "Use custom error configuration". Choose to "Ignore error count" and define error conditions to "Ignore error". Here you can opt in for "Report and continue" and also have any issues be reported to a Log file, but do a test and pay attention to the Log filesize. It may grow rapidly, and even become several GB. 

Press OK, but don't start the processing. Now with the current setup, choose to Script the Action to a New Query Window:

The Action will be scripted as a XML structure. We will now use this command in a SQL Server Agent Job.

Head over to SQL Server Agent and create a new Job:

On the section for Steps, add a new Step:


The new Step needs to be of type SQL Server Analysis Service Command. It will be run under the SQL Server Agent Service Account, so make sure this service account has Read access to the Dynamics AX Transaction Database. Paste in the XML command from previous steps and save the step by pressing OK:


Now you can chose the Schedule section and create a new schedule for this job. You may chose to have it process on a daily basis, or multiple times per day. Choose whatever is suitable for your needs.

Finally, do a test of the Job and make sure it runs through. If it doesn't, use Job History to find the cause of any errors. Feel free to comment or ask, if you get stuck.

Thanks for reading!

Assigning user groups to the Analysis Database Roles using PowerShell

One of the steps involved when installing a full-fledged Dynamics AX 2009 installation is the setting up the security schema for OLAP database. If you do not grant the domain users access to the predefined roles, they will not be able to load the Analysis Reports and Role Center KPI's. When you run the reports and test the Role Centers as administrator, you are essentially bypassing these security checks. You need to test using a regular user to make sure everything is working as expected.

In most cases I end up with simply granting either Domain Users or one specific user group access to all the predefined roles. If you really want to harden security, you would create one Active Directory Group for each role and add users to those roles.

The manual solution

Now adding user groups to each of these roles is a tedious task. The procedure is described on MSDN, but I'm going to repeat the steps here:

1. Open SQL Server Management Studio
2. Connect to the Analysis Services
3. Open the Dynamics AX OLAP Database
4. Open the Roles
5. For each role, repeat these steps
1. Right-click and choose Properties
2. Open Memberships
3. Click Add
4. Click Object Types and make sure "Groups" is marked. Press OK.
5. Click Locations and navigate to the Active Directory you will search in. Press OK.
6. Write on the name of the group you want to add. For example "Domain Users" is a system group that includes all registered domain users.
7. Click Search Names so it validates your input.
8. Click OK
9. Click OK again in the Edit Role Dialog. 

There are 26 roles in my test environment at the moment. Having to add these Memberships will take me about 10 minutes or so, but is extremely boring. 

A simpler solution - PowerShell

Just recently I was about to do this one more time, but then I started thinking about solving this with PowerShell.
PowerShell will be an important part of Dynamics AX 2012, since most of the installation, maintenance and configuration will available with PowerShell commands. So I might as well start to get PowerShell in my fingers.

I searched on the Internet and came across this article by Darren Gosbell about replacing group members from roles in SSAS. By taking his example, I made some minor modifications and here is my script for adding one group to all roles:

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") 

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost") 
$axssasgrp = "CONTOSO\Domain Users"
foreach ($db in $svr.Databases)
{
    # Print the Database Name
    "Database: " + $db.Name
    If ($db.Name -eq "Dynamics AX")
    {
        foreach ($role in $db.Roles)
        {
            "   Role: " + $role.Name    
            $foundMember = $null
            foreach ($member in $role.Members)
            {
                if ($member.Name -eq $axssasgrp)
                {
                    $foundMember = $member
                }
            }
            If ($foundMember -eq $null)
            {
                "      Adding access to " + $axssasgrp
                $newRole = New-Object Microsoft.AnalysisServices.RoleMember($axssasgrp)
                $role.Members.Add($newRole)
                $role.Update()
            }
            else
            {
                "      Usergroup " + $axssasgrp + " already added"
            }
        }
    }
}
$svr.Disconnect()

Now you might need to run PowerShell as administrator for this to work. Change the server name and user group name to make it work in your scenario. I never change the name of the Dynamics AX OLAP.

For cleaning up I also made another script that removes a usergroup. This script looks like this:

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") 

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost") 
$axssasgrp = "CONTOSO\Domain Users"
foreach ($db in $svr.Databases)
{
    # Print the Database Name
    "Database: " + $db.Name
    If ($db.Name -eq "Dynamics AX")
    {
        foreach ($role in $db.Roles)
        {
            "   Role: " + $role.Name    
            $foundMember = $null
            foreach ($member in $role.Members)
            {
                if ($member.Name -eq $axssasgrp)
                {
                    $foundMember = $member
                }
            }
            If ($foundMember -ne $null)
            {
                "      Removing access to " + $axssasgrp
                $role.Members.Remove($foundMember)
                $role.Update()
            }
            else
            {
                "      Usergroup " + $axssasgrp + " not found in role"
            }
        }
    }
}
$svr.Disconnect()

Now I would like to improve this script by adding the ability to read groups from a CSV-file where you could differentiate what user group gets added to what role. I am just learning PowerShell, so if you are a hard core PowerShell scripter and see ways to improve this script, please let me know. 

How to run these scripts

I have only tested this on Windows Server 2008 R2 and PowerShell has its shortcut right on the QuickLaunch. Right-click and run it as administrator. Edit the script so user group reference is correct. If Analysis Services is running on a named instance you will need to modify the connect statement with servername\instancename. Copy your final script and from the top left in the PowerShell window, find the Paste command. After the script is pasted, just press a final Enter and let the script do its work.