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

Question

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.

Why do you need this? Your SSAS Tabular is filled from the same SQL database, so why not just use queries that are used in Tabular project? — George
Long story. We have several ExcelFiles with PowerPivot data that was filled from the SQL views in relation database which was the output of a reporting application. Due to terrible performance of PowerPivot, the reporting application was modified to feed data to an SSAS Cube instead of the PowerPivot files through new SQL Views. The schema of the new Views is very different from the Views that was feeding the PowerPivot files. Now what we need is to import the old Excel/PowerPivot files into the new SSAS Cube but we cannot do this without performing transforms to align with the new schema — Ross Brigoli
Basically, we need to feed the Cube with data from the Old PowerPivot files through the new SQL Views. We managed to import the excel files into a Tabular model. Now we need to get it in the relational database to be able to perform the transforms and feed the new Views. Any idea will be very much appreciated. — Ross Brigoli

George George · Accepted Answer · 2015-02-17T14:44:09

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

Run SSMS and connect to SSAS Tabular instance
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
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:

Open your PP workbook, run PowerPivot
Switch to Data View
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
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

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

1 Answers