Friday, April 4, 2014

Power BI - Schedule Data Refresh for Analysis Services Cube

Currently Microsoft is supporting schedule auto data refresh for SQL Server but not for Analysis service cube. We can schedule auto data refresh for analysis services in Power BI by doing a work around.  Work around would be pulling the SSAS cube data by creating a linked server. 

Please follow the below steps in creating linked server and writing a MDX query using open query after creating linked server successfully.

Step 1:
As it supports schedule data refresh for SQL Server, we are going to use SQL Server connection in our data model. Please create a SQL server db connection in excel Power Pivot data model.

Step 2:
Create a linked server in the DB server with which we created a power pivot data connection in Step 1. Please look into the below screen shot where we can create a new linked server.  DB Server -> Server Objects-> Linked Servers

Please fill your SSAS server and DB details in general tab as shown in the below screen shot. I named my Linked Server as "SSAS_LINKEDSERVER".

Please select the Security option as show in below screen shot.

Please select the Server Options as default as shown below.

Once you click OK, you will see a linked server under Server Objects -> Linked Servers. 

Step 3:
After creating linked server connection successfully , we need to write a MDX query using Open Query method to pull the SSAS cube data. Please use the below code snippet to do that. We need to use linked server name "SSAS_LINKEDSERVER", that is created in step 2. Make sure your open query runs in the SQL server management studio. OpenQuery has 2 parameters - 1. Linked Server name 2. Query to retrieve the data (in our case it is MDX)


--Your MDX query to pull the SSAS cube data



Step 4:
Once your open query pulls the cube data successfully. Please use this query in your power pivot data model to pull the data as shown below.

Step 5:
Once your power pivot data model is ready, we can create Power View / Power Pivot reports on top of your data model. Try to refresh your data model once in excel and make sure it is refreshing before we upload it into Power BI site.

Step 6:
Now we are ready to upload our excel file having (Power Pivot data model + Power View / Power Pivot visualizations) to Power BI for Office 365 site as shown below.

Step 8:
Now we need to set up auto data refresh for the file we uploaded in the step 7. For that, first we need to create Data Management Gateway (DMG). Please follow this url to create a DMG.

Step 9:
Once we create a DMG, we can set-up a auto data schedule as shown in below screen shot. Please click on ellipsis(...), then select "Schedule Data Refresh" option.

Once we select schedule data refresh, you will see below screen, please clink on the "ON" button that is marked.

We will see list of data connections that are there in the excel we uploaded, in our case DB connection will appear. Please select that and configure the schedule.

Once we save the schedule and after runs, we can see the history. Status tells us whether schedule refresh is failed or not. If it is failed, it will send you an email notification based on your schedule configuration that is done in previous step.