The more I've worked with MicroStrategy, the better I understand that there are alot of things you can do on the platform which were not originally intended. As long as you have an “outside of the box” mentality you should be able to come up with a solution to meet just about any requirement. In a lot of these cases, I've found I needed an anchor within MicroStrategy for the additional functionality I wanted to add. In some cases they're generic hardcoded metrics with values not coming from fact table but more often I use a generic attribute.
Some of the things I have done with a generic attribute have been VLDB SQL injects (which allowed me to alter the reporting SQL), create metric or attribute selector in dossiers (since these don't exist in dossiers out of the box), conditionally suppress sections within a document, or to otherwise manipulate how a document is displayed.
A generic attribute doesn't read for any dimension table but is generated from a predefined list of text. I like to build my generic attributes from a logical table. You can also build them from in-memory cubes within a data import, but you are more limited if you go that route.
To create a generic attribute, first define what you want the generic attribute to return. In this sample I was two attribute elements "Group1" and "Group2"
Next I'll create a logical table in MicroStrategy. This gives me the flexibility to define my list without having to create database view. Now I write out my select statements which produces the results I want. Like this:
Select 'Group1' as atbGroup
Union All
Select 'Group2'
I've defined my data type and I make sure to use a consistent naming convention so that the next developer understand this tables purposes. I use this naming convention "LV_atbGroup".
After refreshing my schema, I define my attribute based on this column in my logical table. Viola! I now have a generic attribute.
I plan on posting some cool tricks and hacks using a generic attribute in the near future.
Thanks for checking out this blog post. If you found this post helpful please consider donating. Any contribution is appreciated! Just click the PayPal icon at the bottom of this page.