TM1 Cube Size - Server Crash

[ViRa]

Hi all,

I need advice on this situation. I have a cube in place that loads the claim details of the members as per the date they were incurred for the accounts they belong to. Hence I have the following dimensions - Version, Year, Month, Accounts, Claim Numbers, Measures. However, there are close to 125 million claims in total which is making the cube larger in size affecting the performance as well as utilizing the existing memory in the server (close to 100 GB). There aren't any rules in the cube.

As an alternative, I could load the cumulative claims for a member in a master cube and drill down to line level claims in a drill cube to know the breakdown of each claims incurred by the member. However, doing this I would again hit the 125 million mark in the drill cube.

I'd appreciate if I can get the advice on any other alternative approaches I can follow to avoid server crash due to memory utilization.

Thanks for your time and help.

[declanr]

If you don't need the claim detail im the cube then just load the data to tm1 at a consolidated level and drill straight to sql to get detail. You can see your drill process up to create smaller temporary cubes if you want to slice and dice the detail.

Worth checking whether the cube size is the issue or just the RAM usage when performing a big data load though.

[ViRa]

Thanks Declanr for the reply.

The users need to see the each claim details incurred and hence I need that in the cube. If I add the claim number as a measure, then TM1 would store only one record for the month/year combination for that particular account in a cell. Since I need to see each of the claims in teh cube I've created the Claim Number dimension as 'Claim Number' as consolidated element and 'Claim Line Number'' as leaf level element.

You can see your drill process up to create smaller temporary cubes if you want to slice and dice the detail.

Could you please elaborate on this? I'm using ODBC data source to load the cube and hence the data is being fetched directly from SQL.

[ViRa]

Also, the RAM utilization increases whenever the cube load process is run.

[declanr]

Thanks Declanr for the reply.

The users need to see the each claim details incurred and hence I need that in the cube. If I add the claim number as a measure, then TM1 would store only one record for the month/year combination for that particular account in a cell. Since I need to see each of the claims in teh cube I've created the Claim Number dimension as 'Claim Number' as consolidated element and 'Claim Line Number'' as leaf level element.

Then set Claim Number as your n level and drill to sql to see the detail.

You can see your drill process up to create smaller temporary cubes if you want to slice and dice the detail.

Could you please elaborate on this? I'm using ODBC data source to load the cube and hence the data is being fetched directly from SQL.

You set up a drill to a cube view instead of sql but that runs a sub process that populates the cube with the data from sql they want to drill to. You clear the old data each time; so the cube only has the data the user most recently requested. You will need a clients dimension in the cube.

[declanr]

Also, the RAM utilization increases whenever the cube load process is run.

What I meant is "does that usage go back down when the load finishes"; if so you can just load in smaller chunks

[ViRa]

ViRa wrote:
Also, the RAM utilization increases whenever the cube load process is run.

What I meant is "does that usage go back down when the load finishes"; if so you can just load in smaller chunks

No, it stays on top (full utilization) unless the server is shut down.

You set up a drill to a cube view instead of sql but that runs a sub process that populates the cube with the data from sql they want to drill to. You clear the old data each time; so the cube only has the data the user most recently requested. You will need a clients dimension in the cube.

Thanks for the suggestion, although I'm yet to fully understand the solution you suggested. I'll try my best before I approach again.

Thanks again for your time.

[tomok]

Do this:

I could load the cumulative claims for a member in a master cube

and then you can create a drill process on that cube that will go against the ODBC data source to bring back the details for any specific customer whenever the detail is needed.

Doing this:

I have a cube in place that loads the claim details of the members as per the date they were incurred for the accounts they belong to. Hence I have the following dimensions - Version, Year, Month, Accounts, Claim Numbers, Measures. However, there are close to 125 million claims in total

is absolutely crazy. Your scenario is a perfect example of why they created the drill process against a relational data store functionality. Use it.

[ViRa]

is absolutely crazy. Your scenario is a perfect example of why they created the drill process against a relational data store functionality. Use it.

Thanks Tomok for the reply. I haven't done drilling against ODBC datasource before. I'll go through the Developer's guide and try.

[declanr]

I agree enitely with tomok but I hate drilling directly to SQL as it looks so ugly in the web; hence my half and half suggestion of drilling to a cube where you load small sub sections of data on the fly. I use that method all the time and it works well for customers who want data they can manipulate.

[ViRa]

Thanks Declanr...I'll try the suggestions and update the post.

[ViRa]

Hi Tom and Declar,

As per your suggestion, I've divided the cube into master and detailed cube. Master cube holds consolidated claim amounts as measure values. Also, I've added a dimension called 'Accounts' and have listed all the members within each account as its elements. So the master cube now displays for a time period (eg. 201301) all the consolidated dollar values against each clients and its members.

Detailed cube will have measures that show the breakup of the claim amount at the individual line level for the selected time period.

In the drill process, I will use ODBC (SQL) as the datasource. However, I'm not sure on the syntax for passing the appropriate TM1 dimension variables in the query. I did go through the Developer Guide and the example talks about Access Database.

I'd really appreciate if you could guide me on the details to be passed on to the query so that it fetches the detailed level data for the selected member.

Thanks for your time and help.

[tomok]

Right-clicking on a cell and choosing the drill process will pass X number of parameters to the process, where X is the number of dimensions in the cube. Each parameter variable will be named the same as the dimension name and the value in the variable will be the element represented by the cell you clicked in, You can take these variables and substitute them in the SQL string by using ?VariableName?. So, you build your SQL query to pull back only what you want by using the ?VariableName? syntax, most likely in the WHERE clause. In your case, it sounds like you'll need to sum up some fields as well in SQL. You may need to get your DB guys involved to help you with the query.

Example:

Code: Select all

SELECT Field1, Field2, SUM(Field3) as Total WHERE Field1='?Dim1?' AND Field2='?Dim2?'

[ViRa]

Thanks Tom for time and reply. I'll definitely try as per your suggestion and update accordingly.

[ViRa]

Thanks a lot Tomok for your suggestion. I was able to fetch the results on the fly using ODBC datasource in my drill process. Thanks again!