June 29, 2015 at 6:41 am
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
June 29, 2015 at 8:48 am
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/
June 29, 2015 at 12:52 pm
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
June 29, 2015 at 5:44 pm
No luck after trying with string. same error is coming.
Thank You
June 30, 2015 at 2:06 am
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.
June 30, 2015 at 8:59 am
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
June 30, 2015 at 9:09 am
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.
June 30, 2015 at 12:42 pm
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
June 30, 2015 at 1:21 pm
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
June 30, 2015 at 1:27 pm
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.
June 30, 2015 at 2:00 pm
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
June 30, 2015 at 2:21 pm
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.
July 1, 2015 at 9:34 am
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply