September 16, 2017 at 10:22 am
I have a 2016 ver tab cube in a 2016 ver instance, being loaded by a 2016 ver SSIS package that is in a SSIDB 2016 ver instance. It seems to load the cube very slowly, and well as take a lot of time moving from stage to prod, and prod into the cube. It's reading a single fact view with a number of CASE statements, and 6 very small dim tables. The fact view with all the dims has all the PK's from the DIms in the create view as FK with joins. All the tables, both dim and fact are regular tables, they are not compressed. What can I do to boost performance within the components of SSIS.
September 17, 2017 at 3:18 am
quinn.jay - Saturday, September 16, 2017 10:22 AMI have a 2016 ver tab cube in a 2016 ver instance, being loaded by a 2016 ver SSIS package that is in a SSIDB 2016 ver instance. It seems to load the cube very slowly, and well as take a lot of time moving from stage to prod, and prod into the cube. It's reading a single fact view with a number of CASE statements, and 6 very small dim tables. The fact view with all the dims has all the PK's from the DIms in the create view as FK with joins. All the tables, both dim and fact are regular tables, they are not compressed. What can I do to boost performance within the components of SSIS.
You've been around here long enough to know that this is far too little information for even beginning to guess what the problem(s) could be!
😎
September 18, 2017 at 8:12 am
Eirikur Eiriksson - Sunday, September 17, 2017 3:18 AMquinn.jay - Saturday, September 16, 2017 10:22 AMI have a 2016 ver tab cube in a 2016 ver instance, being loaded by a 2016 ver SSIS package that is in a SSIDB 2016 ver instance. It seems to load the cube very slowly, and well as take a lot of time moving from stage to prod, and prod into the cube. It's reading a single fact view with a number of CASE statements, and 6 very small dim tables. The fact view with all the dims has all the PK's from the DIms in the create view as FK with joins. All the tables, both dim and fact are regular tables, they are not compressed. What can I do to boost performance within the components of SSIS.You've been around here long enough to know that this is far too little information for even beginning to guess what the problem(s) could be!
😎
Sorry, let me provide more details,
For the Stage, I'm loading the SQL Server Stage table from Teradata with Teradata Driver/Attunity Provider, the SQL command is a TPT Export, and OLE DB, Fast Load with Check Constraints on
I'm loading from a SQL Server stage table to a SQL Server Prod table, with OLE DB, Direct Input, Insert/Select
I'm not sure if I should address indexes on both tables. The Prod table does have constraints with FK in from other PK in Dim tables, as well as a complex view built off it that the cube reads.
Thanks
September 18, 2017 at 2:23 pm
What are the times for each one of the steps
1 - teradata to staging
2 - staging to final db
3 - final db to tabular (load data)
4 - tabular recalc (measures and calculated columns)
3 and 4 assume you did split the steps - if doing a processfull the only way to get the different values is through a trace.
- staging to prod db
Are the loads from staging to final db full loads or incremental?
And are you dropping/recreating the indexes before/after load?
Is load using fast load
- prod db to tabular
is it on the same server or different servers - if different have you changed connection string packet size to the max (32768/7)?
What are the hardware configuration of both SQL and SSAS servers - how much memory allocated to each, number of cores, max DOP on the sql instance and so on.
If bottleneck is SSAS do you know if it is paging while doing the processing?
If bottleneck is retrieving the data from the view (you can test this with a straight ssis package to issue the same sql statements your tabular model is issuing and output to a file) - this would be standard SQL performance validation.
you may need to get the scripts from http://byobi.com/2015/10/powershell-scripts-for-collecting-ssas-related-perfmon-and-xevent-trace-data/
to get the perfmon and ssas counters and see where the issues are if on the tabular side.
My version attached including 2 scripts to load the output of perfmon/xtrace onto sql server tables. - and 1 to kick off both monitors in parallel 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply