Tabular HideMemberIf

SQL Server Data Tools 2012 (formerly BI Development Studio) Tabular projects allow adding hierarchies to tables. However, a UI for setting the HideMemberIf property is not provided. This prevents Tabular projects from creating ragged hierarchies. For example, in the Adventure Works Employee hierarchy, Stephen Jiang not only is a manager, but has also sold some products. Without setting HideMemberIf, a blank shows up in the hierarchy (left PivotTable). After setting HideMemberIf on Level3 to NoName, the blank employee name row drops out as desired (right PivotTable):

HideMemberIfPivot.png

Warning: While the HideMemberIf setting works in Tabular models, it is not officially supported by Microsoft. If you encounter a bug in how Tabular handles HideMemberIf and open a support case, Microsoft may not provide support. For example, there are reports that Tabular HideMemberIf will cause Visual Studio to hang. There are other reports that HideMemberIf doesn't work in PerformancePoint with Tabular models.

In order to set HideMemberIf, switch to the diagram view where you manage hierarchies. Highlight one or more levels then right click and choose Set HideMemberIf...

TabularHideMemberIfMenu.png

When the dialog pops up set HideMemberIf. When you click OK the change is applied to the workspace database immediately. ProcessFull is required during this operation due to a bug in SSAS 2012.

HideMemberIfDialog.png

Note that some changes like renaming levels will wipe out the HideMemberIf setting. BIDS Helper backs up the HideMemberIf setting in an annotation on the database. That way, the Tabular Pre-Build feature can prompt you to fix this setting.

After changing a HideMemberIf setting, BIDS Helper may prompt you for the credentials to a data source which is using stored credentials if you have not already entered those credentials during this SSDT editing session. This prompt ensures that Tabular HideMemberIf succeeds (since it needs to ProcessFull the table) and also that data source credentials in the workspace database do not get wiped out as HideMemberIf settings are applied.

Last edited Tue at 1:57 AM by furmangg, version 7

Comments

No comments yet.