Business Analytics: Performance optimation

ta5

Hi

Just a generic question:
Is there a need to tune the BA Database (the one containing the cubes) in order to get faster query results with BA Advanced client?

I didn't dig any deeper yet, but I guess there must be a huge potential for example if creating indexes on fields that are used for selection.

Any input on this will be appreciated.

Thanks
Thomas

ta5

Some thougths and investigations over the weekend gave me some new issues on this.

Queries against the Analysis Service do not show activity in sql profiler at all. This is because Analysis Services creates a lot of files when the cubes are processed and therefore does not need to query sql server again. As far as I understand this is true at least for MOLAP cubes.

Anyway, one question solved, but one more raised
If Analysis Services uses its own files, why is Navision BA Basic creating a database that is named by default BA for MBS-Navision 4.0? As far as I see, Analysis Services uses this db as source, but acutally it could use the real Navision db for this. What's wrong with this argumentation?

Any input bringing light in this matter would be appreciated

Thomas

kine

1) The structure is this:

NAV Db => Scheduled Job => BA DB => OLAP Cube

2) OLAP is not using NAV db directly to prevent locking of data when processing OLAP cube and user requests
3) Which SQL version you are using? If SQL 2005 I recommend to use SP2 because there is performance boost

ta5

Hi Kamil
Thanks for your answer.

Ok, I now understand why the BA DB ist used. We have SQL 2000 in the production environment.

As far as I understand, the Analysis Services does not use the BA DB anymore after the cubes are processed, at least not if the cubes are MOLAP cubes.
Is there a way to speed up the response time for queries against Analysis Services?

Thanks in advance
Thomas

kine

I am not sure, but the DB created by BA Basic is the DB with data used by the MOLAP. It means, the data for slice&dice are processed from this DB. It is why you cannot delete the DB. The AS needs to have the data saved somewhere and this database is used for that. You can try it: create the DB and the cubes, change some data in the db, if the data in the cube will reflect that change, it is connected directly and you cannot "remove" the db...

You can speed up the performance by placing the data on optimal hardware (CPU, RAM, HDDs).

ta5

Thanks!