Hi,
I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please?
Thanks
fmardani wrote:
Hi,
I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please?Thanks
Compilation, to me, suggests that a binary object file is created. There is nothing like this in SSIS. i.e. No compilation occurs.
-Jamie
Hi,
I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.
The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.
When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.
I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.
Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.
Thanks,
Sam
That sounds very strange. Although I'd guess its a SQL Server Agent problem rather than SSIS.
Can you replace the steps with something else - some simple command-line calls for example, and see if the same thing happens?
Do the log fiels for SQL Server Agent and SSIS tie up? i.e. The package may start 30 minutes late but did the job step start 30 minutes late (there's an important distinction here)?
-Jamie
|||
hi sam, I can think over that problem is that your sql agent is very busy attending other jobs ?
|||
Jamie,
Thanks for the reply, I will try the job with a couple of simple calls.
The log fields do not tie up - each job step is starting well before it's package starts.
Sam
|||Enric,
Thanks for the reply, but this is the only job on the server at the moment, so that shouldn't be causing a problem.
Sam
|||Jamie,
Thanks for the reply, I will try the job with a couple of simple calls.
The log fields do not tie up - each job step is starting well before it's package starts.
Sam
If that is the case then I would suggest that the delay is caused by the package going through validation. Set DelayValidation=TRUE on the package to see if this removes the delay. If it doesn't, set DelayValidation=TRUE on all your containers and tasks and see if this removes the delay.
If this solves the problem then you know that it is the validation step that is causing the delay. Try doing what i suggested above and then reply here and we'll take it from there!
-Jamie
|||
this is probably a longshot...
do you see this problem when you run package in bi studio?
is it possible that the service startup is slow?
there is a kb article that talks about problem in sp1
http://support.microsoft.com/?kbid=918644
|||The DelayValidation at the package level, as suggested by Jamie, seems to have done the trick. I also found that there was a msmsgs.exe process running which was constantly using half the processor - killing this has sped things up even more.
Would the DelayValidation setting have any other impact on the package?
Sam
sqlHi all. I am new to SSAS 2005 and MDX in general. My team is building a data warehouse and thus far have the ETL (SSIS) part all done and am now moving on to the SSAS portion of the project. We have a cube built however, when browsing it via pivot tables, we noticed that the measures that are not additive, for example, "Average Log Length", show up as essentially sums over a given time period.
At first we thought that we needed to define the AggregateFunction for the specific measure to be AverageOfChildren. But that didn't work. The source data all has values of say 14.2, yet the average for a given day comes out to 2034.43! Clearly not an average.
This makes these non-additive measures completely useless in the cube.
Can anyone tell me what I am doing wrong? And how to correct it?
Any help would be much appreciated.
Thanks!
Jeff Ptak
Is the Type property of the time dimension specified as Time? The AggregateFunction semi-additive measure specification only applies to the Time dimension in your cube and will not work at all if no dimension is specified as a Time type.|||This has come up before and the following is taken from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1
the AverageOfChildren is semi-additive, ie. it acts like a "sum" across all dimensions except time. For example, in Adventure Works if you add an AverageOfChildren measure on the OrderQuantity field of FactSalesSummary, its value matches that of [Measures].[Order Quantity] at the leaf Date level. But for July 2001 as a whole, [Measures].[Order Quantity] = 966, whereas the AverageOfChildren measure = 31 (which is 966 divided over 31 days of July).
Have you tried creating standard sum and count measures and then dividing the sum by the count? This should do the trick and you can hide the sum and count if they are not otherwise needed.
|||Yes. Set as Time.|||I have already read that post. I think I have it setup correctly, but the AverageOfChildren doesn't do what I want despite browsing by my Date dimension (which is configured as the "Time" type).|||No, as that post explains, AverageOfChildren does not average across all dimensions, it sums across all other dimensions and only averages across the time dimension. It is not really well named, in fact in some of the wizards now it comes up as "Average over time".
Calculating a standard average of the values in the fact table can be done in a couple of easy steps.
1. Create a measure using a SUM aggregation
2. Create a second measure using a COUNT aggregation
3. Create a calculated measure that is (1) / (2)
|||One last question. Are you deploying to Enterprise edition? Semi-additive measures are only supported in Enterprise.
http://www.microsoft.com/sql/editions/enterprise/comparison.mspx
|||Yes. Deploying to Enterprise edition.
I'll try the sum/count thing. From what I have read, that seems to be common work-around to this problem.
Thanks!
|||It's not really a work-around, the averageOfChildren is meant to solve a different, more difficult class of calculation.|||I figured this out. Simply set up a calculation for each non-summable measure that is:
Measure / Count
I made sure the AggregateFunction for the measure is set to SUM and I used the Count measure that the cube wizard inserted into my measure group (which has its AggregateFunction set to COUNT).
I would have thought that there was an easier way. I now have over 600 calculations in my my cube! And that's only for one process area of my facility.
Thanks for all your help.
Hi all. I am new to SSAS 2005 and MDX in general. My team is building a data warehouse and thus far have the ETL (SSIS) part all done and am now moving on to the SSAS portion of the project. We have a cube built however, when browsing it via pivot tables, we noticed that the measures that are not additive, for example, "Average Log Length", show up as essentially sums over a given time period.
At first we thought that we needed to define the AggregateFunction for the specific measure to be AverageOfChildren. But that didn't work. The source data all has values of say 14.2, yet the average for a given day comes out to 2034.43! Clearly not an average.
This makes these non-additive measures completely useless in the cube.
Can anyone tell me what I am doing wrong? And how to correct it?
Any help would be much appreciated.
Thanks!
Jeff Ptak
Is the Type property of the time dimension specified as Time? The AggregateFunction semi-additive measure specification only applies to the Time dimension in your cube and will not work at all if no dimension is specified as a Time type.|||This has come up before and the following is taken from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1
the AverageOfChildren is semi-additive, ie. it acts like a "sum" across all dimensions except time. For example, in Adventure Works if you add an AverageOfChildren measure on the OrderQuantity field of FactSalesSummary, its value matches that of [Measures].[Order Quantity] at the leaf Date level. But for July 2001 as a whole, [Measures].[Order Quantity] = 966, whereas the AverageOfChildren measure = 31 (which is 966 divided over 31 days of July).
Have you tried creating standard sum and count measures and then dividing the sum by the count? This should do the trick and you can hide the sum and count if they are not otherwise needed.
|||Yes. Set as Time.|||I have already read that post. I think I have it setup correctly, but the AverageOfChildren doesn't do what I want despite browsing by my Date dimension (which is configured as the "Time" type).|||No, as that post explains, AverageOfChildren does not average across all dimensions, it sums across all other dimensions and only averages across the time dimension. It is not really well named, in fact in some of the wizards now it comes up as "Average over time".
Calculating a standard average of the values in the fact table can be done in a couple of easy steps.
1. Create a measure using a SUM aggregation
2. Create a second measure using a COUNT aggregation
3. Create a calculated measure that is (1) / (2)
|||One last question. Are you deploying to Enterprise edition? Semi-additive measures are only supported in Enterprise.
http://www.microsoft.com/sql/editions/enterprise/comparison.mspx
|||Yes. Deploying to Enterprise edition.
I'll try the sum/count thing. From what I have read, that seems to be common work-around to this problem.
Thanks!
|||It's not really a work-around, the averageOfChildren is meant to solve a different, more difficult class of calculation.|||I figured this out. Simply set up a calculation for each non-summable measure that is:
Measure / Count
I made sure the AggregateFunction for the measure is set to SUM and I used the Count measure that the cube wizard inserted into my measure group (which has its AggregateFunction set to COUNT).
I would have thought that there was an easier way. I now have over 600 calculations in my my cube! And that's only for one process area of my facility.
Thanks for all your help.
