Using dimension PickList to filter data on import to cube

Post Reply
Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Using dimension PickList to filter data on import to cube

Post by Mark » Mon Jun 18, 2018 4:43 am

Hi all,

A quick bit of background. I am an experienced EP modeler and new to TM1, yet to get the IBM training. I am in the process of self teaching on our 10.2.2 installation currently using TM1 Performance Modeler.

I want to replicate a technique quite often used in EP.

I have a 2 dimensional cube. Dimension A is just a simple hierarchy dimension. Dimension B is a calculation dimension.

Dimension B has a text Picklist defined from another Dimension.

I can successfully import data into this cube. However, the data imported into the Picklist slice contains values outside of the picklist that can not normally be picked by the end user.

In EP the typical behaviour is that these values will not be imported and there is a blank.

How can I achieve this? Is there a setting somewhere? Is there something I need to do post import?

Thanks in advance for any help.

Mark.

declanr
MVP
Posts: 1588
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Using dimension PickList to filter data on import to cube

Post by declanr » Mon Jun 18, 2018 7:09 am

In TM1 picklists are purely a user interface restriction, TI and/or rules can ignore them.
The only way around it is to place logic in the TI that performs the same function, so as its a dimension picklist you have mentioned you would have an If Dimix in the TI.

Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Using dimension PickList to filter data on import to cube

Post by Mark » Mon Jun 18, 2018 5:32 pm

Awesome thanks for the pointer. I will try this and report back.

Thank you! :)

Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Using dimension PickList to filter data on import to cube

Post by Mark » Tue Jun 19, 2018 1:07 am

declanr wrote:
Mon Jun 18, 2018 7:09 am
In TM1 picklists are purely a user interface restriction, TI and/or rules can ignore them.
The only way around it is to place logic in the TI that performs the same function, so as its a dimension picklist you have mentioned you would have an If Dimix in the TI.
Hi Declan,
Im looking at this now, I think ive understood the basic syntax of the DIMIX but not how to correctly evaluate the data to be imported against the picklist. I am also unfamiliar with the best/correct place to implement this in TM1 PM.

I originally tried to create an expression in the Process but couldn't get my syntax right.
I then found the following automatically generated statements in the Prolog. The following appears to be the code block that populates what I am looking to validate against the picklist.

if (DIMIX('3 Major Project', 'Major Project Number') = 0);
DimensionElementInsertDirect('3 Major Project', '', 'Major Project Number', 's');
endif;

I also under 'data' found the statement that populates the data in the cube and butchered that - it still runs the process, but doesn't achieve my desired results.

This was my original effort.
IF(DIMIX('2 Major Project PL List',
CellPutS(Planning_Project_Code, 'Major Project Assumption', job_unique_id, 'Major Project Number'))<>0);
THEN CellPutS(Planning_Project_Code, 'Major Project Assumption', job_unique_id, 'Major Project Number');

With some googling I scraped together the following which I think is more syntactically correct.
IF (DimIx ('2 Major Project PL List', Planning_Project_Code) > 0);
CellPutS(Planning_Project_Code, 'Major Project Assumption', job_unique_id, 'Major Project Number');
ELSE;
CellPutS('', 'Major Project Assumption', job_unique_id, 'Major Project Number');
ENDIF;

Im guessing im not targeting the right area of the Process to insert my code. Is my thinking/logic behind the data tab modification on track or way off?

Thanks in advance for your patience as I appreciate this may be a pretty novice question. I will keep popping updates to my post as I explore further.

Cheers,
Mark.

David Usherwood
Site Admin
Posts: 1337
Joined: Wed May 28, 2008 9:09 am

Re: Using dimension PickList to filter data on import to cube

Post by David Usherwood » Tue Jun 19, 2018 5:38 pm

Suggest
IF (DimIx ('2 Major Project PL List', Planning_Project_Code) > 0);
CellPutS(Planning_Project_Code, 'Major Project Assumption', job_unique_id, 'Major Project Number');
ELSE;
itemskip;
ENDIF;
Also - I don't think you should be adding elements into the dimension using
if (DIMIX('3 Major Project', 'Major Project Number') = 0);
DimensionElementInsertDirect('3 Major Project', '', 'Major Project Number', 's');
endif;

Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Using dimension PickList to filter data on import to cube

Post by Mark » Tue Jun 19, 2018 9:42 pm

Thanks David! I’m back in my office Thursday and will be trying this First thing!

I’m gonna guess the item skip function is exactly what I wanted as I was trying to ignore bringing the data in.

Thanks again I’m really excited to see if this works and will report back! :)

Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Using dimension PickList to filter data on import to cube

Post by Mark » Wed Jun 20, 2018 11:31 pm

Hi David,

Unfortunately this seems to return the same result. I have given the code below a shot as written.
IF (DimIx ('2 Major Project PL List', Planning_Project_Code) > 0);
CellPutS(Planning_Project_Code, 'Major Project Assumption', job_unique_id, 'Major Project Number');
ELSE;
itemskip;
ENDIF;
The first time I didn't clear the data from the cube and the second time I cleared the data just to make sure.

Regarding the DimIx function as per the manual it reads: DIMIX(server_name:dimension, element)

I want to translate the logic back just to make sure ive correctly given the information in my first post.

In my case, for the DimIx function:
- '2 Major Project PL List' is the PickList.
- Planning_Project_Code is the data column from the SQL database query im wanting to populate.

For the CellPutS
- Planning_project_code is as above
- Major Project Assumption is the cube im populating
- job_unique_id the dimension elements im matching to (this Dimension is called '2 Active and Inactive Projects' - not actually referenced anywhere else)
- 'Major Project Number' is the element of the dimension '3 Major Project' which is not referenced anywhere else, it is the element that has the PickList applied to it which is '2 Major Project PL List'



One thing to note I get repetitive 'Import completed with minor errors':
Multiple lots of:
320428 [2a] ERROR 2018-06-19 06:08:44.837 TM1.Process Process "CUBE Major Project Assumption": finished executing with errors. Error file:

and in the expanded link to the log for a bucket load of records:
-->"0262717-00000000-C010001","0262717 - COM-Header drain pump","External",Data Source line (1) Error: Data procedure line (9): Invalid key: Dimension Name: "2 Active and Inactive Projects", Element Name (Key): "0262717-00000000-C010001"
-->Error: Data procedure line (9): error repeats 1 times


Reading this message I understand that:
- The Key "0262717-00000000-C010001" from job_unique_id cant be found in the dimension elements of "2 Active and Inactive Projects"

This is acceptable as the match fails as expected - because due to no match it should not have a value at all for the intersection im trying to populate.

Finally, to note, ive validated the Cube import process SQL query, dimension SQL query, and Picklist file import data for an example that should, and one that shouldn't work. Just incase I had botched this up.

Im completely stumped - I think ive structured it right. To note, this code is being added to the 'Data' tab of the process.

Thanks again for patience and any help.

Cheers,
Mark.

User avatar
PavoGa
Community Contributor
Posts: 262
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Using dimension PickList to filter data on import to cube

Post by PavoGa » Fri Jun 22, 2018 12:25 pm

You might want to add ASCIIOUTPUT or LOGOUTPUT in the ELSE block and output the values being tested and results. For example:

Code: Select all

ASCIIOUTPUT(sErrorFileName, EXPAND('--> %job_unique_id%  %Planning_Project_Code%'));
or

Code: Select all

LOGOUTPUT('ERROR', EXPAND('--> %job_unique_id%  %Planning_Project_Code%'));
I only use the latter if expecting just a few results. Do not want to fill the server log file with lots of unnecessary lines. Also, the "-->" has no significance. I just find it easier to spot those lines in the server log with that in it.

Another useful tip: if you do this:

Code: Select all

nProjectCodeExists = DIMIX('2 Major Project PL List', Planning_Project_Code);
IF (nProjectCodeExists > 0);
You can then do this in your output line:

Code: Select all

ASCIIOUTPUT(sErrorFileName, EXPAND('--> %job_unique_id%  %Planning_Project_Code% %nProjectCodeExists%'));
EXPAND allows you to mix string and numeric variables without having to do NumberToString conversions.
Ty
Cleveland, TN

Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Using dimension PickList to filter data on import to cube

Post by Mark » Sat Jun 23, 2018 12:10 am

Thank you, I’ll try this next. I tried validating the logic with a simple if criteria manually defining strings and what not to no success.

I’m also going to strip the example right back and use some simple dummy data of a few manually created elements.

I’ll report back :)!

Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Using dimension PickList to filter data on import to cube

Post by Mark » Mon Jun 25, 2018 8:34 am

Hi all,

So great news, I got things working - however im confused as to the mechanics. (the Ascii output was also very helpful- thanks).

In short I am able to create me process in architect and get it working.
Specifically because it seems I need a column to indicate the 'element' im working with on the calc dimensions. To review:
- my job_unique_id is an element mapped against the '2 active and inactive projects'.
- I created a new placeholder column that contained 'Major Project Number' as an element mapped against my calc dimension '3 Major Projects'
- finally i map in the data which is the 'Planning_Project_Code'.
- use my DimIx test and it goes smoothly.

however if i tried to create the same process in archiect without the placeholder in point 2, I get an insufficient dimension count error and can not map the data.

To compare this to TM1 Performance Modeler, if im mapping a column to a calc dimension measure i dont have the facility to map the placeholder from item 2, and subsequent processing of the data wont successfully perform the check.

Can anyone shed any light on the diferences? (i also not that when looking at a architect build process, there is no 'map' tab to drag and drop from my ODBC query to cube structure.

Also to note, I cant execute the working architect process from within performance modeler. it throws an error with no associated information.
Cheers,
Makr.

Post Reply