OLAP Universe on MSAS2005 cubes

[Former Member]

All,

I have built an OLAP Universe on top of MSAS 2005 cube. I have BOXI3.1 base version in Windows.

I am seeing two immediate problems

1. Atrocious performance - A simple query that will return in a snap in say Proclarity or Excel keeps running for 15 - 20 mins.

2. Once results are available, if i try to go back to the "edit query" panel my webi report panel just freezes. I can close it and go back to infoview, but my current query is lost. I see this behavior ( report panel freeze - mine is Java2) also in a long running query.

Can anyone kindly assist with suggestions based on your experience.

Edited by: ramaks on Nov 23, 2009 8:58 PM

[Former Member]

Hi,

For all OLAP databases (SAP BW, Microsoft, Essbase) supported in OLAP universes we are using the same drivers than Voyager.

We've achieved a lot of performance improvements during the past months but in same cases the performance is dropping.

One of the major cause is that we are flattening results in order to be consumed by the Semantic Layer and Web Intelligence.

We recommend to not create one big query but prefer to create several queries in Web Intelligence with synchronization.

Moreover it is also recommended to create mandatory filters in the universes (a la SAP variables) to filter the data.

The good news is that the next major version of the Semantic Layer will be multidimensional that means that we will fully support OLAP databases with no flattening. Moreover the MDX generation has completely been redeveloped and optimized.

The first internal benchmarks we did gave us hopeful results.

Regards,

Didier

[Former Member]

Hello Didier,

Thank you for your response. It is indeed my understanding that significant improvements have been done in SP2 and higher releases and we do look forward to the improvements in the next release.

However i currently am tied to BOXI 3.1 base version. SP2 is still under packaging within our firm so i am not expecting it to be fully ready and tested for two months.

I need to expose our cube based universe with the BOXI3.1 base version ( no fix-packs, service packs) in a month. So basically i am looking for any configuration tricks/tips that will help me improve performance

Based on your suggestions

1. We recommend to not create one big query but prefer to create several queries in Web Intelligence with synchronization.

- Can you provide more details on this?

2. Moreover it is also recommended to create mandatory filters in the universes (a la SAP variables) to filter the data.

- Would not refreshing the universe structure over-write filters that have been created? My apologies if this is not true,

i tried this a few months back and saw this behavior.

[Former Member]

Hi,

As I mentioned a lot oif improvements including a robust universe refresh structure have been included in SP2.

It is hard to define a query scope because it will hardly depends of the cube definition and the query content:

<li>Cube type: OLAP, MOLAP or ROLAP

<li>Aggregations definition in the cube (stored or computed on the fly)

<li>Hierarchies depth

<li>Hierarchies weight

<li>Number of measures on the query

<li>Number of hierarchies in the query

<li>etc

Didier

[Former Member]

Hi there. I read your post and am particularly concerned with the performance issues you have been facing using BO on top of a cube and I would appreciate any further thoughts you may have about our situation.

We have a BO universe sitting on top of SQL Server relational data. The data is quite large (getting close to 1 TB) and performance is very bad. We are considering using MS OLAP cubes to a to improve performance - using MS SQL based tools like proclarity and Excel the performance gains are huge. However, it sounds like those performance benefits are not realized in XI 3.1 ( and likely earlier versions of BO). I guess the MDX that is produced by the Universe is not good...?

I also am not sure what is meant by a query as the data source. My instincts are to simply make the data source the whole cube rather than a query of the cube. I want all the information in the cube available to end users. Any thoughts would be appreicated!

Thanks in advance.

[Former Member]

Twebber,

Apologies for the delay. I will be glad to share some experiences and hopefully that will be useful.

If your BO Universe is on relational data, BO does give some stellar performance. So if your data has grown, you can try to work on query efficieny ( indexes) and also try partioning. I wouldn't recommend you to move it to cube unless

1. You want your users to have adhoc slicing and dicing through client tools ( like excel)

2. You want to use the cube to create calculated measures say MTD/YTD etc which would be harder to achieve in a relational model.

In our case - yes the MDX produced by BO is not optimized - hence the performance issues...we got some improvement by using MSAS 2008- however the BO performance cannot compete with Proclarity or Excel.

We are hoping that the newer versions of BO would see some enhancements.

Regards