SSAS - Retrieve multidimensional database objects directories

Problem:

I am trying to list all objects (Cube, dimension, partition, ...) found in a SSAS server. I am able to do that using the following project:

• GitHub - SSASAMODB

I am trying to retrieve the relevant directory (within the data directory) for each object. I am unable to do that since files names contains some Incremental number that change each time you made changes to the objects in the database. Example:

• Cube name: TestCube

Folder:

|Data Dir|\<SSASDB>\TestCube.0.cub

After changing and reprocessing the Cube it changes to another value

|Data Dir|\<SSASDB>\TestCube.1.5.cub

Is there is a property in AMO classes that returns the Folder Path of each object? What is the value of the Incremental number included in the Folder name? Is there some workaround to do that?

---

Since i only have SQL Server Data Tools Business Intelligence tools installed i need a solution compatible with SSIS script Task since it is the only way that i can process the data. Note that there are many articles online for using AMO from script task

Solution:

SSAS Data directory contents hierarchy

After building many multidimensional Cube using SSAS, i can assume that the following tree is the Data directory hierarchy:

|- Data Directory
    |- Database (.db)
        |- Dimension (.dim)
        |- Role (.role)
        |- Mining Structure (.dms)
        |- Data Source (.ds)
        |- Data Source View (.dsv)
        |- Multidimensional Cube (.cub)
            |- Measure Group (.det)
                |- Partition (.prt)
                |- AggregationDesign (.agg)

Each object from the tree above can be stored in form of a directory or/and an XML file.

The Actions and Kpis information are stored within the Cube XML configuration file.

Example:

• Object: Cube
• Directory: <DataDir>\<database>\<cube ID>.cub\
• XML file: <DataDir>\<database>\<cube ID>.cub.xml

Link SSAS AMO objects to data directory files

Reading data using AMO

To read SSAS object from deployed Analysis Cube, i improved the code of the following project to add more objects and to link them with the relevant directories/files.

• SSASAMODB

Updated method

To map every AMO object to the relevant directory/XML file we have to loop over objects starting from top level (database) and retrieve the files/directories found in each level and map it using the .ID property and the extensions (as mentioned in the tree above)

Note that the method only works on local servers or you must have a Mapped network drive with the same letter of the Original drive that contains the data directory. In addition you must have the permission to access the Analysis Server objects

The code is considered as a proof of concept, and can be improved

I created a small windows application and upload it to GitHub, you can use it as a separate tool, or you can simply copy to Classes in the Script Task project and use it within the Script.

• GitHub - MetaCube
• MetaCube Classes

original post: https://stackoverflow.com/questions/55792689/retrieve-multidimensional-database-objects-directories/55833672#55833672