.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 connect SAS OLAP Cube using SSIS

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

Hello Everybody, I am using SAS Olap cubes, i would like to connect those OLAP Cubes using SSIS and need to perform some ETL process, could you guys please let me know the solution,thank you.

Kind Regards


View Complete Post

More Related Resource Links

# users connected to a olap cube?

How do I know how many end users are connected to a cube at certain time? Most of them use excel pivotal tables to connect to the cube.   Thanks, Alex

Need to Automate in SSIS Cube Drop and Recreate of Dimensions: Had to do on the Solution Explorer Ma

Hi SSAS Engineers: This question is in the context of a complete refresh of the SSAS cube. I'm OK on the SSIS approach thanks to TMcD and past successes (also thanks to him). The question is why did I have to manually drop/delete the dimensions in BIDS SSAS, then readd them with the cube wizard to pull in the dimensions with the same names, but new surrogate key relationships and more surrogate keys in total? We need to Automate in SSIS Cube Drop and Recreate of Dimensions. I originally had to perform this function on the Solution Explorer Manually in SSAS UDM, since the names are the same, but the structures and the data are changing for the dimensions on the refresh (and the fact table is subsequently increasing). Herein lies the problem. We had a keynotfound error in the cube processing, since the old keys were being kept. I had run Full processing on the dimensions, but since the dimensions have the same name (no drop old names and add new names), the old surrogate keys remained. The fact table had the old row count as well, since the surrogate key pipeline to look up the surrogate keys for the new dimensions only had the old dimensions, even though I truncated the fact table. Yes--I had already rebuilt the dimensions in SQL Server and the row counts were the new refresh row counts. The dimension counts were right according to refresh numbers after

SSRS 2008 Against OLAP Cube reports rendering slow in IE 6 and IE8

We have SSAS cube built on SQL Server 2008 that has multiple parameters that have a default vault on running of all. The first time we run the report it renders the report in less than 30 seconds, but when we change one value on the data cube pull the reports can take up to 5 mins to run. If I do the same report in Firefox 3.6 it renders the report in 30 seconds. I have change changing the cache size in the IE explorer and ave the latest .net frame work on the computer. What would be causing this issue? The company upgrade from SSRS, SSIS, SSAS, SQL 2005 to 2008. Please advice, this is very annoying for our business end users.   Thanks for your help in advance.

0x80040154 error processing a cube from SQL Server 2008 R2 x64 database engine to OLAP Server 2000 x


I recently upgrade from SQL Server 2000 x32 to SQLServer 2008 R2 x64 but only the database engine not the Analysis Server, I used to use this code to proces the cubes.


but now it do not  work more with the error:
Error at create server:
Error      Source                    Description          HelpFile HelpID
---------- ------------------------- -------------------- -------- -----------
0x80040154 ODSOLE Extended Procedure  Clase no registrada NULL     0

(1 row(s) affected)

I found this article:

It means that I can not process the cube using the same method any more ?

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?



ERROR"The Item could not be found in the OLAP Cube"


Our business unit uses an excel templates where they can get real time analysis. They run these reports on a daily basis.
Every time we do any changes in the structure of dimension, they get these error " the item could not be found be found", and they have to recreate these reports which is time consuming and frustrating to them.

Is there a way out to solve these problems? We constantly add and change things in the cube, but it results on them having re-create everything.

any suggestions? Is there anything I can do from excel standpoint?



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!


Transfered ssis of type maintenance plan connect to wrong server

I transfered a maintenance plan using the method to transfer ssis with sql server management studio.

The sql task contained in my maintenance plan are set to use the "local server connection".  Somehow, when I run the maintenance plan on the target server, the sql task get executed on the source server from which I imported the maintenance plan.

In other words:
I transfer a maintenance plan from server A to server B.
The maintenance plan is set to use local server connection.
Running the maintenance plan on server B affects the server A instead of the server B.

Is there a way to refresh or repair the "local server connection" so that it actually connect to the current server instead of the one the ssis was imported from.

Here are some references on the method I used.



How to connect OLAP data source WITHOUT using http connection


Hello Guys,

I used to connect my OLAP database with this connection strings :

"Data Source=http://<target IP>/olap/msmdpump.dll;Catalog=<cube name>;Impersonation Level=Anonymous;"


but somehow, it managed to be disconnected for some other reason... 

then, i try to not using HTTP connection...

""Data Source=<target IP>;Catalog=<cube name>;Impersonation Level=Anonymous;"


the result is, I cannot connect to my OLAP.. 

Any suggestion? I want to connect my OLAP without using HTTP connection...



Best Regards and Thanks,

Roland Andreas

SSIS 2008 can't connect MDB via OLE DB JET 4


I'm using SSIS SQL 2008 connect to MS-Access MDB via OLE DB Jet 4. I used to work but now I couldn't connect any more. I got "connection failed because of an error in initializing provider. unspecified error." I still can connect the via SSIS SQL 2005.

Really need help..


Unable to connect oracle from SSIS management studio


the connection failed because of an error in initiallinzing provider. ora-01017: invalid username/password; logon denied.


but can connect from SQL PLUS.

My local pc does not connect to SSIS. Is there something I need to do set up the connection?


My local pc does not connect to SSIS. Is there something I need to do set up the connection?

Mr Shaw

Reduce Load Time of Cube Data using SSIS


Hi, I have an issue where I am trying to reduce the time it takes to load SSAS cube data to SQL table using SSIS.

Within a Data flow task I have an OEDB source where in I select "command from variable" where the variable holds the dynamic MDX. I then have a data conv, lookup, derived transform and OLEDB destination respectively.

However the load takes some 2.5 hours for inserting 1 million rows. Any pointers as to how I may reduce it?

I have around 110 columns returned from the source MDX and it has 1 million rows as I said.

the lookup is to get a key not present in cube. since the lookup table is huge, i am using partial cache and the derived column transform just adds a string (hard coded) value of "ETL" and a time using getdate(). Data conversion is for the obvious conv for ntext data (returned from MDX to corresponding datatypes in SQL table...I have the fast load option in the OLEDB destination.

I can't use openquery though. Let me know what may I do to reduce the load time.

SSIS connect to oracle database


In datasource designer for adding a new data source, I am attempting to connect to an oracle server. I then enter credentials and click 'test connection', and get this error.

Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect indentifier specified.

I am using Oracle 11g as my framework.

The strange thing is if i use enterprise manager using 10.2.1 oracle framework, I cannot see any tables. The only way I can see tables if I go and click on "other  users" and then click on the specific user previous employees have used as their login credentials to generate an SSIS package.

I have already set up the access using enterprise manager, but ssis does not follow? Or something?

Thank you in advance.

How can I copy a SSIS package from one server to another server and maintain the same OLE DB Connect


I have an SSIS package that has 10 data flow tasks that was created in Visual Studio 2005.

Each one has a different OLE DB Source but the same OLE DB Destination table.

I am moving from one 2005 server (A) DB to 2008 server (B) DB.  The DB has the same name. The 3 connections used for the 10 different OLE DB Source will be on server (B). The 1 connection used for the 10 different OLE DB Destination will be on server (B). The same OLE DB Destination table is also on server(B). 

When I convert this package to Visual Studio 2008, I lose my connections and have to change each one to the connections on server (B).

Is there a way to change the OLE DB Connection manager 1 time, without having to change it 10 times for the OLE DB Source?

Is there a way to change the OLE DB Connection manager for the OLE DB Destination 1 time, without having to change it 10 times for the OLE DB Destination table.

Thanks for your help.


How can a asp.net application (using Adomd.net) connect to SSAS CUBE?


Hi Guru,

Here is a very simple scenario:

a asp.net application retrieves data from remote SSAS 2008R2 CUBE.

      string conString = "Data Source=;Catalog=Adventure Works DW 2008;";

            AdomdConnection con = new AdomdConnection(conString);

            AdomdCommand command = new AdomdCommand("select from [Adventure Works_1]");

            AdomdDataAdapter da = new AdomdDataAdapter("select from [Adventure Works_1]",con );

            DataTable dt = new DataTable();



I already set:

1. localservice, anonymous, network service accounts in both SSAS Instance Security and SSAS DB roles.

 2. RequireClientAuthentication = false.

unfortunately, I always got the following message:

"An existing connection was forcibly closed by the remote host"


Should I set up HTTP access for SSAS? and why?

Thank you very much for your kind help!!!





InfoPath 2010 cannot connect to SharePoint 2010

You have installed Office 2010 beta and you are trying to connect to SharePoint 2010 from InfoPath but you get an error
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