.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 Cube & Pivot table

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
Hello, I am connecting to my SSAS cube through excel Pivot table. I have the following questions: 1) I have a filter for Company. However, instead of selecting multiple companies from a drop down list, I would like to have a range e.g. Company between 100 and 200. We have this "Filter expression and operator" option in SSAS but I dont see in excel. 2) I have 90 users who will be using my cube via pivot tables. How do I automatically have the data in excel refresh automatically such that when the user comes in, in the morning and open the pivot table, they have the refreshed data. Instead of them manually refresh it as it runs the query for a long time. Thanks in advance for all your help and suggestions! KJ

View Complete Post

More Related Resource Links

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

SSAS 2008 R2 Cube being queried by Excel 2010 Pivot Table - 60x slower than with Excel 2003!


SQLServer 2008 R2

Excel 2010 x64

Hi All,

I have a fairly complex Excel report where a raw data sheet is updated from 10 or so PivotTables pulling from an Analysis Services Cube, (2008 R2), with a VBA script that refreshes all pivottables, and then does some post-processing, formatting etc.

This was originally developed in Excel 2003, and the updating of all the pivot tables ran in under 10 seconds

I've now recreated in Excel 2010 and *each table* is taking up to a minute to refresh!

I've also observed that I can get the refresh time of a single table back down to 1sec, if I delete all other pivottables from the worksheet/book.

Its deeply frustrating, as I'm touting 2010 as being the way for our business to go...it won't help the cause if I deliver something with a x60 increase in runtime!

I've run traces of both the 2003 and 2010 versions executing against the SQL Server, the only thing I can see is that the 2003 version passes 1 single MDX query, whilst the 2010 version appears to be firing it in sections.

I've posted this in the Excel forum too.

Please can anyone help?

Many thanks,





With Excel pivot table client using SSAS cube, how to change the measures to rows?

I want the measures as rows instead of columns in Excel pivot table on a SSAS 2008 cube. How do I do that? Thanks.

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

Excel pivot table report filter selections from a cube

There are two filters added to the report filter, Date (8/31/2010) and Product (Product A), from a cube I have.  Everything works just fine.  Once the cube is refreshed each day to include the new data, for some reason, only the selection of Date, 8/31/2010, stays but the selection of Product changes to All Products.  Both dimensions are fully processed but the surrogate keys for existing records do not change.  I checked the MDX captured in Profiler and in the where clause, the date selection is passed from the pivot table but the product selection is lost and all products is passed in.  Any thoughts? TIA. 

How to generate pivot tables from SSAS cube programmatically?


I am able to produce about 20 pivot tables from SSAS cube one by one. Is it possible to produce these tables programmatically with header and footer information?


Please note that the end users like to use excel.





OLAP Cube - Pivot Table



I have a cube that I am accessing through excel 2007 pivot tables. I have a field that shows values with reverse signs. The negatives show as positive and the positive show as negative. Is there a way in Pivot table to reverse the sign? I can create a formula to do this, but I want this calculated column to appear in the middle of pivot table columns as the client would like a certain order of columns. Any ideas?

Thanks in advance!


Users use Excel-Pivot table to query the cube, how to capture those queries?


Most of our users use Excel'10 for querying the cube,

I've set up the querylog-properties in SSAS engine but it only captures the query from SSAS engine and not from the other platform such as Excel. So, when I use Usage based aggregation wizard, it shows the users/ueries from SSAS engine only. Is there any way to capture the user/query information that comes through Excel pivot table and somehow create aggregation through that information?


Any suggestion/help is very much appreciated,


excel pivot table linked to cube, unable to expand/collaps single field


I’m re-posting question. I’m using excel pivot table to browse a cube, for example the adventuresworks example. I drag dimension ‘Product Categories’ and ‘Sales Territory’ to Row Labels area, a date dimension to column, and any measure to Values area. Now I only want to expand ‘Europe’ under ‘Bikes’, but the excel  will expand ‘Europe’ under every ‘Product Categories’, is there any way to avoid that? In visual studio it works fine.

OLE DB Error: Network-Related or Instance-Specific while Deploying SSAS 2008 Cube

Hi, I encountered the below listed error while attempting to deploy SSAS 2008 DB: Error 1 OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [5]. ; 08001.  0 0  I am running a standalone MSSQL 2008 named instance on Windows Server 2008 R2. Here are the steps I have taken but yet the issue is not resolve: 1) Enable Remote connections on the server 2) Enable TCP\IP 3) SQL Browser is Running 4) Set Firewall Inbound Exceptions for SQL Browser, Default Port 1433, UDP Port 1434 5) Using Management Studio I can successfully connect to the MSSQL named instance from the app server 6) I can ping I MSSQL box from the app server 7) Read and implemented all steps in the MSDN post below: http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx Thanks for your help.

PIvot Table in SSRS 2008

Hi All, Is there any data Region (Pivot) in SSRS 2008 or higher version? Our requirement is to show the report like excel pivot gererated from a OLAP Cube where we can select or unselect any dimesion attribute or measures. Thanks, IndiaSQl  

AdomdDataAdapter.Fill returns memory error when querying an SSAS Cube(2008)

For certain SQL's I will get the following error when calling the Fill method on an AdomdDataAdapter object. "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."   Snippet of Code: DataSet ds1 = new DataSet(); AdomdCommand acmd1 = cn1.CreateCommand(); acmd1.CommandText = "SELECT ..."; AdomdDataAdapter ad1 = new AdomdDataAdapter(acmd1); ad1.Fill(ds1);   Now when we run this query in Ms Sql Studio we don't get any errors but the query returns 1 row with over 40,000 columns. I'm guessing the # of columns might be an issue but not sure.   When doing a Google search it appears that this problem was found in the 2005 and fixed in that version. Have any of you come across this problem?  

Plot Running total charts from SSAS Cube

Environment: I am using SQL Server 2008, I have installed SSIS, SSAS and SSRS. Objective: Create a Effort V/S Defect plot in SSRS 2008 Approach: 1. Created the SSAS cubes with Effort and Defects data by timeline (rollup of data based on Week ending date) 2. Created SSRS project and linked the dataset to SSAS cube. But i am unable to plot a Effort (X-Axis) v/s Defects (Y-Axis). As soon as i drag the measures into the query designer in dataset properties, the effort rollup will break. Following is the data that has been created in SSAS. I need to plot a graph of Hours v/s defects (Running total). Please guide me on how to plot the graph using SSRS 2008 from the already created SSAS cubes Week Of Hours Defects 10/24/2009 8   11/7/2009 63   11/14/2009 68   11/21/2009 80.5   11/28/2009 139   12/5/2009 221.25   12/12/2009 131.75   12/19/2009 124.5   12/26/2009 61.5   1/2/2010 73   1/9/2010 153.5   1/16/2010 149.5   1/23/2010 196   1/30/2010 163   2/6/2010 155.5   2/13/2010 178   2/20/2010 138   2/27/2010 161.5   3/6/2010 189   3/13/2010 191.9   3/20/2010 240.5 9 3/27/2010 260.2 5 4/3/2010 214.5 13 4/10/2010 274.6 24 4/17/2010 200.5 15 4/24/2010 227 9 5/1/2010 237.05 18 5/8/2010 190.5 12 5/15/2010 156.25 6 5/22/2010

Cube Writeback SQL Server 2008 R2 (SSAS, write back)

CUBE WRITE BACK in SQL SERVER 2008 R2 Did anybody get the SSAS write back functionality to work against a decent sized datawarehouse? I'm not asking about a little demo but a significant sized footprint e.g. 14M Rows measures, 6-8 wired Dimensions with 8-10 attributes each. I setup a test server using a Dell XEON ( 2x4core) with 48 GB RAM hardware and the latest Sql Server 2008 R2 release. I used the What-If scenario in Excel 2010 and modified a higher level, hoping SSAS would push the values down. After about 15 minutes and after having used 48 GB memory (on average 5% CPU) Excel throw an error - short on memory - and stopped the action. I found the same behavior in Sql server 2005 and 2008 and was hoping this would work now but apparently not so. Here are my questions: Is anybody using this at all? how can one calculated (roughtly) how much memory is requiered? is there any paper that describes best practise? Thanks for any help in advance, Dirk  

Errors attempting to process SSAS Cube. I get Either user does not have permission to create a new o

Hi All, One of my production server deployed cubes i am not able to process the cube.When i tried to process the cubes i will get an Error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Either the 'NA\gsad478' user does not have permission to create a new object in 'JNJAUSYSVR01', or the object does not exist.  (Microsoft.AnalysisServices)   Over the source server i do have sysadmin access and also  i have added read only role of particular cube. I right click on the database and tried to create new database of SSAS that also thrown with same error. Please help me in this regard.   Thanks, Gangadhar

Excel 2003 pivot table - parent showing grand total than the sum of its children

All, In Excel 2003, I have a product hierarchy, for simplistic sake lets say it goes A > B > C > model nbr.  I have filtered on model nbr 1.  I have the product hierarchy in my pivot table and unit cnt as the measure. Starting at level A in the hierarchy I am showing a total unit of 12000, if I drill down to B it still shows 12000, if I drill down to C it shows 100 and if I drill down to model nbr it shows me model nbr 1 with a unit cnt of 100. So my question is why are my parents not filtering the model nbr, it is taking the total for ALL records that have level A as their parent regardless of the model nbr? This does NOT happen in Excel 2007 or when I browse my cube in SSAS.

Cube Refreshment with Data updated in Table

HI All, I have cube which is MOLAP,and i have created SSIS package to process the cube,now i want to start the job as soon as data got uploaded into the Relational database. Please guide me in this as i am new to ssas. Regards Shraddha
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