SAS OLAP Cubes

[reecesw]

Hii.

I am struggling to find a solution to an issue I am facing with the SAS OLAP Cubes. Not with the cubes themselves but I am looking for a query that Google just doesn't seem to have.

I am looking for a way to get a list of the SAS OLAP Cubes names from the server using VBA. Essentially what I have to do is write a piece of code in VBA that stores the names of the Cubes in a list and process it through a loop. I am not allowed to hardcode the names as is company policy. This is the reason I cant use AS MC and proc metadata.

I appreciate any assistance you have.

Kind regards

RSW

[Patrick]

@reecesw 

There are two very distinctive areas to cover in your question.

1. How to issue a SAS query using VBA

2. How to retrieve information about SAS Olap cubes

For 1) If you haven't done this already then first thing you need working is a call to SAS out of VBA. If you're able to make a "hello world" program execute then you're fine, else that's the first bit you need to solve.

For 2) SAS Olap cubes have a SAS Metadata and a physical data component. To get a list of Olap cubes you first need to query SAS Metadata. If you then also want to know if there is not only a cube definition but also physical data available then you need to check this as well.

So you need A) query SAS Metadata and B) use the retrieved information from SAS Metadata to test for existence of the physical data. The following link shows how you can do the latter: https://blogs.sas.com/content/sastraining/2012/02/22/does_the_cube_exist/ 

I don't have ready made code for retrieving all Olap cube definitions from SAS Metadata but it should be close to code in the following link for retrieving SAS Metadata table definitions. https://stackoverflow.com/questions/21548317/retrieving-tables-located-on-metadata-server-with-sas

To figure out where the Olap object fits into the metadata model here the docu: http://support.sas.com/documentation/cdl/en/omamodref/67417/HTML/default/viewer.htm#classifier.htm

I find it easiest to find my way through SAS Metadata via PC SAS using Metabrowse - but that client might not be available to you.

http://support.sas.com/documentation/cdl/en/itrmxag/64211/HTML/default/viewer.htm#p1uyenyqmev8ckn15y... 

So... Not sure where you are at right now and likely not as simple a task as you would wish for. 

I suggest you give it a go step by step and then come back with detail questions eventually also posting the code bits with which you get stuck. It's also certainly very worthwhile to spend some time searching the Internet as there will likely be solutions out there which can help you considerably on your way.

Steps you need to implement/solve:

1. Execute SAS code via VBA

2. Query SAS Metadata via SAS code

3. Create a list of Cube names querying SAS Metadata

4. Use the list of Metadata Cube definitions to check if the physical data exists

5. Create your report/alerting process

6. Operationalize the whole stuff