Refreshing SSAS cube

Question

Refreshing SSAS cube

VRT

SSCertifiable

Points: 5776
More actions
June 29, 2015 at 6:41 am

#148755

Hi
I wanted to do a incremental load of my cube.
My sql query for taking delta is this.
select column 1, column 2 ...column N from tableFact
where load_dt > current date - 1
This basically check the load date and find the records came newly from yesterday. I schedule this daily.
Lets think like the job failed on Sunday and no body available to take care. It picks Monday schedule automatically and should pull from Saturday to Monday (last successful date to current date).
what I thought is that I want to read the latest load date from SSAS Cube using MDX. can this be possible in SSIS package.
Thank You

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

You can use an Execute SQL task to run MDX. An example is here: https://gqbi.wordpress.com/2014/04/03/ssis-using-sql-server-analysis-services-ssas-mdx-query-into-variable-in-execute-sql-task/

I'm on LinkedIn

VRT SSCertifiable Points: 5776 More actions · Answer 2

It looks like helpful.

But I tried this approach. I have the below query and I am getting the error "Unsupported data type on result set binding last_data_update"

I pulled the execute SQL task. I did as mentioned in the link. I used the below query.

SELECT last_data_update FROM $System.MDSCHEMA_CUBES

where cube_name = '$Tablename'

and I mapped the last_data_update with variable I created which is of DateTime type. I know the Last_Data_Update gives the DateTime type only. But still getting error as mentioned above.

Thank You

PB_BI SSCoach Points: 17463 More actions · Answer 3

Firstly, use the ordinal number for your result; this would be 0. Secondly just make your variable a string.

I'm on LinkedIn

VRT SSCertifiable Points: 5776 More actions · Answer 4

No luck after trying with string. same error is coming.

Thank You

PB_BI SSCoach Points: 17463 More actions · Answer 5

Apologies the previous link is misleading. In order to do this correctly you need to create an ADO.NET connection manager to your cube and then choose ".Net Providers for OleDb\Microsoft OLE DB Provider for Analysis Services xx.x". You can then keep your variable as DateTime and it will map correctly.

I'm on LinkedIn

VRT SSCertifiable Points: 5776 More actions · Answer 6

Its now working fine. Thank you.

now the next challenge, Can I read this variable in Data Flow source tasks.

I created a OLEDB connection to oracle database.

I want to take the delta records by giving query as below.

select column 1, column 2....column N

from Dimtable

where loadDate between <variable> and current_date

Thank You

PB_BI SSCoach Points: 17463 More actions · Answer 7

VRT (6/30/2015)
Its now working fine. Thank you.
now the next challenge, Can I read this variable in Data Flow source tasks.
I created a OLEDB connection to oracle database.
I want to take the delta records by giving query as below.
select column 1, column 2....column N
from Dimtable
where loadDate between <variable> and current_date

Oracle as well? Not making this easy for yourself are you 😀

This one is relatively simple. Just create a string variable for your SQL and then in the expression concatenate your SQL statement with your modified_date variable; if it's DateTime you'll have to convert it so:

"select column 1, column 2....column N

from Dimtable

where loadDate between "+(dt_str, 20, 1252)@variable+" and current_date"

Then in your data source choose "SQL Command from Variable" as the data access mode and pick your SQL variable from the drop down.

I'm on LinkedIn

VRT SSCertifiable Points: 5776 More actions · Answer 8

Thanks a lot.

I am able to work with it.

yeah 🙂 its hard way and this is how the requirement is.

There is another challenge. can I use the variable in Analysis server processing task for fact refresh.

I will try that out.

Thank You

VRT SSCertifiable Points: 5776 More actions · Answer 9

I think I am close to what I want to achieve.

Using Execute SQL task, I check the last_data_update from system table and take that date and keep it in a variable.

then use the Data flow.

In OLEDB source task, I create a query using this variable and pull data from last_data_update to current date from Oracle.

I use Dimensional Processing task for processing Dimensions.

I use Partition Processing task to process the Cube.

Hope this works as per plan. I test so far for dimensions and its working fine. Need to test for facts.

Thank You

PB_BI SSCoach Points: 17463 More actions · Answer 10

VRT (6/30/2015)
Thanks a lot.
I am able to work with it.
yeah 🙂 its hard way and this is how the requirement is.
There is another challenge. can I use the variable in Analysis server processing task for fact refresh.
I will try that out.

Yes this is possible, using the same method that you did for SQL but instead of SQL write XMLA in a variable and then use it in an XMLA task to refresh your fact partition using a process incremental.

I'm on LinkedIn

VRT SSCertifiable Points: 5776 More actions · Answer 11

Do you think XMLA is easier than SQL. Does it really helpful for oracle data sources?

Only concern is, Neither I nor my clients have XMLA skills. Every body here are good with SQL so I preferred this approach.

Thank You

PB_BI SSCoach Points: 17463 More actions · Answer 12

You're talking about two different things. XMLA is not an alternative to SQL, you use it to (amongst other things) process cubes. If you want to dynamically process fact dimensions based on your variable then you have to use it to some degree. You don't have to be an expert; in SSMS you can right click on a cube object to process, set the options and then script it out. You can then examine this script and customise it to your needs.

I'm on LinkedIn

VRT SSCertifiable Points: 5776 More actions · Answer 13

I understand now what are you telling. I thought that is only for Tabular models. We can do that for MultiDim also.

Thank a lot Paul for your great help.

Thank You