sysdatabases equivalent for OLAP DBs

Question

sysdatabases equivalent for OLAP DBs

timingskey

SSCommitted

Points: 1708
More actions
December 1, 2004 at 9:51 am

#109768

Is there a system table that holds the names of OLAP DBs? If not, then is there some other way to query that information?
Thanks,
December 6, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

harishr SSC Veteran Points: 262 More actions · Answer 1

Hi there

You need to look at the tables that hold the meta data for Analysis Services. When you install AS it uses an Access DB to hold this info

C:\Program Files\Microsoft Analysis Services\Bin\msmdrep.mdb

. You should really migrate this to a SQL Server DB.

Hope this helps

timingskey SSCommitted Points: 1708 More actions · Answer 2

Thanks Hari.

Can you elaborate though? Should it be converted to a SQL Server DB just for the sake of getting the table information I'm talking about? Or should it be converted to a SQL Server DB, and used as such, on an ongoing basis?

Please forgive my lack of knowledge. I'm just getting started with OLAP.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

It should be migrated to MSSQL to utilise the SQL engine as the repository DB engine, allow for it's inclusion in your backup routine of your SQL installation, and generally to make you feel better about it not being in MSAccess . Another thing you can do when it's in MSSQL is add the triggers (as per MSft documents on msdn) that will give you some level of logging of who changed what, when (ie a small amount of change control audit).

Steve.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

getting back to your original question, the way to interrogate objects in AS is through the use of DSO (Decision Support OBjects). This is very easy to do with VB6, slightly harder with VBScript (no object typing ) and OK(ish) with .net (I've used C#) but a little more cumbersome because of the poor object model used in AS2K (bring on Yukon!).

There are many example scripts/VB code on websites (like msdn) that can get you started, as well as BOL.

Steve.

timingskey SSCommitted Points: 1708 More actions · Answer 5

Thanks for your help Steve.

Regarding converting the mdb file to a SQL Server DB, It's puzzling to me why MS didn't do that for us to begin with!

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Honestly, I think ti was so that AS could be installed without SQL, but (for us anyway) the reality is that to manage an Enterprise installation of AS, we need the repository to be stored in SQL and will either get another license or utilise an existing one.

Steve.