MS SQL Server OLAP - Performance Problems

Author

Performance Problems - Aggregation and MOLAP

Karthik Rao

2005-12-24, 7:23 am

I would like to improve performance on queries by persisting calculated
values. I could run an overnight job to calculate all cells, or I could
persist parts of a sub-cube that a client creates.

Is this what aggregation does? Or are calculated values re-calculated
at each query?

I have tried aggregation etc and I had the counts refreshed and all
that. But my query erformance has not improved at all. Moreover BIDS
hangs quite often, as it tries to synchronize or something (I see a
process OLPSynch that gets quite active when this happens.

Where can I find help on aggregations? (Has to be a lot better than BOL
:-))

Jéjé

2005-12-24, 1:23 pm

with AS2000 you can creatre new cubes which contains preaggregated values,
then use this cube in your formula, or calculated cells.

AS2005 cache the formula results, so when a user ask for a formula, the
result is cached on the server, next users will hit the cache.

"Karthik Rao" <rao.karthik.s@gmail.com> wrote in message
news:1135423628.591603.262610@g14g2000cwa.googlegroups.com...
>I would like to improve performance on queries by persisting calculated
> values. I could run an overnight job to calculate all cells, or I could
> persist parts of a sub-cube that a client creates.
>
> Is this what aggregation does? Or are calculated values re-calculated
> at each query?
>
> I have tried aggregation etc and I had the counts refreshed and all
> that. But my query erformance has not improved at all. Moreover BIDS
> hangs quite often, as it tries to synchronize or something (I see a
> process OLPSynch that gets quite active when this happens.
>
> Where can I find help on aggregations? (Has to be a lot better than BOL
> :-))
>

Karthik Rao

2005-12-26, 3:28 am

Thanks, Jeje,
I have read as much, but am looking to understand this a little more in
detail.
Help, anybody?

Jéjé

2005-12-26, 9:23 am

you want to learn more for which solution?
preaggregated values in a cube (AS2000)?
or how the results are cached in AS2005?

Microsoft has published a sample project called "T3 Project" based on
AS2000.
This project demonstrate how to create a terrabyte cube.
there is some preaggregated values.

http://www.microsoft.com/presspass/...se-ScalePR.mspx
(the link to the detailed information is wrong in the presspass :-( )

"Karthik Rao" <rao.karthik.s@gmail.com> wrote in message
news:1135578433.607780.54700@g44g2000cwa.googlegroups.com...
> Thanks, Jeje,
> I have read as much, but am looking to understand this a little more in
> detail.
> Help, anybody?
>

Karthik Rao

2005-12-26, 11:23 am

Thanks, I am looking for 2005 behavior. And whatever I have read so far
has been pretty sketchy. And I am a newbie too. :-)

J=E9j=E9 wrote:

> you want to learn more for which solution?
> preaggregated values in a cube (AS2000)?
> or how the results are cached in AS2005?
>
> Microsoft has published a sample project called "T3 Project" based on
> AS2000.
> This project demonstrate how to create a terrabyte cube.
> there is some preaggregated values.
>
> http://www.microsoft.com/presspass/...terprise-Scale=
PR.mspx[color=darkred]
> (the link to the detailed information is wrong in the presspass :-( )
>
>
> "Karthik Rao" <rao.karthik.s@gmail.com> wrote in message
> news:1135578433.607780.54700@g44g2000cwa.googlegroups.com...

Jéjé

2005-12-26, 11:23 am

ok...

if you really suffer "big" performance issues with AS2005...
you can do the same thing as with AS2000, you can create a preaggregated
measure group and use it in particular cases.
for example, you have a table which contains the quantity sold at the
category level of your product dimension, something like this could help
you:

Scope(products.[Product Category].members, measures.Qty);
measures.Qty = measures.PreAggrCategQty
End Scope;

But with AS2005, the performance is excellent.
but there is a big difference between cold cache and warm cache. when a user
access the first time a cube, the cube is not in memory, so there is a delay
to load it into memory. next queries are far better due to the cache access.

you can schedule some MDX queries easely with SQL 2005 to "fill the cache".
create an XML/A command which contain the MDX query you want, go to the SQL
2005 agent, create a new sceduled task to execute this XML/A command.

If you don't know how to write the MDX query and the overall XML/A command,
just start the SQL Profiler and intercept the requests made against your
OLAP server.
Then start your client application (for example the integrated visual studio
OLAP browser) and setup the pivot table to display what you want. the
profiler will display the MDX query and also the XML/A command generated.

maybe this helps you for the moment.

"Karthik Rao" <rao.karthik.s@gmail.com> wrote in message
news:1135610889.379088.259330@g44g2000cwa.googlegroups.com...
Thanks, I am looking for 2005 behavior. And whatever I have read so far
has been pretty sketchy. And I am a newbie too. :-)

Jéjé wrote:
[color=darkred]
> you want to learn more for which solution?
> preaggregated values in a cube (AS2000)?
> or how the results are cached in AS2005?
>
> Microsoft has published a sample project called "T3 Project" based on
> AS2000.
> This project demonstrate how to create a terrabyte cube.
> there is some preaggregated values.
>
> http://www.microsoft.com/presspass/...se-ScalePR.mspx
> (the link to the detailed information is wrong in the presspass :-( )
>
>
> "Karthik Rao" <rao.karthik.s@gmail.com> wrote in message
> news:1135578433.607780.54700@g44g2000cwa.googlegroups.com...

Karthik Rao

2005-12-28, 3:24 am

Thanks, Jeje
Am using Query Biuilder in reports instead, is a much easier way.

When I add a product to subcube area, the code uses a Filter function.
Is that any different from Subcube?

I would also like to pass an argument to my mdx. Like the product
category. So an external job can run for some product categories and
warm up the cache.

Can I build code to pass arguments in BIDS? Or do I need to move the
whole thing into a COM+ component or something?

Best regards

Sponsored Links