0

I was wandering, if there was a way to reverse engineer an SSAS OLAP cube back into its original relational representation. Is such a thing possible?

Eugene Goldberg
  • 14,286
  • 20
  • 94
  • 167
  • When you say reverse engineer, are you talking about a query to get data back into a tabular format? Or are you talking about recreating the tables and that are being used for Facts and Dimensions? – Wyatt Shipman Mar 10 '16 at 21:18
  • I'm talking about getting data back into Tabular format – Eugene Goldberg Mar 10 '16 at 21:19

1 Answers1

1

I was able to set up a linked server on my Sql Server and then use an OpenQuery to get data out. It is a little bit ugly, but you can do it like below:

SELECT "[Dimension Name].[Hierarchy Name].[Department Name].[MEMBER_CAPTION]" as Department,
  round("[Measures].[Some Calculation]",3) as Value,
  'Value_Descr' as Value_Descr
FROM OPENQUERY(SSAS_Link,' SELECT NON EMPTY { [Measures].[Some Calculation] } ON COLUMNS
    ,NON EMPTY {([Date Dimension].[Hierarchy].[Fiscal Year Code].ALLMEMBERS * [Dimension Name].[Hierarchy Name].[Lowest Hierarchy Level Field].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION
    ,MEMBER_UNIQUE_NAME ON ROWS FROM (
    SELECT ({ [Dimension Name].[Field to Filter].& [Key of Field to filer] }) ON COLUMNS
    FROM (SELECT ([Fiscal Week Code - Last Week]) ON COLUMNS FROM [Cube Name])  )
    WHERE ([Dimension Name].[Field to Filter].& [Key of Field to filer]) ')
Wyatt Shipman
  • 1,669
  • 1
  • 10
  • 22
  • I wrote a post about creating a [Linked Server to Analysis Services](https://bizintelsolutions.wordpress.com/2015/01/03/openquery-from-analysis-services/) if you need to set one up. – Wyatt Shipman Mar 11 '16 at 16:02