06-14-2018 12:33 PM
I am not sure where to post this. I do not see a board for ABM or the new name CPM (Client Profitability Management)
We are running SAS / ABM V7.2 (SAS 9.3) on top of Oracle 12c.
What I would like to is to create a query in Oracle that would give me the table names of the cubes for a given model. I would like to get results similar to:
Model name Model Ref Model ID Cube Name Table Name
TS201706. TS1706 M2529 TSTC1 M2529_C4531001
I have a model ID 2529 where I have the cubes built on the model. When I look in oracle i see three tables that represent the cubes (M2529_C4531001. M2529_C4533001, M2529_C5436001).
Model Name is TS201706, Model Ref TS1706 and Model Id M2529
I can look in the ABM Client to see that TSTC1 cube is M2529_C4531001.
I have query that gets me the three pieces of data on the model
select 'M' || mdl.id as Model_Id, wso.name as Model_Name, wso.refnum as Model_Reference
from SASABM.workspacenode wsn
join SASABM.workspaceobject wso on wso.id = wsn.objectid
join sasabm.model mdl on mdl.name = wso.name
where wso.type = 1;