.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

How to generate pivot tables from SSAS cube programmatically?

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

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.





View Complete Post

More Related Resource Links

SSAS Cube & Pivot table

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

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.

how ssas generate sql queries when processing dimensions and partitions?

hi all, how does ssas2008 generate sql queries to read data from the source, where processing dimensions and partitions? is there any reading meterial that clearly explains how the sql queries are constructed according to properties of dsv, dimensions and measures and partitions.   thanks in advance.Andrew Chen Interested in BI related technologies

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.

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?  

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


Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n

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

Browse SSAS Cube Data From Your iPhone

Does the world want an iPhone app that allows you to connect, browse and pivot SSAS cube data?  What features would that app have?  Is there existing apps that are close?  Looking for others thoughts on this concept.  Thanks in advance!

create a calculated member in a cube in SSAS

Hello   I’m trying to create a calculated member in a cube in SSAS and was hoping someone could help.   What I’m looking to do is count the number of occurrences of a value in a field.  I have Status field that contains New, Renewals and Enquiries and another field that contains a count, the problem I have and the reason for this query is the count is set to 0 for Enquiries.   The tSQL equivalent would be something like...   Select Date, Status , count(Status) From aTable Where Status = ‘ENQ’   And to summarise, what I’m looking to do would look something like the below   Date Status Count Commission Apr-10 NEW 1 100 May-10 NEW 1 100 May-10 REN 1 50 Apr-10 ENQ 0 0 May-10 ENQ 0 0 Jun-10 REN 1 50 May-10 NEW 1 100 Just looking at the ENQ by month. Date Count of ENQ     Apr-10 1     May-10 1     Jun-10 0       Any help gratefully received.

Excel 2007 connectivity to SSAS 2005 cube

Hello - I am using an excel 2007 odc file to connect to an analysis services 2005 cube (on windows server 2003 R2 OS). I receive the following error message   "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server" Have already attempted the following fixes but running out of ideas now 1. Added localeIdentifier to ODC file    <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=bisql;Initial Catalog=SLAM ACT BUD; LocaleIdentifier = 1033 </odc:ConnectionString> 2. Ensured that all dimensional attributes which are used in MDX calculations (named sets, calculated members) are set to "AttributeHierarchyEnabled  = True" Running out of steam so any help is greatly appreciated.

I have a deployed cube in SSAS,

Hi,   I have a deployed cube. How i can send it to my user so that he can do analysis.   Gaurav

Range or List Filtering via SSAS cube in Excel 2007

Hi All, A client has posed an interesting question, as current users of business objects webi they can filter the results of a cube/universe by copy and pasting a range or list of values seperated by a ';' into a list filter that is availble. e.g. product2;product533,;product029;product8389 etc etc. The list can sometimes be hundreds long. How is that same function acheived in browsing a ssas cube in excel 2007, all I can see is that a user has to use the drop down list and individualy select the values they want. This does not seem like a great method! Any ideas? They are using SQL / SSAS/ SSIS/ SSRS 2008 R2 Cheers DC
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