creating subset with large amount of elements

[s_wang]

I am trying to create couple subsets and most of them have more than 200,000 N elements. Total N element in the dimension is about 250,000. Those subsets are being used by TI process.

I tried several ways.

1. Query thru the dimension subset 'ALL' and insert to the appropriated subset based on attributes or level number = 0. This took about 2.5 hour.
2. Build the subsets with MDX and turn them into static subset - very quick but cannot destroy the subset because they are used in the views.
3. Query thru the dimension subset that was build by MDX and insert the element from MDX subset - too slow, almost the same time as 1
4. export the elements from MDX subset to a file and insert the element in the file to subset - almost the same result as 1

Any suggestion on building subset with large amount of element. We have 9.5.2.

Thanks

[Wim Gielis]

I am trying to create couple subsets and most of them have more than 200,000 N elements. Total N element in the dimension is about 250,000. Those subsets are being used by TI process.

I tried several ways.

1. Query thru the dimension subset 'ALL' and insert to the appropriated subset based on attributes or level number = 0. This took about 2.5 hour.
2. Build the subsets with MDX and turn them into static subset - very quick but cannot destroy the subset because they are used in the views.
3. Query thru the dimension subset that was build by MDX and insert the element from MDX subset - too slow, almost the same time as 1
4. export the elements from MDX subset to a file and insert the element in the file to subset - almost the same result as 1

Any suggestion on building subset with large amount of element. We have 9.5.2.

Thanks

2. Build the subsets with MDX and turn them into static subset - very quick but cannot destroy the subset because they are used in the views.

You delete the views first, then you delete the subsets. Not the other way round.
If needed, delete the views and subsets only in the Epilog tab, after processing them in the Metadata and/or Data tab.

[s_wang]

This option will not work.

I need the view in order to revise the logic, debugging and all other stuffs. I think I am going to insert a dummy subset in the view that use those subset before I rebuild the subset with MDX turn to static. And reinsert the subsets back to the views. This way I only has one line to insert per view instead of multiple insert lines per view.

I will try this and see how long this will take.

[lotsaram]

This option will not work.

I need the view in order to revise the logic, debugging and all other stuffs. I think I am going to insert a dummy subset in the view that use those subset before I rebuild the subset with MDX turn to static. And reinsert the subsets back to the views. This way I only has one line to insert per view instead of multiple insert lines per view.

I will try this and see how long this will take.

You need the view, ... for what exactly? What are you doing with a view that uses subsets that have 100s of 1000s of elements?

I assume you think that you can't delete the view because you have used it as the data source in a process and if the view is deleted then you will get an error when opening the process, am I right?

What most experienced developers would normally do is to create the process with a temporary manual view it can be any old view. This is solely to populate the preview and variables windows. (This view is just to set the variables, it won't be used for processing data.) Then on the Prolog tab create a view with code, assign subsets to it and assign the new view as the datasource of the process using the DatasourceCubeView function. Then on the Epilog tab destroy the view and subsets.

If you use the bedrock library then the view and subset creation and tidy up is very very easy as you only need to call one TI "function" to create the view and assign all subsets using a concatenated filter string argument and again only one function on the epilog passing in the view name to destroy the view and subsets. If you haven't seen this then you should look into it.

[Andy Key]

For large dimensions, where you insert the element in the subset will make a difference to the time taken.

If you always use 1 as your insertion point this will take longer than inserting at SubsetGetSize( DimName, SubName) + 1 as the cost of performing the SubsetGetSize is less than reshuffling all the indices.

[s_wang]

Thanks. I will try that to see if I can reduce the time.