A Couple of Report Builder – UDM Hacks

If you target the SSRS Report Builder as an UDM client you may find the following two workarounds (aka hacks) useful. Both hacks require manually updating the SMDL definition. Alas, so much about model auto-generation L.

Multi-grain Relationships Don’t Appear

If a dimension joins a measure group at a higher grain the corresponding Report Builder role is excluded from the model. Steps to repro:

1. Open the Adventure Works SSAS project in the BIDS cube designer and switch to the Dimension Usage tab.
2. Note that the Date dimension joins the Sales Targets measure group at the Calendar Quarter level (not the Date level). That’s because the FactSalesQuota (the fact table Sales Targets is based on) stores the sales quotas at the quarter level in AW.
3. Generate a report model from a data source pointing to the Adventure Works SSAS database
4. Open the Report Builder and connect to the Adventure Works cube.
5. From the Sales Targets folder, drag the Sales Amount Quota on the report canvas.
6. Note that the Entities pane excludes the Date dimension.

Hack: Change the SMDL definition manually to include the dropped relationship. To do so, compare the SMDL definitions side-by-side before and after the grain was changed using a file comparison tool such as SourceSafe or ExamDiff. For example, the first missing role entry should look like the one below:

<RoleID>udm:Role_MeasureGroupDimension_Entity_MeasureGroup_RPM_Profitability_Measures_Entity_Dimension_Date_Entity_Dimension_RPM_Date</RoleID>

</RolePathItem>

</Path>

<AttributeID>udm:Attribute_Hierarchy_Date.Date</AttributeID>

</AttributeReference>

<AttributeReference>

<Path>

<RolePathItem>

In my case, I had to restore three entries caused by raising the grain of a dimension relationship. Once the entries have been restored manually and the model was deployed, the reports worked as expected.

Text-based Calculated Members Are No-Show

An UDM text-based calculated member comes back as empty in a Report Builder report. That’s because the Report Builder defaults the data type of the member to Float. Steps to repro:

1. In the Adventure Works sample cube add the following calculated member:
   
   

   Create Member CurrentCube.[Measures].[Hello]
   As “Hello”,
   VISIBLE = 1;
   

2. Assign the member to a measure group so it will show up in the Report Builder.
3. Create a table report and uses the Hello member. The column comes back empty.

Hack: Overwrite the data type of the member in the auto-generated SMDL:

<Attribute ID=”udm:Attribute_Measure_Hello”>

<Name>Hello</Name>

<DataType>String</DataType>

<Nullable>true</Nullable>

…

</Attribute>

Deploy the model and observe that the text-based calculated member now does show.