Not all the elements were created within the dimension

[ViRa]

Hi all,

I've created a dimension using TI process with CSV file as the data source. The element - member number ('Mem_Num') in the data source file has 15000 entries. However, when I create the dimension, it creates only 266 elements within it. I know the limit of number of elements in a dimension is definitely more than 266. Could anybody please let me know why only limited number of elements are getting created.

Appreciate your time and response.

Thanks

[Alan Kirk]

I've created a dimension using TI process with CSV file as the data source. The element - member number ('Mem_Num') in the data source file has 15000 entries. However, when I create the dimension, it creates only 266 elements within it. I know the limit of number of elements in a dimension is definitely more than 266. Could anybody please let me know why only limited number of elements are getting created.

(a) It would help if you posted the code that you were using to do this, including which tab of the TI process you're doing it on, as well as an example of the data source.
(b) Have you checked the log file directory to see whether there's an error log?
(c) You say that the data source has 15000 entries but are they 15000 unique entries?

[ViRa]

Hi Alan,

a) In the 'Variables' tab, I defined the Mem_Num as 'String' and the Content as 'Element'. In the Maps tab -
Cube - No Action
Dimensions - I entered a new dimension name (Eg. AE_Mem)and the Action changed to 'Create' and Element Order was set as 'By Hierarchy'
Please note that the member number is getting converted to scientific notation in csv or xls format. So to convert the scientific notation number into a string, I added a function in Metadata tab as below -

Code: Select all

DimensionElementInsert('AE_Mem', '', NumberToString(Roundp(MEM_NUM,2)), 'S');

When this process was saved and run, the new converted elements got created below the scientific numbers in AE_Mem dimension. However, both scientific notation elements and the converted member numbers were 266 each. Please refer the below screenshot -

Scientific-Converted.doc

Screenshot showing elements created

(113.5 KiB) Downloaded 355 times

Unfortunately, I'm unable to send the data source image as it contains user's social security number. However, I can explain it further in case you have any questions on the data source.

b) There are no errors since the process ran successfully.

c) You are correct. I verified and noticed that there were duplicate entries. So I filtered only unique entries out and there are 2333 unique entries.

[ViRa]

Hi Alan,

Could you please guide me on what I'm missing?

[Sebastian.Klein]

Why do you create your elements as text elements? It is just a guess, but it could be that there exists an undocumented limit of text elements in a dimension.
Try to change the element type to numeric and import the file again.

One further question: You said you have defined Mem_Num as 'String' and the Content as 'Element'. Normally you would then have generated code in you TI, but the screenshot doesn't show it. Beneath that fact your posted code (and that one in the screenshot) would not work with Mem_Num set to 'String' because the RoundP function expects a number not a string. Did you describe the right process or posted the right code?

[ViRa]

Thanks for your reply Sebastian. I did post the details of the process I'm facing issue with. Since the excel shows duplicate entries as well and TM1 considers only unique, the number of elements in the dimension mismatched.

So currently the cube structure is such that it shows the $ amount claimed by members for a month. Some members have more than one claims in a month. Right now, the cube shows only the latest claimed amount rather than showing all the claims made by the user date-wise. I tried to overcome this issue by using 'Accumulate Values' option in the TI process loading the cube but doing this adds up all the claims and shows as one entry in the cube against the latest date rather than showing the breakup. Eg, if member has had 3 claims of $10 each for 3 different days in a month, the cube shows $30 against the latest date instead of the three separate entries of $10 each.

I know this is a different issue than what I posted initially but since I got to know the cause of the original issue, I'm continuing in the same thread. Could anybody please guide me on showing individual results rather than a summed up result in the cube? Appreciate your time and help.

Thanks

[tomok]

I know this is a different issue than what I posted initially but since I got to know the cause of the original issue, I'm continuing in the same thread. Could anybody please guide me on showing individual results rather than a summed up result in the cube? Appreciate your time and help.

You're kidding, right? You have data by day and want to maintain this detail when loading into a cube. Wouldn't this mean expanding your time dimension to capture that data by day?

[ViRa]

Wouldn't this mean expanding your time dimension to capture that data by day?

There are cases where the claims have been incurred on the same day but the $amount has been split. Eg. on Jan 18, 5 claims have been made. For such cases, I have built a separate cube to load day-wise data and using drill process trying to see the detailed results from the origination cube.

However, even in the day-wise cube, those 5 claims are either showing up as only the latest claimed amount (when 'Store Values' is used in load cube TI process) or the total amount is displayed on the same date (when 'Accumulate values' is used). I thought day-wise cube would split the results for the day when I select the appropriate month.

Any suggestion will be helpful.

[declanr]

Wouldn't this mean expanding your time dimension to capture that data by day?

There are cases where the claims have been incurred on the same day but the $amount has been split. Eg. on Jan 18, 5 claims have been made. For such cases, I have built a separate cube to load day-wise data and using drill process trying to see the detailed results from the origination cube.

However, even in the day-wise cube, those 5 claims are either showing up as only the latest claimed amount (when 'Store Values' is used in load cube TI process) or the total amount is displayed on the same date (when 'Accumulate values' is used). I thought day-wise cube would split the results for the day when I select the appropriate month.

Any suggestion will be helpful.

If you have a cube that has the date dimension split out the lowest level to day... you will only be able to show 1 value for that day (whether its an accumulation of multiple records from elsewhere or not) if you want to see it at a per record level you need to split the dimension further (e.g. "01-01-01" as a consolidation of "01-01-01-Record 1", "01-01-01-Record 2" etc), or consider adding a "claim" dimension into the cube but that would add a lot of sparsity that is unnecessary. TM1 is not a relational database.

I would consider setting up drill processes through to the source database. So TM1 would just hold the accumulated value for the day (and possibly another measure that shows how many claims that includes) then when they click "drill" it takes them straight through to the database.

[tomok]

Any suggestion will be helpful.

My suggestion is that you either take a training class or read ALL the documentation for TM1. Understanding that a cube can only hold one value per cube cell is a fundamental concept of OLAP databases and something you clearly don't have a handle on.

[ViRa]

Thanks Declanr for your guidance. I was able to get the desired results based on your suggestion. Since the data source here was csv file, I could not redirect to database, but thanks for explaining me the alternatives.

Yes Tomok, I'm fairly new to the development side and hence such question from my side.