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

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

Fact Dimension not working in Excel

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server



I have ItemSales fact table. I have fact dimension Item Sale Transaction Information. Key is EntryNo, it also has attributes DocumentDate, DocumentNo, Description.


The problem is, that while this dimension works fine in browser, it's not possible to add it to excel. Whenever I try to drag and drop it to rows or columns, it tries to execute query, but then it disappears again. If I try to check the checkbox next to it, it shortly appears checked, but then disappears again. Oh, and this only happens for EntryNo, that is Key attribute. Adding any other attribute works as expected.

Strange thing is, I have another measure group with similar dimension, and it works fine there.

I've even tried recreating the dimension..

View Complete Post

More Related Resource Links

In sharepoint excel working fine in office 2007 but not in office 2010


Hi All,

we receive error  while trying to create excel sheet using the template we have uploaded on the SharePoint 2007.


This occurs on a machine that has Office 2010 installed. Earlier the machine had Office 2007 and everything was working fine.


Can you let me know why is problem is coming and how to resolve this.



Sanjeev Tiwari


Relationship between a dimension - two fact tables

I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

Working with Excel

Hi all,    I'm working in VS2005 and my task is following,   1. Need to open Excel file   2. Then call a macro function of the Excel   3. Close the Excel filefor this i'm using Microsoft.Office.Interop.Excel.ApplicationInstead of Interop what else i can use to do this Excel operation?Thanks!

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

Target audiences not working in excel services web part

My appologies i posted this originally in the pre-2010 forum So i'm reposting here: We have recently upgraded to Sharepoint 2010 and we're experiencing problems with the "target audience" field being available to the users. I am not the CA admin though so it'll make this a bit tough to get solved if the problem is there. What i'm looking for are the suggestions of things for me to forward to the CA admin to change or check if anything. We have a seperate Virtual Machine that is running 2010 that has this option available to users on that farm for some reason. however on our corporate farm we don't have that option available. Plus on top of this the quick launch items are not working with audiences as well to turn off the headers to links that have nothing in them due to security trimming on document libraries. I'm hoping that this being a more of an overview of things to check would help others who may have this problem as well. I've seen resolutions such as rebooting the server and such, however that's a bit lower level and we need some higher level things to check that may resolve the issue. Think the simple stuff first to check and lets work down the list. Doesn't matter how stupid it is but can we get a more compiled list of things to check to make sure that target audience visibility to users is enabled? Thanks!

Non parent-child dimension with fact data at different levels... How!

Hi all, Quite new to SSAS, wondering if anyone could help with the following... I've got a dimension with attributes that indicate geographical location based upon UK postal boundaries - so ~1.8m Postcodes > ~10000 Postcode Sectors (PCDS) > ~3000 Postcode Districts (PCD) > ~100 Postcode Areas (PCA). The problem I have is that the address information is not of the highest quality. I have matched 50% of my facts to postcodes, of the remaining 50% i've mapped them to PCDS, PCD or PCA, where possible, leaving the lower-levels as NULL in those cases. My attribute hierarchy works fine where I have complete records all the way down the hierarchy, but I only have a single UNKNOWN member at the top (GOR) level. I want an UNKNOWN member at each level. It makes sense to me how to do this using a Parent-Child dimension, but i'm keen to avoid that as the performance is terrible. I'm hoping there's a method of configuring this. Any help massively appreciated.

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.

Cannot see the Dimension description while using Local Cube with Excel

Hi everyone, I'm new to the world of Analysis Services, so I might make a stupid question.
I searched over the web, but I haven't found an answer.

I'm using:
- Microsoft SQL Server Analysis Server 9.00.4035.00
- Microsoft Office Excel 2003 Sp3 (i have the Excel Add-in for SQL Analysis Services version 1.5.0166.0)

I created a cube in Microsoft Visual Studio 2005 with a fact table and only one dimension.
In the 'KeyColumns' DimensionAttribute properties i have the Dimension key and in 'NameColumn' the description.

When I browse the cube with Excel (olap driver 9.0) I can see the description of the Dimension without problem.

The problem starts when I create the local cube with the excel procedure (create offline cube) and I connect to It using Excel; in this case, the description ('NameColumn') disappears and i can see only the Dimension code ('KeyColumns').
When I connect again to the server Cube, leaving the local cube connection, i can see again the description.
It seems that the Local cube does not contain the description of the Dimension, but only the key.

I did the same thing using Microsoft SQL Server Analysis Server 2000, and when I connect to the local cube created with Excel I have no problem viewing the description of the Dimension.

Where am I doing wrong? Am i fo

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


Named Set working in excel but not in Browser....



I have a problem in the named sets.

I have a dimension with 6 levels and I have to calculate the standard deviation for each level, only for the siblings. I used Siblings to calculate the stdevp but it was not working with the filter, So i had to use named sets.

The problem is, this calculation is working fine in the Excel, even with the filter, but in the SSAS browser, filter is not working. Could anyone please suggest me the solution for this, or to calculate stdevp for siblings with some other way, that should work for the filters also(Filter here should be on the same dimension for which the stdevp is being calculated)?.

here are my calculations:

Named Sets:


[LEVEL2] : [DIM_A].[A].[LEVEL2].

Save Dimension member only when this have lines in Fact Table


I have one dimension table with over 600 item, but in my fact table only 40 of this member have lines (my fact have a filter for the 2 laste year)


How save on the dimension only this 40 members?


Best regards




Excel Export Report with Parent Child Dimension OLAP





I have a problem with a reporting services 2005 report.

The data source is OLEDB for OLAP 9.

The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:

The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.

Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the

Loading Dimension & Fact tables


Hello Experts,

I am new to SSAS & I need to load Dim & Fact tables for a data warehouse. I've the basic idea to load them, but i dont have the exact picture to load a fact table. For eg. if we have 3 dimension tables like Dim_Time / Dim_Geography / Dim_Product and one Fact Table Fact_Product as described in most of the online examples. Then how to load them. I know for fact Tables we need to do aggregations. But how to apply those aggregations on what criteria.  In the said example if we need to load Fact_Product then we should be able to see sales by product, sales for a given point in time & sales for a given geographic location. Then how to do that. Do we need to apply aggregate for all the dimension tables

I know i am not much clear, but i hope you guys can understand. If not please let me know i will try to explain it more. Please clear my doubts.

Thanks & Regards,


Excel 2010 - Calculated Members on Dimension



Was wondering if there was any workaround in Excel 2010 to make these appear. My understanding is that the behaviour is unchanged from 2007.

Does anyone know of any plans to support calculated members on dimensions in Excel?


Dimension key attribute changes in fact table



How do I need to handle a case in which the fields in a fact table that represent the foreign keys to the dimension tables might change? What kind of process do I have to do to the cube?



Working with excel in asp.net


I am uploading an excel file ( containing int values )using the fileupload control. I need to work on one entire column in the excel sheet.

I want to copy contents of the file either in an array or a table and then work on it.

Can anybody suggest a better way of working on the excel since .


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