2
Vote

HideMemberIf on Tabular has very slow response or generates timeout error

description

I have a ragged hierarchy that is 1 - 10 levels deep. I originally had the formula for each level set so that it returned the same name as the node above if the depth of the leaf node didn't go down to that level. I tried to use HideMemberIf = ParentName, but I kept getting timeout error messages from BIDS Helper.

I changed my formulas to just return blank if the leaf node didn't go down to that level and then tried HideMemberIf = NoName. This works eventually, but I have to wait for a good 3 minutes after setting this as SSDT goes to Not Responding for a while. Eventually, it starts responding again, but this is a bit slow and painful for a 10 level hierarchy.

Am I doing something wrong? Is there something I can do to optimize this?

comments

furmangg wrote Jun 5, 2013 at 3:40 AM

Due to a bug in SSAS, we have to do a ProcessFull on the whole table where you're setting HideMemberIf. Other than optimizing processing by optimizing calculated columns and optimizing the SQL query for that table, there's not much you can do. Is it a large table? Do you have high cardinality columns in the hierarchy? Do you have adequate memory or is it having to do a lot of paging because it's short on memory?

Regarding a timeout error, can you connect to SSAS with Profiler and attach the .trc file (or at least tell us the exact timeout error and exactly what command timed out)?

I know the design-time experience is pretty slow due to the full process, but hopefully the feature is still helpful to your users. I'm open to suggestions if you have ideas of making it less painful.

MeaganL wrote Jun 5, 2013 at 7:11 PM

You can close this. I was on BIDS Helper 1.6.3 with SQLServer SP1 and was experiencing extreme slowness while setting the HIdeMemberIf property and more slowness when deploying with that property set. We just installed CU4 yesterday and things are working much better.

The table only had about 50,000 rows in it and was about 40 columns wide. It was an employee table, and I was building an organizational hierarchy of who reports to whom. But things are working but better now without changing anything other than the installation of CU4.

I love BIDS Helper, so please don't take that as a criticism. I just thought there was a bug or something that I needed to change to make it work. Thanks!