[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Load analysis services data into an Access table

Posted on 2009-12-22
4
Medium Priority
?
322 Views
Last Modified: 2016-02-13
I have a cube in SQLAS 2005.   I also have created an MDX statement to query the cube and return data (using pro-clarity).

What I really need to is use MS Access and import the data queried by the MDX.   If there is someway for me to pass the query from Access to SQLAS then this would be ideal.

I can't see how to do this so any help would be well appreciated.
0
Comment
Question by:lee_jd
3 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26113656
If the data from the query can be saved as a comma-delimited file or an Excel worksheet, you can import it into an Access database, or link to it, using the built-in tools or code (using the TransferText or TransferSpreadsheet methods).  
0
 
LVL 2

Author Comment

by:lee_jd
ID: 26118171
I was looking for a better solution i.e. take data into a table by querying in Access.
0
 
LVL 5

Accepted Solution

by:
grzegorzs earned 2000 total points
ID: 26144891
I have found a solution, but not easy one... Maybe someone could find a better one...

1. In your SQL Server create linked server pointing to SSAS:

EXEC master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'localhost', @catalog=N'Adventure Works DW 2008'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

2. Now you can execute a MDX Query via linked server - you use SQL Server connection, not SSAS!

select * from openquery(SSAS,'
SELECT  
      {Measures.[Internet Sales Amount]} ON COLUMNS,
      [Date].[Month].members ON ROWS
    FROM [Adventure Works]
')

3. Create a view in one of databases in your SQL Server. Copy query to view definition.
View is necessary because Access imports tables and views via ODBC source. I haven't found place to enter SQL query.

USE [MDW]
GO

CREATE VIEW [dbo].[v_MDX]
AS
SELECT     [[Date]].[Calendar]].[Calendar Year]].[MEMBER_CAPTION]]] AS Cyear, [[Date]].[Calendar]].[Calendar Semester]].[MEMBER_CAPTION]]] AS CSemester,
                      [[Date]].[Calendar]].[Calendar Quarter]].[MEMBER_CAPTION]]] AS CQuarter, [[Date]].[Calendar]].[Month]].[MEMBER_CAPTION]]] AS CMonth,
                      [[Measures]].[Internet Sales Amount]]] AS Amount
FROM         OPENQUERY(SSAS, '
SELECT  
      {Measures.[Internet Sales Amount]} ON COLUMNS,
      [Date].[Month].members ON ROWS
    FROM [Adventure Works]
')
                      AS derivedtbl_1

You must alias each column returned by a view, because Access won't allow [[Date]].[Calendar]].[Calendar Year]].[MEMBER_CAPTION]]] as column name. Enter aliases in the view definition.

4. Create ODBC Connection in Access. You provide driver - SQL Server Native Client, server name. You must remember to enter your SQL Server database (this one with the view) as default one.

5. When you import data from ODBC Source you have to pick a table or a view from list. You should see your view in it. Next you can save import definition.

Another way is to use SSIS to transfer data from SSAS to Access database. Of course you must use linked server to SSAS, and connect to SQL Server connection. Then you can use query from pt. 2.

HTH

best regards


0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question