Dealing with UDM Configuration Settings

Sometimes, you may need to have a configuration setting that is stored outside UDM. For example, you may need to store a connection string to a database that will supply the allowed set for dynamic security. As with SSRS, you can use the msmdsrv.exe.config file for this purpose like so:

<configuration>

<startup>

<requiredRuntime
version=v2.0.50727
safemode=true />

</startup>

<runtime>

. . .

</runtime>

<appSettings>

<add
key=connectionString
value=“connection string here />

</appSettings>

</configuration>

 

This shouldn’t come to surprise since the SSAS server process hosts a .NET application domain to execute SSAS stored procedures. Once the configuration settings are defined, you can read them with a single line inside a SSAS stored procedure:

public
static
string GetConfigSetting(string settingName) {


return settingValue = ConfigurationManager.AppSettings[settingName]; // need a reference to System.Configuration

}

Kudos to Report Builder as UDM Client

Its UDM support limitations withstanding (a partial list here), the Report Builder should definitely be on your list if your requirements call for ad-hoc reporting from an SSAS 2005 cube. Here are some Report Builder features that I particularly like:



  1. Ability to pre-filter the data on the report before the report is run. In comparison, Excel takes the optimistic approach to load all dimension members before allowing you to set a filter. This can surely send Excel in a la-la land if your cube has large dimensions (hundred thousand or more).

  2. Support of calculated columns. For some obscure reasons, the Excel UI doesn’t have an UI interface for creating calculated members (you can do so programmatically however).

  3. Standard report look and feel. In comparison, Excel limits the user to the PivotTable fixed layout (assuming that the user doesn’t convert the report to formulas).

  4. My Reports which allows the business user to save her reports in her own area of the report catalog if she doesn’t have rights to write to other folders.


  5. Nice filter support. This may not be so obvious but each SSAS attribute hierarchy supports an InstanceSelection property which the Report Builder honors. For example, the attached image shows what happens if you set InstanceSelection to List or FilterList (no UI difference between the two). Another filter UI selection is dropdown.

Of course, the Report Builder-UDM integration will improve in future releases (believe me, I am banging hard on the SSRS-SSAS integration door) to make it even more compelling choice for UDM reporting.