Hi,
I have a SQL Server (RSQL) and a SSAS Server(RSSAS). RSQL has a linked server to RSSAS, naming [LinkedCube]
Here is a screenshot of my Linked Server Properties
I use SSMS to connect to RSQL and then run a query that returns data from SSAS using linked server. The result set contains 100,000 rows and it took 17 seconds to return data completely.
The Profiler said that MDX query duration is about 3 seconds, so it took 14 seconds to return data.
The network connection has a maximum transfer rate at 100MB/sec, so 14 seconds is not acceptable.
Do you know why it took to much time to transfer data ?
In my opinion, I think that there are problems with OLE DB for MOLAP. I use format=Tabular in connection string, default SASS format may be format=Native , so it need more time to transform data format. If I use Excel to get the same data from SSAS, it is faster, 5 seconds in total. Excel use ADOMD.NET instead of OLE DB Provider and Excel request data using Native format.
EXEC('SELECT {[Measures].[Qty]} ON COLUMNS,{[Product].Members} ON ROWS FROM [AnalysisServicesCube]')AT [LinkedCube]
Thank you