I have a staging database (relational) where I have a table (staging.IntakeAndClosure) with all the lookup data i.e. Ids are all ready resolved. I want to move this data as is to the Fact table IntakeAndClosure. I have already created packages for the dimension tables. I need to create a package to move the fact data from my staging (relational) db to the cube. Any idea how I would do this using SSIS packages? I have Ole DB Destination-component which is connected to the cube, but I can only see my dimensions and cannot see any fact Tables Thanks in advance

Hello Daminu, i really don't know if i got u right. At first you got a Fact Table that is in the Staging Area and you want to move the Data to the same Table in the "FACT" Scheme. So why not using partitions? Or do you need the Data still in the Staging tables? Or is a view the solution for which you are looking for. At next, if you got the Data on your Fact Table you can use a SSIS Package, which executes a SSAS-DDL Command on the SSAS Server. You will get an example of the XMLA Command on http://technet.microsoft.com/en-us/library/cc966525.aspx Greetings

Daminu - Bit confused in what you are trying to accomplish. From what I understood, you have a table in staging the contents of which you want visible in a cube, correct? The cube in turn has to get the data from somewhere, which in your case could be a fact table in a db called DW. Why can't you use a SSIS package to transform and copy the data in a form you want seen in your DW? Use this fact table in your DW to feed your cube.Jagannathan Santhanam

Hi, I do not have a fact table in the staging area. I have a relational table in the staging area but the IDs (foreign keys) are all resolved. I would like to move the data from this table to a fact table in a cube in analysis server using SSIS Package. I am using a Ole-DB Destination componenent which can connect to the analysis server. In the "Ole DB Destination Editior" and in the drop down list "Name of the table or the view" I do not see any fact tables I can only see my dimensions.

Hello Daminu: I think you got the connection to your RDMS (relational), via ODBC, but have been unable to move data to one of the fact tables comprising the cube. Is that right? If yes, unless you need to use a Slowly Changing Dimension component, it is quite ordinary, just use the OLEDB Destination.Arthur My Blog

Hi, Yes, you are right, I have an Old Db provider to my Source table (Relational) And Ole-Db Destination to my target (Cube/Analysis Server), But I cannot see the fact tables in my cube. I can only see my dimensions

For the destination Ole-Db component, I am using the following provider: "MS -Ole Db Provider For Analysis Services 9.0". What am i doing wrong?

If I log in to the analysis server using SQL Server Management studio with the same account, in my cube, I can see facts/measures and the dimensions.

So but the Measuregroup is allready developed isn't it? You don't use an "Dataflow" for moving Staging Data to an Cube Fact "TABLE". You have to define in the Cube that you are using the Facttable. So try the following Code at the DDL-SSAS Element at the Controlflow part of SSIS: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"> <Object> <DatabaseID>SSAS_DATABASEID</DatabaseID> <CubeID>SSAS_CUBEID</CubeID> <MeasureGroupID>SSAS_MGID</MeasureGroupID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch> And replace SASS_DATABASEID, SSAS_CUBEID, SSAS_MGD with the IDs from your Cube Project. Then use the SSAS DDL Task Sorry for non english version :)

check this link and this Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

So but the Measuregroup is allready developed isn't it? You don't use an "Dataflow" for moving Staging Data to an Cube Fact "TABLE". You have to define in the Cube that you are using the Facttable. So try the following Code at the DDL-SSAS Element at the Controlflow part of SSIS: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"> <Object> <DatabaseID>SSAS_DATABASEID</DatabaseID> <CubeID>SSAS_CUBEID</CubeID> <MeasureGroupID>SSAS_MGID</MeasureGroupID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch> And replace SASS_DATABASEID, SSAS_CUBEID, SSAS_MGD with the IDs from your Cube Project. Then use the SSAS DDL Task Sorry for non english version :)

Hi Sharp, Thanks for your reply, Where do I specify the source (staging/relational) database / table information in the XML? Thanks

Hi, In the script below, where do I specify my source relational table. Dim oConnection As ConnectionManager oConnection = Dts.Connections("DM") Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection)) Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection)) Dim oServer As New Microsoft.AnalysisServices.Server oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabaseID) There must be an easier way of transfering fact /measure data from staging (RELATIONAL) to a cube.

I am curious: is that a possibility that you do not see the fact tables because of the account access rights? Are you able to use the same account to connect to the destination and see both types of the tables?Arthur My Blog

Hi Arthur, Yes. I am using the Admin account on the server

Daminu, i don't know what you have allready done with SSAS but you don't move data into a cube like you move Data between two tables. This "moving data into a cube" is more a pull than a push operation. Then you are in SSMS and see the Cubes and Dimensions, you can RightClick on the cube and click on Process. Then the Analysis Services begin to grab the Data from the RDMS and integrate it in the Cube. Perharps you also have to Process your dimensions, because if there are new keys for the Dimensions (for example new customer) you would get an error if you only try to process the cube(because he cannot find the key for the new customer on the Dimension). You also don't have to specify your "Relational Source". Thats because you have to specify the relational data while you're designing the cube. You can see which relational database your cube uses then you open your Analysis Services in the SSMS and open your database and then study the "Datasources". If you want to see, which Table is used by a specific Measuregroup, open in your SSMS your databse and cube, continue to open the measuregroup down to "partitions" and double click on one partition. Now you can see the "Source" where the table is selected. So now it should be clear, that your relational source is allready specified in the cube himself. Then you process the cube, he'll try to read the tables and grab all new information which he can get. There also another Task your will find perharps helpful, the Analysis Services processing Task. Here a link with more information: http://www.mssqltips.com/sqlservertip/1560/analysis-services-processing-task-in-sql-server-integration-services-ssis/ If you want to change the relational database, the easiest way is to use the Cube Deployment Tool. On the third page there are some lines (like Account for Processing etc.). There is also a Line "ConnectionString". This is the Link to your server! But you can't just select the table.

HI repolevedlqs, Thanks a lot. I double clicked on my cube and was able to see the data sources that pointed to my staging DB. So I do not need any of my SSIS packages, right? All I need to do is process (Process Option =Full) the cube and then all the data will be refreshed from the staging DB where are all IDs etc are already resolved. Right now my fact table is not populated with data. Once I populate it I will try it out. THanks a lot.

Hi Daminu, no problem, the forum is here to help :) But one thing, you don't need a SSIS Package. But if your cube should be processed without your work, you can build a SSIS Package with an AS DDL or Process Task. So your development usage the manuel processing is ok, and i do it all the time. But in the productive enviroment, the cube shouldn't be processed manually. Greetings