Powerplay transformer - Categories related

[mu_ravi]

Hi All,

Could any one please tell us how to limit the categories in the transformer tool.
We have developed a cube using transformer tool , by taking package as the source from framework manager.
We have a dimension called physican name which has 20 000 categories as names. So we want to limit the no. of categories of this physician name.
We are using this cube as source (package) to our reports and hence reports are throwing error due to large no. of categories.
Error: maxrowsretrival  error in report studio.
Please help.

Thanks in advance.

[Arsenal]

the max retrieval row count error in RS can be fixed easily enough. That error should not related to Transformer because Transformer has a very high category limit for a dimension (3 or 4 Million or some such equally high number). If you select the query in RS that is building the report and look in the properties pane, you can change the max retrieval to a high number or just leave it blank without any numbers and the report will generate fine.

If you want to be more efficient and reduce categories to positively impact cube build time then you'll have to have some sort of a filter restricting the names. So for example, let's say you decide to break up the single name dimension into dimensions - Names (A-M) and Names (N-Z). I'll assume the name is stored as last name, first name but if it's the other way then just change the calculated query item below appropriately

In your FM business layer, create a Name Dimension as per normal but create a calculated query item that extracts the first letter of the name and checks to see if it is in "A", "B", "C" all the way till M. If so, give it a value 1 else 2
Now Create 2 query subjects presentation layer in FM - name1 and name2 and each should have a filter embedded into it to  - first one should have calculatedqueryitem (from above) = 1 and second should have calculatedqueryitem=2

then build your dimensions off these 2 query subjects and you should now have 2 different dimensions with smaller categories. Even better if you can have the calculatedqueryitem evaluation pushed into the database so you get the sort value as a number directly fro the DB

kinda brute force way but it should work