Create View and Get the Variable name on TI Script

[Guang Ming]

Dear all masters...

I would like to ask you about what I have done here...
I am creating the TI process. In there, I am creating a view on TI using scripts, set the view as the datasource and then pull all data to another cube. All in one TI process. Here is the code that I used.

Code: Select all

CubeName = 'Cash flow';
ViewName = 'ExportDataToCube';
DimName = 'Months';
SubsetName = 'MonthName';

ViewExist = ViewExists(CubeName, ViewName);

if(ViewExist = 1);   
     ViewDestroy(CubeName, ViewName);
 Endif;

ViewCreate(CubeName, ViewName);
ViewTitleDimensionSet(CubeName, ViewName, 'Measure');
ViewTitleElementSet(CubeName, ViewName, 'Versi', 3);
ViewSubsetAssign(CubeName, ViewName, DimName, SubsetName);
ViewColumnDimensionSet(CubeName, ViewName, 'PeriodeMinggu', 1);  
ViewRowDimensionSet(CubeName, ViewName, 'Account',1);
#ViewZeroOut(CubeName, ViewName);

DataSourceType = 'VIEW';
DatasourceNameForServer = CubeName;
DatasourceCubeview = ViewName;

I think I stuck after Im thinking about how to get the variables.

Usually when we create TI and use view as a datasource, the next step is set the variables name on variable tab, and then, we can continue to prolog, metadata, data, and epilog stuff and we write our codes / scripts there. But the problem here, Because I create view manually using script and I have to set the datasourceview, but I dont know how to get these variables. Do you mind to explain to me how to use the variable? I tried to find the answer in this forum to figure out the problems, but I cant find the answer that would solve my problems.

Thanks a lot for your answer.. your explanation would really help me a lot...

[lotsaram]

You should check out the bedrocktm1.org for some ideas as this sort of thing is covered there and there may even be a process that does what you want already.

Nothing wrong with your approach you just need to use a dummy cube view as the "source" for the purposes of defining the variables during development. This view won't ever be used in execution and doesn't even need to be there once development is passed. If your process could be used on different cubes with different numbers of dimensions then you do need to make sure that the cube view used as the initial dummy source contains the maximum number of dimensions you would expect in order to have variables available.

[Guang Ming]

You should check out the bedrocktm1.org for some ideas as this sort of thing is covered there and there may even be a process that does what you want already.

Nothing wrong with your approach you just need to use a dummy cube view as the "source" for the purposes of defining the variables during development. This view won't ever be used in execution and doesn't even need to be there once development is passed. If your process could be used on different cubes with different numbers of dimensions then you do need to make sure that the cube view used as the initial dummy source contains the maximum number of dimensions you would expect in order to have variables available.

I read them already.. Hmm... I think most approaches in bedrock is about running the process that already created before. But what I need to do now is I have to make a new process to create the view, use it as a datasource, pull all data from that view to the cube, then delete the view, all in one process...

[Duncan P]

You said that your problem was with the variables and lotsaram has just told you how to solve it. There is no other way.

The variables part of the process definition is not manipulable in the code sections of the process. You will need to create a dummy cube with at least as many dimensions as the highest dimensionality potential source. Then you need to set that as source for your process on the first tab of the process editor. It will create a bunch of variables which you can rename V1, V2, V3 etc. and which you should mark as "Other" so that they are accessible in the code sections.

When you assign a new view datasource TM1 will use as many of these variables as it needs to give you the data of the source, one for each dimension and one for the value. The rest will be left blank.

Additionally, on the Metadata and Data tabs and regardless of the dimensionality, the data values will always be available in either SValue or NValue and Value_Is_String will tell you which.

[Guang Ming]

Well, thanks again Duncan and lotsaram.... I'm glad you would help me. Now I can understand. Firstly I need to create TI that creates only view, right? Then, I need to prepare the dummy cube where store all views that I'm gonna use. And on the 2nd process is: use the created view as the data source. Hopefully it helpful too to other readers. Thanks again guys...