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.
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... 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