Performance regarding transposing fact data

Hello

I have an Analysis Services performance question:

Scenario:

My Fact table has a column that indicates some value-ID and only one value column. Out of this fact table I'd like to create a cube with two measures, one for each value-ID. So I could either write a query like

Code Snippet

SELECT key1, key2,

sum(case valueID when 'A' then value else null end) as measureA,

sum(case valueID when 'B' then value else null end) as measureB

FROM myFacts

GROUP by key1, key2

Then I'd use this table or view as fact table.

Or I could include the case syntax directly in the measure definitions inside Analysis Services and use the original table.

Does anybody know what's better concerning performance

-regarding cube processing?

-regarding querying the cube?

Are there other things that make one solution the better one?

Hi,

If you use your select statement to load the data into the cube.

-Processing will be slower as it has to execute the case statement

-Querying will be faster

If you read in the values with out the case statement

-Processing will be quicker as it doesn't have to execute the case statement

-Querying will be slower, you will have to create to MDX calculated measures that do the same as you are showing.

If you use your group by select statement, the granularity of your fact is also going to change - is that really what you want?

I would just create two MDX calculated measures, does depend on the size of data I suppose.

CREATE MEMBER CURRENTCUBE.[measures].[measureA] as

([Measures].[Value],[Dimension].[Dimension Key].&)

,non_empty_behavior = [Measures].[Value],VISIBLE = 1;

Might look something like above - ish

Hope that helps,

Matt

|||

Hi Matt

Thank you for your reply.

(First I apologize for not having added that I'm using Analysis Services 2000 not 2005.)

Generally I don't want to create a calculated member for each measure becaus calculated members are only calculated during run time but I want to have the measures correct after the cube was processed.

In Analysis Services 2000 I don't have the ability to use a select statement as a source for a fact table. Instead a table or view must exist in the underlying datasource. So there I have the possibility to either create a table or view with the select statement above or I enter the case expression inside the measure definition. So somewhere the case will be executed because having a dimension like "valueID" is not what I want or, wait, ...maybe it could be also a possible solution to have just one measure in the cube and have a valueID dimension... But this leads to more difficult queries and some inconveniances for cube users.

Regards

Chris

|||

Hi,

My 2000 skills are a little rusty and weren't perhaps that great in the first place

I would probably create a view, it would allow you to compare A against B or even create a total of A and B if you need to.

Sorry I can't be much help, perhaps someone else will help you.

Cheers

Matt

Performance on parent childe 2000 vs. 2005

We like to use parent childe dimension in our OLAP project. In Analysis Server 2000 we know there is no aggregation stored in intermediate levels. Analysis Server could only store aggregation on leaves and the root level. This could slow down the query performance.

How dose Analysis Server 2005 managing aggregation in parent child relation? And what happened with attributes hierarchies on in parent child dimension?

Hope someone can give me a hint.

Marco

Hi,

you could have one parent child and many othere hierarchies in the dimension. Analysis Services is capable to build aggregations on all othere hierarchies and root/leave of parent child, but is still not capable to build aggregations on intermediate levels of the parent child.

I expect the server to be faster especially if you could limit the parent child by restriction with other hierarchies

HANNES

Performance on Fact table connected in View

Hi,

I have a Fact table and I'm accessing it through SQL Views where I have "group by". This is the one I feed to the Analysis Services. Would it be faster to access data if I have group by's in Views and feed that to Analysis Services or would it be better to just do the select statement in View and let the Analysis do all the aggregations?

cherriesh

If SQL and SSAS are on the same box then removing the group by should make things go faster as SQL will not need to sort the results and spool them temporarily out to tempdb until all the results have been read and there will be little latency to deal with between the two services.

If they are on separate machines it depends if the amount of time it takes to do the group by is more or less than the time it would take to transmitt the additional data over the network.

Performance of multiple charts on a report (RenderStream issue with RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.

The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.

The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)

I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).

Jon

I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.

However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.

Any help would be greatly appreciated. Thanks! - Eric -

|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!

Performance of multiple charts on a report (RenderStream issue with RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.

The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.

The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)

I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).

Jon

I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.

However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.

Any help would be greatly appreciated. Thanks! - Eric -

|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!

Performance of multiple bar charts per report (RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart attached to it to
show the average score out of five for each multiple choice answer
within a question.
The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (over 1 minute) when using SOAP access.
The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one for each chart.)
I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).
JonYou could make the calls asynchronous (I'm assuming all calls are
synchronous).
"jonny" <jon@.jongianni.com> wrote in message
news:1147337806.062580.126100@.u72g2000cwu.googlegroups.com...
>I have a 'questionnaire analysis' report. The report consists of approx
> 25-30 questions and each question has a bar chart attached to it to
> show the average score out of five for each multiple choice answer
> within a question.
> The report ends up containing 25-30 bar graphs all contained within a
> List control. The report performance is acceptable using URL access but
> is exceptionally slow (over 1 minute) when using SOAP access.
> The performance bottle-neck seems to be the multiple calls to
> RenderStream to render all of the images that get generated when the
> report executes (one for each chart.)
> I was wondering if there was any way, still using SOAP, of avoiding
> using RenderStream or of speeding up performance. Report caching is not
> really an option in my scenario nor is splitting into multiple pages
> (user wants everything on screen at once.).
> Jon
>