First published on TECHNET on Feb 10, 2011
There really isn’t a lot of information out there about reporting so I’m just going to start putting some things out here in the blog as I come across them. Hopefully, these little posts will eventually add up to something meaningful collectively.
This blog post will cover a couple of things –
1) How to join two dimensions – the incident dimension and the incident classification dimension.
2) How to get the display strings for a particular language for a particular enumeration.
Here is the query:
SELECT Strings.DisplayName AS Classification, COUNT(*) AS Incidents
FROM IncidentDimvw Incident
Join IncidentClassificationvw Classification ON Incident.Classification_IncidentClassificationId = Classification.IncidentClassificationId
Join DisplayStringDimvw Strings ON Classification.EnumTypeId = Strings.BaseManagedEntityId
WHERE Strings.LanguageCode = 'ENU'
GROUP BY Strings.DisplayName
Here is a sample of the results:
A couple of interesting things that are good to know:
All enumerations (aka “Lists”) that are brought over to the data warehouse are stored on dimension tables. We also call them “Outriggers”. The display strings for those enumerations are stored on the DisplayString dimension. You’ll need to join across the enumeration dimension table to the DisplayString dimension table to get the localized display string for a given enumeration value.
Also notice how the query above queries the
s (notice the
at the end of the names) not the
directly. This is definitely a recommended best practice. Although it is unlikely that we will change the table schema drastically, having customers use the views instead of the tables provides us the flexibility to change the table schema if we need to in the future without breaking customers’ reports.
More to come on report authoring…