Skip to Content
author's profile photo Former Member
Former Member

Reporting on Transactional Cubes

Hi,

A transactional cube is loaded data from BPS (APO* request)all the time, and there are aggregates associated with this cube. By default aggregates are rolled-up after the number of records reaches 50000. If a query is run before that threshold is reached, is it going to hit the aggregates? If yes, it will not have the most recent data; if no,performance would be very slow...why do we need the aggregates since they are not accessed?

Am I missing something? Could you guys clarify.

Thanks,

FF

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 07, 2005 at 09:41 PM

    Dear Fred:

    Aggregate will have data only after roll up, In Trans cube if you want to make a use of the other records which are still in 'yellow' stage..not loaded officially. You can do setting in your BEx Query in the data packet..bring the most recent data into the free char. section. In this case, the query will not hit the aggregates..instead the data will be fetch from cube.

    thanks.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Have you run the query using RSRT? Think you need to confirm whether the query uses the aggregate when it runs without the Most Current Data variable or not. The perfromance problem sounds like it could be doing what Buddhi suggests, although I believe that is contrary to what I have seen in the past, but it has been a while.

      What DB and SP are we talking about? Some of this could easily be different depending on the DB.

      I would use RSRT to confirm how many SQL queries are run as a result of the Bex query, and then actually review the Explain Plan(s). Wondering if using the Most Recent Data variable use causes a very different execution plan to be used.

  • author's profile photo Former Member
    Former Member
    Posted on Oct 07, 2005 at 08:20 PM

    Fred -

    I've never heard of any threshold that causes a rollup. Perhaps you mean the Transactional cube is set to automatically close the open request (yellow) when it has 50,000 records and that in turn causes the rollup. That would be my understanding of how it works.

    In's been a while since I looked at this, but I'm pretty sure what you will see is that -

    when you run a query on a transactional cube <b>AND</b>

    you are using the Most Current Data variable to also include data from the open request, <b>AND</b> there is a suitable aggregate -

    the system is smart enough to use the aggregate to get the data that it can from there <b>AND</b> it will also query the open request of the base cube to get the data it needs from there.

    You should be able to verify it this all in RSRT. Just need to make sure the aggregate has everything the query needs.

    Pizzaman:)

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.