I have a string column in a Fact table that I want to show up in my MDX queries. Although I'd normally use member properties for this functionality, because of a data warehousing consideration, I can't go that route. How can I get the column into MDX as a string so that it will show up as a string in my analysis cubes?
It is possible to load text as data into Analysis Services. Because measures can be only numeric, you have to perform a little dimensional modeling to make this approach work. First, you must build an annotation dimension whose Annotation attribute or level is linked to the annotation field in the Fact table. Then, write a calculated measure that, for each cell that the text string will be associated with, retrieves specific Dimension members as a value of the calculated measure. This setup will let Analysis Services expose the string in a cube cell.
You can read a complete step-by-step description of this process in the Microsoft white paper "Handling Annotation and Comments in Analysis Services," (http://www.sqlserveranalysisservices.com/OLAPPapers/Handling%20Comments%20in%20Yukon.htm), and you can read about how to substitute a string for cell coordinates in the article "Cell Annotations," (http://www.sqlserveranalysisservices.com/OLAPPapers/Cell%20Annotations%20in%20AS2005v1.htm).
CLASS="subhead"—Thierry D'hersLead Program Manager
SQLBI Program Management
