Attributes, Properties; Dimensions, Levels, and Hierarchies, oh my!

I have a most basic question: how to correctly access properties (attributes) of dimension members that are at a different level. Here's a simple query from AdventureWorks to illustrate:

Code Snippet

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

[Measures].[Internet Sales Amount]

on 0

from [Adventure Works]

This query works, but the city property is left out of the results (postal code is provided). How can I get City, State-Province, Country, etc, as properties in a query like this? (As the relationships are all correctly defined, and the levels for these attributes/properties are less restrictive than the query level, I'm totally missing why this doesn't work in either the form given -- but do I have the name perhaps wrong?)

Seperately, I have read (somewhere) that property access is to be discouraged in SSAS05, instead prefering direct attribute access via UDM; is there a better way to write these sorts of queries, say including City, etc in the output grid area?

Thanks as always!

To somewhat answer my own query, I have found that:

Code Snippet

with member [measures].[cc] as [customer].[city].MemberValue + ', ' + [customer].[state-province].MemberValue

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

{[measures].[cc], [Measures].[Internet Sales Amount]}

on 0

from [Adventure Works]

properly returns the customer "city, state" in the output grid area. But I am still interested in how to obtain the city and state-province instead as properties of the customer members on axis(1).....

Thanks again!

Attributes not visible in Cube when adding with AMO and Hierarchyenabled = false

I need help with a big problem.

I'm using AMO to add new Attributes to a Dimension and I have to use AttributeHierarchyEnabled property = false as Default.

But then I'm never able again to see the Attribute in the Cube though I change with Visiual Studio the property to true.

If use AttributeHierarchyEnabled = true all works fine.

Dimension dim = mDataBase.Dimensions.GetByName(Attr.DimensionName);

DimensionAttribute Attribute = dim.Attributes.FindByName(Attr.AttributeName);

Attribute = new DimensionAttribute();

Attribute.Name = Attr.AttributeName;

Attribute.ID = Attr.AttributeName;

...

Attribute.AttributeHierarchyEnabled = true;

dim.Attributes.Add(Attribute);

Thanks for any help.

You need set the AttributeHierarchyEnabled to true for both DimensionAttribute and CubeAttribute in AMO.

In visual studio, you need change the property both in dimension attribute property dialog and cube attribute dialog.

Attributes in referenced dimension

I am wondering about the correct/best/most efficient way to access attributes contained in a referenced dimension. ie in AdventureWorks: say I had population data for states (or some other level other than the base key/postal code level), and I added this as a column in the Geography dimension (Population attribute/property related to state attribute); I would then like to use this new data in a query against Reseller Sales by the Reseller dimension. Is this the "right"/optimal approach:

with member [Measures].[popu] as linkmember([Reseller].[Geography Key].currentmember, [Geography].[Geography]).parent.parent.properties('Population', TYPED)

select [Reseller].[Reseller].members on 1,

[Measures].[popu] on 0

from [Adventure Works]

While this works, I do wonder about the efficiency, and Mosha's comments regarding the general avoidance of both linkmember and properties has me suspicious that "there must be a better way"....

Thanks!

-f

Efficiency can be a question of how your users would like to see data in dimensions or how fast a query will be.

If you create a view on the Resellers and the Geography dimension you will have autoexist between attributes in these dimensions. That can be an efficient and a better solution than using referenced dimensions. This is from the point of view of how SSAS2005 works.

HTH

Thomas Ivarsson

|||

Thanks, Thomas -- in my particular situation, I'm pretty stuck with the referenced dimension (my real situation is a lot more complex). I do pull many attributes into the logical equivallent of the Reseller dimension in my situation, but can't pull all -- so I was really focused on the question of if this is indeed the right way to cross the referenced boundary and effect the "indirect overwrite" (to coin a phrase) to grab the attribute of interest (on a different level/hierarchy in the referenced dimension just to add to the complexity). I'm a little surprised if there isn't an easier/more efficient way to do this....

On an indirectly related note, this difficulty with referenced dimensions does really bug me, as while they do provide some measure of reuse, it seems that we still need to do a lot of data duplication between dimensions to really make use of the data values for calculations and even browsing, as in my example. While this is not a huge impact in terms of storage (dimensions are small and all that), it seems wrong from a development POV as we have data access/migration spread between ETL and/or AS DSV and/or AS calcs to overcome an implementation issue. I understand that there is a performance impact to keeping things clean, but as attributes in related dimensions are logically really just attributes in other hierarchies in the relating dimension, it would seem to me important to unify the handling of related dimensions with hierarchies within a dimension. (Logically, I would have expected this sort of functionality to be implemented by having related dimensions implemented directly attached to a dimension, in the dimension design area -- to perhaps be materialized as part of the processing of the relating dimension, rather than via the dimension usage area, but perhaps I am missing something fundamental about the implementation of AS.)

Thanks again,

-f

|||

Hello again Sir SliceDice. I am not sure about your business problem but most design approaches in SSAS2005 simply follows the same rules that you have with joins between tables in the relational world.

You have one-to-many, one-to-one and many-to-many.

Referenced dimensions can be subsituted with views, like I suggested before, or by having both a resellers key and and geography key in the fact table.

I suggest you to have a look here ( www.kimballgroup.com ) for guidelines of how to solve your business problem.

To solve business problems when the structure is fixed in a starschema, with MDX or dimension relations, is always harder than changing or rewrite the structure of the data warehouse.

I know that design changes in a data warehouse will effect the report side negatively, but I have no clear idea of why Microsoft have added referenced dimensions to SSAS2005? I have a hard time finding out why I should use them except when something is wrong with the design of my data mart /data warehouse.

Kind Regards

Thomas Ivarsson

|||

Thomas,

Thanks again for your reply. I agree that we can work around the limitations of the current implementation of the UDM via a view, or via recording the additional geography attributes in the fact table or the (Resellers in the case of AdventureWorks) dimension table.

BUT, let me ask: is geography a useful abstraction in its own right? Of course it is! It makes a lot of sense to seperate and isolate the geography information/attributes to their own table; it makes a lot of sense for lots of reasons to use a "snowflake" approach for geography and reseller. But now having the snowflake, we need to incorporate geography hierarchies/levels into the Reseller dimension OR use a referenced dimension. In the case of the Customer dimension, the AdventureWorks demo takes the former approach (Geo in the dimension); in the case of Reseller, it takes the latter approach (referenced dimension).

With the embedded snowflake approach (eg: Customer dimension), it is painful and wasteful to have to repeatedly design all the same hierarchies and attribute relationships, particularly if there is a lot of data related to the flaked dimension (Geography; e.g.: say you had geographical area data, population/census data, government data, etc). Do the users need to browse and/or calculate with all these items in all the related dimensions? Maybe, maybe not (but we can be sure the need will grow in the future) -- and while we can certainly work around the situation (views and the like), it still is a waste of time & effort to repeat the work.

And, in the realm of software development beyond the realm of data warehousing/business intelligence, this sort of repeating of effort and data is a non-starter. Sure, some exceptions from 3NF are reasonable and well justifiable, even if only to improve performance (especially with the strict gatekeeping administration of a strong ETL system), but wholesale duplication of dimension definitions/embedding sounds like a recipe for a lot of waste and problems with future change, as well as problems from unexpected behavior ("but I did it like that with the customer data, why doesn't it work right for the resellers -- they're in the same state!").... The problem of future change is especially significant: in my experience, it is particularly easy to break existing reports via changes to cube structure, making for very unhappy users (and the worst is when you break your own reports!): thus, solid upfront architecture is especially important.

Of course, to some extent, this is exactly the problem: these BI systems have originated in one world oriented to reporting and analysis, when my application, while still being described in those terms, is in reality quite a bit outside of the typical BI realm: I have pulled quite a bit of business logic into cube space, and have had great sucess with cubes as an effective general compute platform. After all, ALGORITHM+DATA STRUCTURE = PROGRAM, and in cubes we have a general purpose data structure (can model any data structure desired, and can do so very efficiently for exceptionally large structures), and we have a very descriptive algorithm implementation language (that allows effective blending of both declarative and procedural code efficiently over large data sets). The UDM was a huge step in this direction; with a few more improvements the AS05/UDM environment could be an ideal general compute platform....

But all that aside, back to my original question: is this the preferred approach to cross related dimension boundaries and grab properties?

linkmember([Reseller].[Geography Key].currentmember, [Geography].[Geography]).parent.parent.properties('Population', TYPED)

-f

|||

I don't know the complexities involved in why you chose a reference dimension, but I agree with Thomas that you can combine those two tables using a view and put all attributes into one dimension. Then you get auto-exists which should make it easier to efficiently get the population # you need.

It sounds like you're not going to go that direction, so optimizing the MDX is something to look into. That exact MDX you had above returned nulls for me because I don't believe there's a Population member property in the Geography dimension. (But it illustrated what you were doing, so it was fine.) I rewrote it as follows to test performance:

with member [Measures].[popu] as

Exists([Geography].[Geography].[Country].Members, linkmember([Reseller].[Geography Key].currentmember, [Geography].[Geography Key])).Item(0).Name

select Exists([Date].[Month Name].Members*[Reseller].[Reseller].[Reseller].members,,"Reseller Sales") on 1,

[Measures].[popu] on 0

from [Adventure Works]

In terms of performance, the following query outperforms it and returns just the same results I think. It's using Exists with a measure group to find the right combinations of Resellers and Geographies. You might consider trying this as I suspect it will perform better:

select Exists([Reseller].[Reseller].[Reseller].members*[Geography].[Geography].[Country].Members,,"Reseller Sales") on 1,

{} on 0

from [Adventure Works]

attributes and name column property

I'd like to verify my logic on using the name column.

In my dimension i have a two tables

The main table contains the majority of the fields that will create the attributes for the

dimension some of these fields are code values where the description is contained

in a second table.

So here is my question/logic

Instead of adding the Description field as an attribute from the second table,

I should take the code value from the main table as the attribute and then set the

"name Column" property to reference the description from the second table.

The way you describe is definitely better than defining 2 attributes. Having more attributes is less efficient. You will need to define more aggregations to get a better performance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Attribute relationships

I am having trouble understanding SQL Server 2005 Analysis Services dimension terminology. In AS 2000, you defined relationships with hierarchies...just drag and drop levels. In AS 2005, you drag and drop hierarhy relationships, but you can also define attribute relationships. What is the difference between defining a dimension like:

Hierarchy
Level 0
Level 1
Level 2
Level 3

Generated Attribute 23
Level 0
Level 1
Level 2
Level 3

VS.

Hierarchy
Level 0
Level 1
attribute relationship to Level 0
Level 2
attribute relationship to Level 1
Level 3
attribute relatiohship to Level 2

Generated Attribute 23
Level 3

Both designs seem to accomplish the same thing. But there must be some kind of differences. I've read the other thread on attribute definition, but I was hoping for a layman's explanation.

You touched one of the most fundamental questions in AS 2005.

You should look at what is called "Natural hierarchy" and you definitely should be using it in your dimension.

Not sure if what you have as a second example is Natural hierarchy. (Not clear what is the key of your dimension) But it looks close.

Some past posts on the same matter:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=360657&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=377519&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193477&SiteID=1

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for the reply and the link to those treads. I've seem some of those treads before, but they never made sense before. I've since re-read the Project Real Analysis Services Technical Drilldown article and I believe I understand now. In my example above, the second option is the prefered design. Level 0 is the top, Level 1 roll ups up to Level 0, Level 2 rolls up to Level 1, etc. It was confusing because you drag the table fields onto the Hierarchy and Level area just like AS 2000. When you do this, AS 2005 creates a Key attribute that contains all of the levels. It is my understanding now that if the hierarchy are Natural and not Reporting Hierarchies, it is best to define the attribute relationship in the Hierarchy pane and remove the attribute from the attribute key definition. Thanks again.

Eric

Attribute relation error

Hi! I have changed some attribut relations in a time dimension user hierarchy(SSAS2005 SP1), from flexible to rigid. When I try to deploy this change to the server the whole process stops with these strange error messages:

"Error 1 File system error:
Error opening file; \\?\D:\MSSQL\MSSQL.2\OLAP\Data\SalesStatistics.0.db\Sales Statistics 2.0.cub\Sales Master Cube.0.det\Sales Master Cube.0.prt\35.agg.rigid.data
is not a disk file or file is not accessible."

The next error repeats several times for all named sets I have built for the time dimension:

"Error 2 MdxScript(Sales Statistics Facilities) (174, 6)
The dimension '[RecentMonth]' was not found in the cube when the string, [RecentMonth], was parsed. 0 0 "

[RecentMonth] is actually a named set, that exist, with the following code:

Tail(Filter([Time].[Time_Calendar].[Month].Members,

([Time].[Time_Calendar].Currentmember,[Measures].[Sales SEK])>0))

My other named sets use this set as a reference like Last12Months and so on.

It is the first named set in a collection of named sets. All named sets are placed last in the scripting tab i BI-Dev Studio.

Any ideas?

Regards

Thomas Ivarsson

Problem is solved. I removed the named sets for the time dimension, processed the cube without them and processed again with the the same named sets.

SSAS 2005 probably use an internal identifier for the dimension that the named sets are mapped to. Changing attribut relation types probably means a new identifier/version of the dimension.

/Thomas Ivarsson

Attribute Hierachies

Hi there. I have a problem that I have been struggling to understand. Here is the situation. I will use a time dimension just as an example.

If I build a time dimension containing 2 Years of dates from 2005 to 2006 with 3 attributes: Year, Month, Date and set my attribute relationships as Date ->Month and Month -> Year. In management studio I can drag over Month and then Year (in that particular order ) and it will give me the correct results. For example if I expand the Month of January 2006 the year column will show 2006. Now If I filter the year column for the year of 2006, you would expect that the month column would only contain the months of 2006, but it doesn't. It still has months for 2005.

Now if I rebuild that dimension setting the attribute relationships to the key. I.e. Date ->Year, Month. And no attribute relation to month. I can rebuild the cube and drag over the same 2 colums, Month and Year (in that order) and then filter on the the year column for 2006 and then the Month column will show only the months for 2006.

Whats going on here?

I would check to make sure you have the total heirarchy structure as part of your key.

IE: Date should have a key of (Year --> Month --> Date).

|||

I'm not quite sure what you mean since date is the key.

My user hierarchy for the first scenario is:

Year

Month

Year (year is the attribute relationship)|||

I was able to recreate this scenario using Adventure Works. Seems to me this should work, but it behaves as you described. Here's what I did:

First, I created a new dimension called Date Test 1 that had three attributes -- Date, Month, Year based on the DimTime dimension table. For this dimension, I left Month and Year related to Date (the default). The Month attribute had members like "January 2002", "January 2003", etc. while the Year attribute had members like "CY 2002", "CY 2003", etc. I included this dimension in the Adventure Works cube, relating it to the Internet Sales measure group (joining from the Date attribute to the OrderDateKey column in the underlying fact table). After deploying and processing the updated project, I browsed the cube. From the Date Test 1 dimension, I put Month on rows and got each month listed. I then added Year to the filter area (not the subcube filter area, but the normal page filter area. I selected only "CY 2003" from the list of years and the list of months was indeed filtered to just those in 2003. Note that this was done without putting any measures in the data area -- just the dimension data. This works as I would expect it to.

Second, I created another new dimension called Date Test 2 that had the same three attributes with the same definitions. However, in this test, I moved the attribute relationship for Year from the Date attribute to the Month attribute. Thus, Date had a single attribute relationship to Month and Month had a single attribute relationship to Year. I again added this new dimension to the Adventure Works cube, relating it in the same manner as Date Test 2. Then I did the same browsing test. In this case, adding Month on rows to begin with gives the same list of all months across years. But, adding Year to the filter area and selecting "CY 2003" did NOT filter the list of months displayed on rows. Again, this was done without any measures in the data area (as was the first test above).

A few interesting observations:

1. I turned on SQL Server Profiler and captured the MDX queries being generated. As far as I can tell, they look exactly the same. They query in the browser window results in a few different queries that look something like this:

CREATE SESSION
SET [Adventure Works].[{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0] AS
'
{
{ [Date Test X].[Month].[All] },
AddCalculatedMembers([Date Test X].[Month].[Month].MEMBERS)
}
'

SELECT
NON EMPTY [{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0]
FROM [Adventure Works]
WHERE
(
[Date Test X].[Year].&[2003]
)

2. In the browser window, if I instead add the Year attribute to the subcube filter area (instead of the page filter area in the pivot area itself), things work as expected. For both dimension tests, the list of months displayed on the rows after adding the Month attribute is filtered when a specific year is selected for the Year attribute filter.

I suspect that this has something to do with how the query engine determines what attribute combinations actually exist (ie, the autoexists feature of the engine). It seems to be able to determine that the months of a given year are the only months that exist when both attributes are related to the key attribute for the dimension. But it doesn't seem to be able to determine this in a scenario where the relationship between the attributes is above the key attribute for the dimension.

I would consider this to be a bug myself. Anyone from Microsoft care to comment (Mosha?)

HTH,

Dave Fackler

|||

Thanks for taking the time to go through this. Another thing to note is that the same situation occurs when both attributes are related to the key attribute and then you try to filter the dates by the month.

I suspect that this is a bug as well. Any other comments?

Thanks.

Attribut key not found vs Characters

Hello,

When I process one of my dimension, I have an error message "attribut key not found".

It's not link with records present in fact table and not in dimension table, but with the value of the attributs which contain symbols.

To be accurate I integrate first data from a database DB2 AS400 to sql server 2005 (with provider ADO.NET ODBC).

In the database DB2 AS400 the data could have different languages (chinese, japanese,...) but so far it is not necessary to see data with the correct language (moreover the database is not setup in Unicode and 1 specific language = 1 CCSID value...).

So I could have a client name with symbols in the name like :"€"

I think this is what SSAS doesn't like when I process the dimension...

When I set the KeyNotFound property of the dimension to "Ignore Error", I can after process my dimension, but when I browse it, I have an error ("the server sent an unrecognizable response").

Do you have any idea to how to deal with this error?

I think about eliminating the symbols like "€" to begin, but I don't know if there are better solutions (and less dirty...).

Thanks,

Guillaume

There are two properties on the attribute's key columns you should check: Collation and InvalidXmlCharacters. The first will set the character set and case sensitivity for the column in question. When not set at the column level it inherits from the parent object, usually all the way up to the default settings specified when the OLAP server is installed. This should allow the server to understand when two different character codes represent equivalent or different characters. The second property, InvalidXmlCharacters, determines the behavior of the server when it encounters characters that are not valid in XML without some encoding. For performance reasons, the server will not check for the presence of such characters unless you set the InvalidXmlCharacters property. As a result if such invalid characters exist, the response the server sends to the client will not be valid XML.|||

Thanks for you reply, it works now!

I need to ignore the "attribut key not found" error to be able to process the dimension, but thanks to the option InvalidXmlCharacters set to "remove" I can browse my attributs.

Guillaume