Load analysis services data into an Access table

Posted on 2009-12-22
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.
Question by:lee_jd
    LVL 31

    Expert Comment

    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).  
    LVL 2

    Author Comment

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

    Accepted Solution

    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

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

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

    select * from openquery(SSAS,'
          {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]

    CREATE VIEW [dbo].[v_MDX]
    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, '
          {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.


    best regards


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now