How you should go about naming objects in Analysis Services is another hobby horse of mine – I had a small rant about it in “Expert Cube Development with SQL Server Analysis Services 2008” and I thought I’d mention it here because it’s sure to provoke some debate.
My guiding principle when naming objects (cubes, dimensions, attributes, measure groups or measures) in Analysis Services is to think like you’re designing a user interface and not a database. That’s because a cube really is a kind of user interface: it’s a friendly, easy-to-query window onto all of the data in your data warehouse for non-technical users. That means that any object that the user is likely to see in the metadata, and which will appear in any reports, pivot tables or other queries the user generates, should have a name they understand and want to see. That also means that any techy naming conventions you follow in your relational database design should be completely ignored because, while they might make sense to you as an IT professional, they are likely to be gobbledegook to your users.
The commonest example of bad practice that I see is having Analysis Services dimensions called “Dim Something” – “Dim Product”, “Dim Time”, “Dim Customer” and so on. Hands up who has a cube with dimensions named like this? OK, feel ashamed. Ask yourself, do your users want to go to the CEO with an Excel spreadsheet containing column headers like this? No, of course not, they want to see “Product”, “Time” and “Customer”. They know these things are dimensions already and the “Dim” bit is totally redundant. Similarly, they don’t want to see measures called “Sls_Pct_Chg” or attributes called “CustID” or any of that; and even if you come up with what seems to be a reasonable, human-readable name yourself but it’s still not what the users want they’ll do their best to change it. By not giving them the names they want you’re generating extra work and hassle for them, putting them off using the cube, and making it more likely that different users will come up with different names for the same thing in reports.
Of course this means you have to go and talk to your users about what they want to have their objects called. Since changing an object’s name can end up breaking calculations and any reports that your users have already defined, then you need to do this right at the beginning of your project, even before you’ve run any wizards for the first time. You still need to make sure the names make sense, are consistent, and are acceptable to the whole user community, but ultimately it’s them making the decisions and not you. And if it’s too late to change things now on your current project, remember this post the next time you set about building a cube!
Totally agree with you and too often I see everything prefixed with \’Dim\’ or \’Dim_\’ and I am like ??? Is that really what you want to look at in Excel, PPS, etc.? Probably not, but that is the way it was deployed and by now it is too late. I try to catch people in the forums too when they post questions and point this out as well. Along with the attribute and hierarchy names.
You would be surprised how many cubes out there have "dim" prefix names for dimensions. I also love it when I see the database field name as the name of the measure and suggest that we make it more "user friendly" and the developer says "no, they want it that way, they know what that means". Do they really know what RAR_UN means?
Absolutely true, I almost committed this mistake in my first project but then luckily I had someone to guide me and explain to me then. But now that this issue is raised, would it not help if we had a way of renaming the measures differently in different perspectives? That way, even if there is some cube where the measures have been named awkwardly, we could actually rename them in the perspectives without affecting the existing applications on top of it. Or we could even present the same measure with 2 names to 2 different departments. I have had cases where the users from different departments wanted the same measure with different names.
You are completely right – names MUST be considered user interface in SSAS.This is the reason why we use only VIEWS in DSV to decouple Data Mart tables from SSAS entities. The sql VIEW statement contains the mapping between internal field names and user names, so that any DBA can look at this mapping without the need to open a SSAS project. It is easy to debug. And it can be analyzed by tools like SQL Doc.I think we mentioned that in the "Expert Cube Development … " book and it is an important part of the SQLBI Methodology too (http://www.sqlbi.com/sqlbimethodology.aspx).
Another pet peeve is not naming the "all" level. Drives me crazy when people drag 2 or 3 dimensions on the rows and all they see is "all", "all", and "all"…..
Would it really be so hard for the SSAS team to strip the Dim and Fact prefixes off the auto-generated names for Measure Groups and Dimensions when stepping thought the wizards etc… Given they even implemented word boundary detection. Thought not…
So I am not afraid to come out and say I have done the "Dim" thing before, but I caught myself as well and renamed it inside the Dimension Usage editor. Not sure if that was the lazy way to do it or not. I haven\’t done it since. Funny this topic came up becuase I was just having a discussion with our technical architect about naming columns from the DB to the Cube. An example would be calling name NM in the database and Name in the cube. Or Nbr in the database and Number in the cube. Wondering if any of you have any standards you use for doing DB to Cube translations.
Now that we know what everyone\’s against, what is everyone *for*? Marco–and \’Expert Cube\’–justified views. But that\’s a mechanism, not a naming convention. Any proposals? Naming user hierarchies is always an interesting debate (e.g., \’By <leaf level>\’.) There has to be room for abbreviations, too, in order to avoid column and row labels that use excessive screen real-estate. Personally, I *default* to the same Prime|Modifier|Class standards enforced in ERwin for the RDBMs. With \’state_cd\’, however, I\’ll proper-case the label and remove the \’_cd\’ Class. Yes, there is also a \’state_name\’, but–unlike in the DB–I don\’t have to work hard to distinguish the two to users. Only one is pushed to the foreground in order to simplify the \’UI\’. The assumption is that \’state_name\’ will largely be hidden in the background and displayed as a member property ([State Name]), if at all.
I suppose I\’m \’for\’ whatever the users want, although of course the users generally don\’t know what they want until you give them a few suggestions. Wrt user hierarchies, it\’s a matter of taste but I don\’t like using \’By XXX\’; I generally prefer \’Top Level – Bottom Level\’.
I like to use names that match the warehouse tables, then add translations for what humans read (all languagle fall back to 1033 if you don\’t want to target a specific language). This way you can keep changing the names later on and not break existing reports.Standard Edition you can\’t use translations, so in this case i would do as chris has said.
Hi Chris,
I have confusion on naming hierarchy, attribute and level name of hierarchy whether to name in singular form or in plural. I think naming all of them in singular is good, but in some cases I found attribute name and hierarchy name are in singular form and level name inside the hierarchy are in plural form. Please suggest which will be best to refer. Thanks.
Personally, I always use the singular for all names; I never use plural. Mixing the two is my least favourite – but really, it’s a matter of taste.
Thanks Chris, I also like singular, Plural may not make sense always.
Just to clarify, are you saying leave the tables with their prefixes, such as DimPurpose or FactInternetSales, but change their names inside Analysis Services? Or are you saying to change the table names too? I’m thinking its the first one, but I thought I’d ask just in case.
It’s the first one. Database naming conventions are great for relational databases but different rules apply within SSAS.