1

Per the project requirement, I want to migrate all the data from the Microsoft SSAS cubes to Teradata database.

So I am not very sure about the best approach to do this activity. First thing comes into my mind is to get extract of the data in excel files & then load it into staging tables using FastLoad or MultiLoad utilities available in Teradata.

Can you suggest any better approaches to accomplish this activity, if available ?

Thank you,

Aditya
  • 2,299
  • 5
  • 32
  • 54
  • What flavor of SSAS? Multidimensional or Tabular? – GregGalloway Dec 17 '16 at 13:18
  • Hi Greg, I believe it's a multidimensional flavor. Does it matters ? – Aditya Dec 18 '16 at 17:49
  • yes. Tabular will definitely store the lowest level row detail in all tables and it's easy to get data back out. If Multidimensional sees two fact rows with the same dimension keys in the same buffet it will summarize up and store as one row – GregGalloway Dec 19 '16 at 13:09
  • 1
    I would recommend you figure out the SQL source of the cube and work on migrating from there. Since its multidimensional you will have more success that way. – GregGalloway Dec 19 '16 at 13:10
  • You could define Teradata as a linked server, and then bulk-batch tables one-by-one from inside a cmdsql/isql scripts or even an ssms window. – access_granted Dec 20 '16 at 01:55
  • I'd recommend against involving Excel at all. It's a truly dreadful tool for handling data. Misrecognised dates, numbers stored as text... If you have to use an intermediate file-system stage, rather than direct import/export, flat files (with regard to text qualifiers etc) are the way to go. – SebTHU Dec 20 '16 at 09:42
  • @GregGalloway, the problem is we have history data in MSBI Cubes only. So getting the data directly from source table using SQL not possible at the moment. Perhaps, this is the reason we are planning to migrate Cubes to Teradata and henceforth will maintain history data in the tables. – Aditya Dec 20 '16 at 13:25
  • @GregGalloway, I will once again confirm the source is tabular or multidimensional. I hope it should be tabular so that we can figure out some feasible solution. – Aditya Dec 20 '16 at 13:29
  • @Aditya if you connect to Analysis Services in SQL Server Management Studio in the Object Explorer window you can expand the Data Sources folder and look at the connection string for each data source. It would be highly unlikely that the data only exists in SSAS. – GregGalloway Dec 20 '16 at 13:55

0 Answers0