May 27, 2015 at 6:37 pm
Hi All
Way out in these parts, we're not cube experts so management hired a developer to create a SSAS database for us. The SSAS database/project we've been delivered currently generates dozens of build "warnings" when we build the project. Almost every object generates one or more warnings. I recall a talk by the estimable Chris Webb a while ago and he was at pains to emphasize that you should not simply ignore the "blue squiggly line" unless you have a really good reason to - in which case you should annotate your reason.
Here is a list of the warnings. I've obfuscated the object names to protect the innocent. If the developer says "Don't worry about! it's fine! Trust me!" should we take his advice? Are some of the issues more serious than others? Are some "sleepers" that might be fine now but will become headaches in the future?
------ Build started: Project: DW, Configuration: Development ------
Started Building Analysis Services project: Incremental ....
Dimension [Blah 1] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 2] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 3] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 4] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 4] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 5] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 6] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 7] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 8] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 9] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 9] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 10] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 11] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 12] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 13] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 14] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 15] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 16] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 16] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 17] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 18] : Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError.
Dimension [Blah 19] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 20] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 21] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 22] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 23] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 24] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 25] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 26] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 27] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 28] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 28] : Define attribute relationships as 'Rigid' where appropriate.
DimensionAttribute [Blah 29].[ Blah 29 Id] : Use numeric key columns for attributes with 500000 or more members.
Dimension [Blah 29] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 30] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 31] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 32] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 33] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 33] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 34] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 35] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 36] : Define attribute relationships as 'Rigid' where appropriate.
Dimension [Blah 37] : Create hierarchies in non-parent child dimensions.
Hierarchy [Blah 38].[ Blah 38] : Attribute relationships do not exist between one or more levels of this hierarchy. This may result in decreased query performance.
Dimension [Blah 39] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 40] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 41] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 42] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 43] : Create hierarchies in non-parent child dimensions.
Dimension [Blah 44] : Create hierarchies in non-parent child dimensions.
Hierarchy [Blah 45].[ Blah 45] : Attribute relationships do not exist between one or more levels of this hierarchy. This may result in decreased query performance.
Dimension [Blah 45] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 46] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
Dimension [Blah 46] : Define attribute relationships as 'Rigid' where appropriate.
Cube [Blah 47] : The ' Blah 47' and ' Blah 47' measure groups have the same dimensionality and granularity. Consider unifying them to improve performance.
Cube [Blah 48] : The 'Blah 48' and 'Blah 49' measure groups have the same dimensionality and granularity. Consider unifying them to improve performance.
Cube [Blah 48] : The ' Blah 48' and ' Blah 50' measure groups have the same dimensionality and granularity. Consider unifying them to improve performance.
Cube [Blah 48] : The 'Blah 48' and 'Blah 51' measure groups have the same dimensionality and granularity. Consider unifying them to improve performance.
Build complete -- 0 errors, 58 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
May 30, 2015 at 6:18 am
*Sucks teeth like a mechanic*
Oooookay. As ever with this type of thing the answer is definitely "it depends".
I would never completely ignore them though (particularly 2, 4 and 6 - if the developer tells you "it's fine" for these please feel free to assault them with a snooker cue).
I shall share with you my thoughts on all of these warnings, based upon all of the reading I've done plus all of the times I've dealt with them over the years. People may disagree and that's fine as they may have read different things, had different experiences, be an annoying contrarian etc.
1. Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.
This is from a "best practice" that states that if an attribute is a member of a defined hierarchy then it shouldn't be available as a selection on its own.
There's a kind of logic to this; you don't want an overcrowded dimension and most client tools will allow a user to hide parts of a hierarchy. In the real world though
you might have, for example, a date hierarchy that goes Year > Quarter > Month > Day. Then you might have a user that only ever cares about looking at
measures by month. Asking them to hide the rest of the hierarchy levels in the client tool may cause you to lose them from day one (getting users onside is soooo important
in a DW/OLAP implementation, not to say that you should acquiesce to all of their desires, but that's a discussion for another day).
So yeah, tidy up where possible but not at the sake of usability.
2. Define attribute relationships as 'Rigid' where appropriate.
This depends on what sort of data is in the dimension. Relationships that are "Rigid" do not change - an example of this is a Calendar Date hierarchy as mentioned above
(Year > Quarter > Month > Day). 1st April 2015 will always be in the Month of April in the 2nd Quarter in the Year 2015 (in a Calendar, not Fiscal, hierarchy) so it can be
defined as "Rigid". Rigid relationships can lead to performance improvement. Examine the dimensions that are giving you this warning. Are the relationships Rigid? If not leave
them as Flexible as defining a relationship as Rigid when it is not will result in a processing error when doing a Process Update.
3. Create hierarchies in non-parent child dimensions.
An easy one; Do your dimension attributes form a logical (one-to-many) hierarchy? If so then put them in a user defined hierarchy for improved usability. If not, ignore.
4. Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError.
This is an indicator that a conscious choice has been made by the developer to turn off the defualt error configuration (which doesn't ignore key duplicates). It can mean
that they just haven't bothered to properly set up the attribute key columns to be unique or it could be a workaround for an underlying issue with the data itself. Seek
to resolve this by setting up appropriate attribute key columns in the first instance. If you have user hierarchies and the key columns are not set up correctly then the
hierarchy may not display properly. If this is not possible then fix how the data is consumed by the cube so there are no duplicates.
More on key columns here: http://www.bidn.com/blogs/DevinKnight/ssis/1887/ssas-understanding-keycolumns-dimension-property
5. Use numeric key columns for attributes with 500000 or more members.
This is a question of design. What specifically has more than 500000 members? Is it really useful to have this as an attribute to "slice" the data by? Really? Have you
recently sustained a head injury? Joking aside, this might just mean that the key column of the dimension is not numeric (it probably should be but this is not the place to
get into database design....) or it could be that the dimension size is a bit silly and rather useless for an OLAP solution.
6. Attribute relationships do not exist between one or more levels of this hierarchy. This may result in decreased query performance.
Another easy one. Create the attribute relationships or you're in for performance problems 🙂 More here: http://www.mssqltips.com/sqlservertip/3414/sql-server-analysis-services-attribute-relationships/
7. The ' Blah 47' and ' Blah 47' measure groups have the same dimensionality and granularity. Consider unifying them to improve performance.
What it says on the tin....if these dimensions can be sensibly unified then do it, otherwise ignore.
I hope this helps. Any follow up questions then give me a shout.
June 5, 2015 at 6:04 pm
This is one of the most detailed and thoughtful replies I've seen for a long time. I'm very grateful for the time you put into this and I'm sorry I didn't get back to you sooner. Awesome!
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply