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

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

SSAS Dimension Question

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :


Is it possible to display the all of the elements of two separate dimensions on a report?

For example, lets say I have two dimensions, one called Produce(containing Vegetables and Fruit) and another called TypesOfFruit(containing Apples and Bananas).  Is it possible to get output displayed like this

Vegetables  Fruit 

                 Apples Bananas

instead of


Apples Bananas

The problem is that one of my dimensions does not contain all of the elements of the other dimension.  In the case above, Vegetables are excluded from the report because there are no vegetables in the TypesOfFruit dimension. 

Any help is greatly appreciated!


View Complete Post

More Related Resource Links

SSAS newbie question

Hi Guys, I come from a SAP BW background, and i was wondering if i need to learn SSIS and SSRS together with SSAS in order to get a job? A insight into the current job market would also be helpfful. Thanks!

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 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

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.


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


SSAS 2008 Dimension: Multiple FK Relationships to the same Dimension



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

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 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?





SSAS Beginner Question

As the title states, I am new to SSAS. My question is related to my cube design which I think is incorrect. I have 3 tables, Invoices, invoice details and journal postings. Invoices holds the invoice number, invoice details is related to invoices by invoice number and holds the amount billed. Journal is also related to invoices by invoice number and holds the post date. when I browse my cube I select invoices.invoice number as rows and amt billed as columns. unfortunately, it is showing my the full sum for every row regardless of invoice number. Any ideas?

Get SSAS Dimension Attribute SQL Column Name using C#



I am trying to determine what the real SQL Server Column name is for a Dimension Attribute using ADOMD.net, however all I am able to find is the the TABLE_NAME and COLUMN_NAME Columns using the GetSchemaDataSet("DBSCHEMA_COLUMNS", null) function.

Does anyone have any suggestions?



Role playing dimension and member naming question.


I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

Dimension Design Question


Hello All,

a quick question on dimension design...
We have a project detail page a asp.net based web page where admins enter the project details
There is a section in this page for Project Team where we can assign users to different role (like Project manager, Partner etc..)
There can me more than one user for the same role.

Right now we have total of 10 roles defined in the system.

Now the design question is while doing the dimension design do we need to create ten different dimensions? like DimPM, DimPart etc...
or there is any other way to do this by just creating one single dimension.

Basically from the cube we should be able to see who are the PM's, PART's etc for a project

I have two approaches right now in my mind...
First Approach
a) Need ten dimensions for ten roles ex DimPM(PMKey, PMUserID, PMUserName). DimPart etc...
b) Create a Factless table with columns (FctLesskey, ProjectKey, PMKey, PRATKEY etc...)
c) Fact table - ProjectKey, FctLessKey, Amount etc...

Second approach -

a) Create a Role dimension (fill all the roles) and will have columns RoleKey, RoleName
b) Assuming there is a User dimension (Userkey, UserName etc...)
c) FactLess table - FactLessKey, UserKey, RoleKey
d) Fact Table - ProjectKey, FactLessKey, Amount etc...

Please let me which

After adding a new attribute to a dimension and saving, SSAS unprocessed partitions. Can anyone help



After adding a new attribute to a dimension and saving, SSAS unprocessed partitions. Can anyone help me understand why this happened and point to some reading materials for detail? (can't find any...)


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