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)

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.

Get Next(Nth) Business date function in SqlServer

When we were working on Insurance project, we got a requirement to process HSBC payments on HSBC business(working) days only. For that HSBC provided us a list of bank holidays for two years. With this list of holidays we created a SQL table' dbo.BankHolidayCalendar'(see the table at the end). Saturday and Sunday are also holidays for Bank.

We have a requirement that, we need to process all the HSBC payments which are 3 days due and 2days in future from today. For this I have written a generic sql function 'dbo.GetBusinessDateFrom'. This function requires 2 i/p parameters

1. @currentDate - Ref Date i.e., from Which date do you require Nth Business date
2. @businessDays - Ex.(2, 3, -2,-4, N etc) From above Ref date to how many days you need go forward or back ward(Negative number indicates back wards)

By using below sql function we can find the future Nth business date and past Nth business date. To find the past Nth business date we need to give negative number as input parameter for @businessDays

To find whether the date is Saturday Or Sunday , I have used the built in sql function
DatePart(dw,@date) - Dw for the Day of the Week. If you want know more about this function
http://support.microsoft.com/kb/186265. As i'm taking Saturday and Sunday are also bank holidays I shoudn't count these 2 days for that i'm using datepart(dw,@nextBusinessDate) not in (1,7)

Ex: How to Use/ Call the below function
Getting 3rd BusinessDate from today -> dbo.GetBusinessDateFrom(GETDATE(),3)
Getting past 3rd business date from today ->dbo.GetBusinessDateFrom(GETDATE(),-3)


/****** Object: UserDefinedFunction [dbo].[GetBusinessDateFrom] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE FUNCTION [dbo].[GetBusinessDateFrom]
(
@currentDate as DateTime,
@businessDays as int
)
RETURNS DateTime
AS

BEGIN

declare @businessDaysCount int;
declare @isBankHoliday bit;
declare @progress int;

set @businessDaysCount=0
set @isBankHoliday=0;

declare @nextBusinessDate DateTime
set @nextBusinessDate = @currentDate

if(@currentDate is null)
return null

if (@businessDays>=0)
set @progress=1;
else
set @progress=-1;


while((@businessDays>=0 and @businessDaysCount<@businessDays) or (@businessDaysCount>@businessDays))
begin
set @nextBusinessDate=dbo.GetDateWithoutTime(dateadd(day,@progress,@nextBusinessDate))
select @isBankHoliday=count(*) from BankHolidayCalendar where HolidayDate=@nextBusinessDate
if (datepart(dw,@nextBusinessDate) not in (1,7) and @isBankHoliday!=1)
begin
if(@businessDays>=0)
set @businessDaysCount=@businessDaysCount+1
else
set @businessDaysCount=@businessDaysCount-1
end
end

return @nextBusinessDate
END


Below is the sql table contains list of bank holidays
ID HolidayDate Description
1 2008-01-01 New Year's Day
2 2008-03-21 Good Friday
3 2008-05-19 Victoria Day
4 2008-07-01 Canada Day
5 2008-08-04 B.C. Day

6 2008-09-01 Labor Day
7 2008-10-13 Thanksgiving Day
8 2008-11-11 Remembrance Day

9 2008-12-25 Christmas Day
10 2008-12-26 Boxing Day
11 2009-01-01 New Year's Day
12 2009-04-10 Good Friday
13 2009-05-18 Victoria Day
14 2009-07-01 Canada Day
15 2009-08-03 B.C. Day
16 2009-09-07 Labor Day
17 2009-10-12 Thanksgiving Day
18 2009-11-11 Remembrance Day
19 2009-12-25 Christmas Day
20 2009-12-26 Boxing Day

GetDateWitoutTime -- User Defined SQL Function - SqlServer

/****** Object: UserDefinedFunction [dbo].[GetDateWithoutTime] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



CREATE FUNCTION [dbo].[GetDateWithoutTime]
(
@Date as DateTime
)
RETURNS DateTime
AS
BEGIN
DECLARE @dateString varchar(30)
SET @dateString = CAST(YEAR(@Date ) as varchar) + RIGHT('00'+CAST(MONTH(@Date ) as varchar), 2) + RIGHT('00'+CAST(DAY(@Date ) as varchar), 2)
return cast(@dateString as DateTime)
END