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)
EXEC ('SELECT * FROM OPENQUERY(SSAS_LINKEDSERVER,''
--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.
http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/create-a-data-management-gateway-HA104093659.aspx
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.
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)
EXEC ('SELECT * FROM OPENQUERY(SSAS_LINKEDSERVER,''
--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.
http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/create-a-data-management-gateway-HA104093659.aspx
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.
8 comments:
Nice post. It was really helpful. Thanks Raghav...
Thanks Kiran.
Hi Raghav,
can you give me a suggestion:
I have created a tabular model on SSAS.
In order to publish a Power View report on PowerBI, if I well understood, I have to import the data from the tabular model into a Power Pivot in excel and then build the Power View on the Power Pivot data model.
There is no way to build a Power View report directly agains the SSAS tabular model and then publish it to PowerBI. Am I right?
In that case: is there an easy way to import all the data and the structure of the SSAS tabular model directly in Power Pivot?
Thank you,
Roberto
Hi Roberto,
To publish any report to Power BI, you need to have data model in Power Pivot. In your case, you can write mdx and get the cube data into power pivot data model. OR you can create different pivot tables and add these pivot tables to power pivot data model and on top of it, you can create different visualizations and publish them to Power BI. Let me know if you have any questions on it. Hope I answered you questions.
Thanks,
Raghav.
If I understand you correctly this means that you can only build a PowerView on top of a PowerPivot model?
If so why would you then choose to connect to SSAS and not directly to the database/source - to me it apears as double Work Building an SSAS and then connect using MDX!
Hi Christian, I have my dimensions and measurement in my SSAS cube. That is why I am connecting to cube.
I understood everything except step 1. I don't see an option to just connect to the sql server. When I create a connection in power pivot it wants to know the datasource.
Thanks,
Kit
Post a Comment