.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSAS 2008 Dimension: Multiple FK Relationships to the same Dimension

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server


I have kind of a fundamental design question: In my DWH are 2 tables TICKET and EMPLOYEE. EMPLOYEE is referenced by TICKET 2 times: TICKET_OWNER_ID and TICKET_ASSIGNED_TO_ID.

I have created a Ticket and a Employee dimesion in my SSAS Project. The Ticket Dimension should no have two atrributes: Owner and Assigned To. I can create the attributes by dragging the EMPLOYEE.NAME column from the DSV window into the attributes list but there is now to specify which relation will be used.

And of course I get pretty intresting results when I process the dimension.

I could create a named query in the DSV selecting * from EMPLOYEE and create a second Employee dimension but is that the only way I can get this to work ?

Thanks, ITD

View Complete Post

More Related Resource Links

SSAS 2008 Dimension root values changes after incrementally loading the dimension with 'process_up

The Dimension root  members (below 'ALL') changes after incrementally loading the dimension with 'process_update'.  How do I prevent that from happening ? There was no changes to data of the underlying table of the dimension.   

SSAS 2008 - The OLAP data source has no property fields available for this dimension


Last week I was able to view member properties for my Product Dimension in Excel 2010 pivot tables. Suddenly, without having made any changes to the dimension (for example, in the Attribute Relationships window of the dimension), those member properties are no longer available in Excel. I get the error "The OLAP data source has no property fields available for this dimension" when I try to select "Show Properties in Report" or "Show Properties in Tooltips" in the Pivot Table. At the same time, member properties are available for all other dimensions. I have also not made any changes to the data source connection in Excel and in any event, there is no option in the data source connection to enable or disable member property visibility. I am using BIDS 2008 and SQL Server 2008 R2 (10.50.1600.1). Does anybody have an idea why this might be happening?


SSAS 2008 - Dimension colmun/attribute not visible in cube browser?


Hi, I have one dimension and one fact table in SQL 2008 server...

i.e. DimEmployee (Columns : EmployeeID, FirstName, LastName, DateOfBirth, Addrss, PostCode, MobileNumber, Gender)
     FactEmployeePay ( Columns : EmployeeID, Amount)

When I create SSAS 2008 cube on those two tables (Relationship is EmployeID colmun) , deploy/process project/cube and then I go to "Cube Browser", but in dimension Employee table I can not see any columns other than EmployeeID. Why in this simple cube I can not view other dimension columns like FirstName, LastName,....

Any idea for SSAS 2008?


how to sort date dimension in SSAS 2008?


Hi I have SSAS 2008 DimDate dimension.

At source DimDate table has:
DateID : 20100518 (primary key), 20100519, 20100520,... (and SortKey too at T-SQL level)
MonthName : April, May, June,....
MonthNameYear : April,2010  May,2010  June,2010

I would like to sort dimension by MonthNameYear's attributes (currently I have order like January 2001, January 2002, January 2003.... but I want order like January 2001, Feburary,2001,March,2001 .......)
In SSAS visual studio, in Date dimension structure >> Attributes panr>> Month Name Year (properties : OrderBy, I set to Key, Name, AttributeKey, AttributeName and deployed but no luck in sorting).

Any idea how to sort date dimesion attributes? Thanks.

Process Update does not reflect change in case of dimension member in SSAS 2008.


Process Update does not reflect change in case of dimension member in SSAS 2008. We can replicated this issue in Adventure Works 2008 with below steps.


1.  Customer Name Aaron Allen is in Title case both in Adventure Works SQL 2008 Data Mart as well as in SSAS 2008.


2.  Change Customer Name Aaron Allen to Upper case in Adventure Works SQL Data Mart.


SSAS C# Amomd Dimension Data

Hi, i have the following problem. I'm able to connect to the cube, get the Meassure Names, Dimension Names and Dimension Attribute Names. But how can i get the Dimension Attribute Data (MEMBERS) in a List or Array. I mean how can i acces the data of an Dimension Attribute like in MDX: Dimension -> Attribute -> Attribute Data [Dim Customer].[First Name].&[Frank] I would be great to get/recive all Data an put them in a Table(List/Array/DataTable/...). Thx for your time and your help

SSAS - How to get other members from dimension that has Parent Child hierarchy?

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

SSAS 2K5 - Server time dimension

Hi, I'm building a cube and I want to add a time dimension. I don't have any time table in my OLTP (Source) database. So I tried to use the wizards from SSAS to create a time (date) dimension. I have choosed the "Server time dimension" one. It did create all what I need with all the hierarchies. But when I tried to add time business intelligence with the wizard, it doesnt see my new time dimension. I have checked if the type was "time" for the dimension and It was ok. So I don't know why the Time intelligence can't see my time table .... btw, I start the Business Intelligence wizard from the cube.   Thanks

SSAS 2K5 - Name of the dimension included in attributes

Hi I have a fact table with 2 field using the same dimension table. So, in SSAS 2005, The dimension is added (Lets say dimXYZ). So the dimYXY is used for 2 field in my fact table. When I go in my "dimension usage" tab ... i'm adding the 2 dimension that are bind to 1 physical dimension wich is dimXYZ. So I rename (in the dimension usage tab) the dimension freshly added. ("Dimension AB" and "Dimension CD"). But when I browse the cube, the attributes are displayed like Dimension AB.NameOfAttributes and Dimension CD.NameOfAttributes) Is there a way to hide the name of the dimension in the attributes ? Thanks * Sorry for the bad english ... I speak french ;-)--------------------- Richard Martin

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Need multiple distinct counts, have 1 fact and 1 dimension

I am using SSAS 2005. I have 1 fact table and 1 dimension. I would like to create multiple distinct counts in 1 Measure group, at least I would like them to appear as in 1 measure group to end-user. I have tried role-playing dimensions, and a roll your own approach that work in limitation but didn't scale. Any help and advice would be great.

SSAS - performance penalty standard dimension without custom member formulas VS standard dimension w


Hi all

I was wondering if there is a big performance penalty if one introduces custom member formulas to a standard dimension.


Can multiple users connect simulateously to an offline cube created by SSAS 2008?


We have created an offline cube on a network drive from SSAS 2008 and users are able to successfully access this serially using the Excel 2003 cube add-in or Excel 2007, in concert with version 10 driver, but when a user is already connected, the second attempt to connect will get the message :

Connection to <filename> server, <dbname> database, <cubename> cube failed.  OLAP Server error: The following system error occurred:  The process cannot access the file because it is being used by another process. ., File system error: An error occurred while opening the '<filename>' local cube file.

This is regardless of whether users connect specifying read-only and we have found it impossible to connect to a cube if the file has the read-only attribute set.

Is there any way to solve this without users taking copies onto their workstations?



SSAS - Use simultaneously unary operator and custom member formula in a PC (Parent-Child) dimension


Hi all

I was wondering (even this might a very bad idea from a logical and performance perspective) if it is possible to use unary operator and custom member formula simultaneously? Until now I have used unary operator but now some new calculation can't be expressed using unary operator. The idea is just to add the new members calculation to custom member formulas. Is it possible to make cohabitate both. If I had to switch all unary operators to customer member formulas what would be the equivalent expression for +, - , ~?

thanks for your answer


Multiple companies and fiscal calendars in date dimension table

We are creating an application where different companies can have their own fiscal calendar starting on different dates. For example one company’s fiscal year may start in April and some others in Sep. Also fiscal calendars may start at any date. (E.g. 29 Sept).

These fiscal calendars would be created for parent companies. We have added the parent company ids to the date dimension table. Please suggest the correct way of setting the attribute relationship in the above scenario. If this is not the right method to do this, please suggest the correct design approach for achieving this.


Thanks in advance,


Hamlin Stephen

SSAS adding a time dimension and using it



(Using SQL Server 2008 R2)

Im rather new to SQL Server and certainly to SSAS. AT the moment I am working on a project in which I need to implement SSAS on a SQL Server. The database originates from UniVerse and it does not have a time table I can use as a dimension.


I have found a script that will be able to set up the time dimension table, but:

-how do I add it to the database


-how do I make it available to be chosen as a dimension?





Multiple Parent Child Hierarchies in SSAS 2008 R2


I'm trying to create a dimension containing financial accounts in SSAS 2008 R2 where individual accounts can belong to one or more hierarchies.  I also need to be able to specify a unary operator since a leaf level account may be subtracted from one parent but additive to another parent.  For example revenues and expenses may be grouped one way for statutory reporting and a completly different way for management reporting and further belong to some arbitraty groupings that users find necessary.

I can't seem to find a way to have a leaf level member in a dimension more than one time in a Parent child hierarchy, and I can't seem to find any way to include the unary operator in a hierarchy where I use attributes to define the hierarchy.

Are there any work arounds that anyone knows about?  What I found from the help file was that multiple parent child hierarchies which supported in SSAS 2000 are not supported in 2008.  However there is no suggestion for what to do.

Any insight would be greatly appreciated.


Bill Webster

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend