CellGetS from Cube B and AsciiOutput it in Cube A is not working

[ViRa]

Hi all,

I've tried looking around in this forum and online to my best before posting this new topic. I Could not find any guidance. Please my only hope now is any of you all assisting me.

Cube A (Master cube) stores detailed demographic details of members by Account, Model, Type and Time dimensions. Cube B (cube stores the choices of Account, Model, Type and Time made by the users through a front-end). The number of dimensions and its structure is not the same in both the cubes. Account dimension in Cube A has Group Number as leaf level whereas in Cube B the Account numbers are the elements. Similarly, Model in Cube A has further levels as leaf whereas in Cube B its just the Model as elements.

The requirement is to output the details from cube A for only those Model, Type and Time chosen by users stored in Cube B for an account.

I plan to Asciioutput from Cube A during the cube load process. The idea is to get the 'Account' Number from Cube B (CellGetS) and compare it with Account in Cube A for every record using IF condition within a While loop. If matched, the code will compare the 'Model' from Cube B with Cube A and similarly the 'Type' will be matched. Once these three IFs match, the Asciioutput will save the details in the required txt format in a location for only the matched Models, Type and Accounts.

The problem is when I CellGetS the Account Number from Cube B and test to see what number it has fetched by Asciioutputting the Account number in Cube A, it is returning blank strings. If the same account number is Asciioutputted from Cube B, it shows correct number. I dont understand what mistake I'm making that it is fetching blank string from Cube B.

Without the CellGetS getting the account number from Cube B, the IF condition is not matching and hence no output is being generated.

The code written in the Cube A is -

Code: Select all

CubeA_AcctNum =  CellGetS('CubeA',PYM,vProd_Level3_Cd,RISK_TYPE2_CD,Acct_MemProf,Act_Grp,'Acct_num');

CubeB_AcctNum = CellGetS('CubeB',PYM,Model,Acct_Num,'Account Number');

Asciioutput('E:\test.txt', CubeB_AcctNum);

I request you all to please help me. You time is highly appreciated.

Thanks

[ViRa]

Please note, the elements that make up the dimensions in Cube B are present in the Cube A Variables tab either as 'Other' or 'Data'. Hence the syntax of CellGetS is not throwing any error and the process runs fine. Despite that, it fetches empty string from Cube B.

[Steve Rowe]

If your code is compiling and executing without error your best bet is asciioutput all the variables in your two CellGetS so you can gain a better understanding of what is going wrong.

Though you have provided a lot of detail I'm not sure it is enough to be able to tell you the error you are making.

Cheers,

[tomok]

it's pretty obvious why our CellGetS is returning a blank. It's because you are pointing to a spot in Cube A where the Acct_Num measure is blank. Now, your task is to figure out why. There's no way anyone else can help you with this. You're the one with all the data. Like Steve suggested, you should put in ASCIIOutput statement in your code that will let you see what actual values you are passing into the CellGetS function, more specifically, the values for PYM, vProd_Level3_Cd, RISK_TYPE2_CD, Acct_MemProf, and Act_Grp,

[ViRa]

Thanks Steve and Tom for your time.

I did asciioutput the account number in each of their respective cubes and CellGetS returns correct results when used in their own cubes. That is, CellGetS('CubeA',PYM,vProd_Level3_Cd,RISK_TYPE2_CD,Acct_MemProf,Act_Grp,'Acct_num'); in Cube A returns results and
CubeB_AcctNum = CellGetS('CubeB',PYM,Model,Acct_Num,'Account Number'); in Cube B returns the results. Same with Models and Types.

The issue is just when I'm trying to use the CellGetS to bring in the account number from Cube B into Cube A. Should the dimension structure be same in both the cubes for CellGetS to work? As long as the elements are present in Cube A, shouldn't the CellGetS bring in the result from Cube B and output in Cube A? Sorry if my understanding is incorrect. Appreciate your time and help. Please guide.

[Steve Rowe]

As long as a variables value is a valid element name then the reference is valid, irrespective of where you populated the variable name from.

Not sure I can help with the rest, it sounds like you have an underlying issue with the basic logic of what you are trying to do. I'd output the value of all the variables not just the accounts, you need to check everything..

Cheers,

[Wim Gielis]

Indeed, use AsciiOutput and limit the source (view) to be able to understand what is going on. Line by line, variable by variable until you find your flat - either in the logic, data, ... The cube structure is allowed to be different. Just make sure you use the correct coordinates for the cube lookups. If needed, use hard-coded elements to help you in visualizing the problem and concentrate only on what is important (comment out the rest of the code).

[tomok]

The issue is just when I'm trying to use the CellGetS to bring in the account number from Cube B into Cube A.

This makes no sense. You can't bring anything from one cube to another with CellGetS. That function can only do one thing: Retrieve string value from a cube, nothing more, nothing less. If you are trying to move something from one cube to another you would need a CellGetS coupled with a CellPutS.

[Karthik1710]

I am not sure if I understand your requirement completely.

Cube B (cube stores the choices of Account, Model, Type and Time made by the users through a front-end).

These details in Cube B(Account, Model, Type and Time) are stored as measures? What are the dimensions you have for this cube?

In general, this looks like a pretty straight forward requirement to me. You have a cube with few dimensions. You need users to filter out the data by entering some Accounts, Types etc. This could be easily done by a simple websheet. Why do we even need a secondary cube?

[ViRa]

Thanks all for your time to look into my issue and provide suggestions.

Indeed, use AsciiOutput and limit the source (view) to be able to understand what is going on. Line by line, variable by variable until you find your flat - either in the logic, data, ... The cube structure is allowed to be different. Just make sure you use the correct coordinates for the cube lookups. If needed, use hard-coded elements to help you in visualizing the problem and concentrate only on what is important (comment out the rest of the code).

Yes Wim, I'm trying to do the same. I'm trying to Asciioutput both the cube values and trying several modifications in the dimension structure to make it work. I would need some more time in order to figure out what's going on. I will definitely update all with my results.

ViRa wrote:
The issue is just when I'm trying to use the CellGetS to bring in the account number from Cube B into Cube A.

This makes no sense. You can't bring anything from one cube to another with CellGetS. That function can only do one thing: Retrieve string value from a cube, nothing more, nothing less. If you are trying to move something from one cube to another you would need a CellGetS coupled with a CellPutS.

I agree Tom that we need CellPutS in order to update the cube. What I meant in my statement was to bring in value from a cube and just output it (using ASCIIOUTPUT) in another. I'm not trying to update the cube with value from other cube. Sorry, if my statement came across as confusing.

I am not sure if I understand your requirement completely.

Cube B (cube stores the choices of Account, Model, Type and Time made by the users through a front-end).

These details in Cube B(Account, Model, Type and Time) are stored as measures? What are the dimensions you have for this cube?

In general, this looks like a pretty straight forward requirement to me. You have a cube with few dimensions. You need users to filter out the data by entering some Accounts, Types etc. This could be easily done by a simple websheet. Why do we even need a secondary cube?

Karthik, From a front-end application, the users will choose the Account, Model, Type and Time for which they need the details (Cube A is the detailed cube). These choices which will be stored in the back-end (SQL) will be used to update Cube B. Thus, Cube B now stores these choices (as measures) against respective Account, Model and Time Dimension. So yes, Account, Model, Type are dimensions as well as measures (including Type).
I now need to bring in these choices from Cube B into Cube A and the IF condition in Cube A will match the account numbers and asciioutput the results for only those selections made by the user.

Thanks

[Wim Gielis]

Vira,

From reading this, I'm still wondering whether you know the difference between AsciiOutput and CellPutS. Do you?

[ViRa]

Vira,

From reading this, I'm still wondering whether you know the difference between AsciiOutput and CellPutS. Do you?

Hi Wim, my understanding is that Asciioutput is used to output the cube cells fetched by the parameter passed in the function to a txt or csv file.
CellPutS is used to load the value fetched by the parameter in the function to a cube cell. For using CellPutS we need to use CellGetS first to fetch the desired value.

Hope this is fine. Thanks.

[Wim Gielis]

Yes, AsciiOutput goes to a text file, while CellPutS writes to a cube and CellGetS reads from a cube.
Good luck in solving the TI problem using AsciiOutput to text files. It's what TM1 developers have to do every day (because there are no alternatives).

[Karthik1710]

Karthik, From a front-end application, the users will choose the Account, Model, Type and Time for which they need the details (Cube A is the detailed cube). These choices which will be stored in the back-end (SQL) will be used to update Cube B. Thus, Cube B now stores these choices (as measures) against respective Account, Model and Time Dimension. So yes, Account, Model, Type are dimensions as well as measures (including Type).
I now need to bring in these choices from Cube B into Cube A and the IF condition in Cube A will match the account numbers and asciioutput the results for only those selections made by the user.

Thanks

Well, are you using Cube B elsewhere? If the sole purpose of this cube is to store selected parameters of users, I personally think it is not required. We do have a couple of such report extracts in our applications. What we do is create dimensions for all these parameters, if not present already, and have 'All' subsets. Then, using a websheet we give options to users to choose from dropdowns('All' Subsets) for each of them. Then using an action button pass the selections to a process that builds a View using subsets created on the fly with the parameters. And ASCIIOUTPUT in Data.

Would that solve your purpose?

[ViRa]

Yes, AsciiOutput goes to a text file, while CellPutS writes to a cube and CellGetS reads from a cube.
Good luck in solving the TI problem using AsciiOutput to text files. It's what TM1 developers have to do every day (because there are no alternatives).

Thanks Wim. I'll try my best.

Well, are you using Cube B elsewhere? If the sole purpose of this cube is to store selected parameters of users, I personally think it is not required. We do have a couple of such report extracts in our applications. What we do is create dimensions for all these parameters, if not present already, and have 'All' subsets. Then, using a websheet we give options to users to choose from dropdowns('All' Subsets) for each of them. Then using an action button pass the selections to a process that builds a View using subsets created on the fly with the parameters. And ASCIIOUTPUT in Data.

Would that solve your purpose?

That sounds like a good solution. Thanks for sharing. However, the users are expecting a fancy yet simple interface with couple of check boxes to make their selections. Hence, the front-end piece came into picture. Anyway, in my case multiple users could be making their choices at a time. Wouldn't that send across too many view requests to be run as TI process when they all hit the Action Button? Moreover, if that slows down the performance, they will have another reason to complain. We plan to output the files on a monthly basis and store it in a location.

[Karthik1710]

That sounds like a good solution. Thanks for sharing. However, the users are expecting a fancy yet simple interface with couple of check boxes to make their selections. Hence, the front-end piece came into picture. Anyway, in my case multiple users could be making their choices at a time. Wouldn't that send across too many view requests to be run as TI process when they all hit the Action Button? Moreover, if that slows down the performance, they will have another reason to complain. We plan output the files on a monthly basis and store it in a location.

Performance should be okay I think. In our case, these reports are executed multiple times a day by multiple users. We export a csv file with User's ID as file name for each run and store it in TM1 server, this way there is no conflict between 2 user files. Anyway, you could try it out and see if your users like it. As long as you make a easy-to-use interface they shouldn't mind.

Thanks

[ViRa]

Hi all,

I was able to get the desired results. Still, CellGetS from Cube B is not being output (using ASCIIOUTPUT) in Cube A. But, when I use that value fetched by CellGetS for further processing in the IF conditions, it is giving me the desired output from Cube A (which is the detailed cube).

Thanks all for giving your time and assisting me. Karthik, I will definitely try out the solution you suggested as well.
Thanks again.

[ViRa]

Hi all,

I was able to get the desired results. Still, CellGetS from Cube B is not being output (using ASCIIOUTPUT) in Cube A. But, when I use that value fetched by CellGetS for further processing in the IF conditions, it is giving me the desired output from Cube A (which is the detailed cube).

Thanks all for giving your time and assisting me. Karthik, I will definitely try out the solution you suggested as well.
Thanks again.

[Wim Gielis]

I was able to get the desired results.

Good !

Still, CellGetS from Cube B is not being output (using ASCIIOUTPUT) in Cube A.

This is again a confusing statement in a confusing topic, while in fact CellGetS, CellPutS and AsciiOutput are simple.
But if the topic is solved for you, let's keep it like that.