CODEWITHSUNDEEP
ssasssis-2012ssas-tabular
Ross Brigoli
Ross Brigoli

Reputation: 696

How to transfer data from Tabular model (SSAS Tabular) to Relational Database?

I am new to SSAS and Tabular model cubes. I have a data migration task that aims to import data from a Tabular model into a SQL Server database.

I have tried SSIS with not success. Using OLEDB Data source connected SSAS instance with Tabular database, I configured the data source to execute and MDX command " evaluate ('Tabular Table Name') ". It returns successfully but only partial number of records (around 1000) are returned. So i reconfigured the data source to use OPENROWSET and selected the tabular table that i want to query but I am always getting an error related to Column mapping, even though the column mapping is correct.

Upvotes: 0

Views: 5377

Answers (1)

George
George

Reputation: 702

Okay, I suggest you try this way (I tried it with our PowerPivot databases, which is pretty similar to SSAS Tabular_:

  1. Run SSMS and connect to SSAS Tabular instance
  2. Right-click the database you want to dump into SQL Table and select Script -> Create to -> New Query Window. Be patient and wait until SSMS generates XMLA script for you
  3. Find SQL queries that were used to fill SSAS tabular tables by searching XMLA script (keyword = QueryDefinition)

This is what I got for our database:

                     <Partitions>
                            <Partition>
                                <ID>factLinksSeller_484c3291-2123-4391-8627-fd4b584d1726</ID>
                                <Name>factLinksSeller</Name>
                                <Annotations>
                                    <Annotation>
                                        <Name>IsQueryEditorUsed</Name>
                                        <Value>True</Value>
                                    </Annotation>
                                    <Annotation>
                                        <Name>QueryEditorSerialization</Name>
                                    </Annotation>
                                    <Annotation>
                                        <Name>TableWidgetSerialization</Name>
                                    </Annotation>
                                </Annotations>
                                <Source xsi:type="QueryBinding">
                                    <DataSourceID>15719e99-95fb-44c1-8399-18a769ae1be4</DataSourceID>
                                    <QueryDefinition>select
                                                       *
                                                     from
                                                         dbo.factLinksFull X
                                                     where X.signaturePersonID=16
                                    </QueryDefinition>

Now you can use this queries to load data into your SQL Server DB via SSIS

Even if you filled Excel files by external tool, after importing them to SSAS this data should be stored somewhere, so you could check it in XMLA script and make the decision what to do next

There exists a much more simple way to do it, if you have local PowerPivot workbooks and your memory can handle datasets from those:

  1. Open your PP workbook, run PowerPivot
  2. Switch to Data View
  3. Copy whole table from the tab you are on, by pressing icon in the left upper corner of it, then press CTRL+C and wait for the data to be transferred to the memory
  4. Paste your data in another Excel 2013 file, since they now can handle almost any size of data, or MS Access table. Then import data to SQL Server

Upvotes: 3

Related Questions