Connecting to AS400 from SSAS 2008 Project

Posted on 2009-12-16
Last Modified: 2016-02-12
I am trying to connect to AS400 server using IBM iSeries Access for Windows from an SSAS 2008 project (from BIDS). I am able to create a datasource to the AS400 Server using IBMDA400 provider and the connection test is also successful. But when I try to create a data source view based on this datasource, it is not dispalying any tables/views from the underlying AS400 database. Why is it not displaying the tables/views list?

Secondly, I have created a blank data source view based on this datasource. Then I have tried to create a named query using a query (which fetches the data from a table from AS400 database). It is able to resolve all the columns names (ie it is replacing the * with the column names) and when I run the query in Named Query definition window, it is giving the results. But when I click on OK button to save the Named Query, it is not displaying the columns names in the DSV pane and the Named Query is not getting recognized when I try to select a table from the existing DSV while creating a Dimension. Please do let me know the reason for this. Bit urgent.
Question by:Srinivas_Vengala
    LVL 34

    Expert Comment

    by:Gary Patterson
    Please post your connection string for data source.

    I'm not sure about the second issue.  PErhaps another expert can shed some light.

    - Gary Patterson


    Author Comment

    Here is the connection string:

    Provider=IBMDA400.DataSource.1;Data;Persist Security Info=True;Password=**********;User ID=test;Initial Catalog=db1
    LVL 34

    Accepted Solution

    Based on your connection string, here are a couple of thoughts:
    • The user ID that you are using ("test"), might not have rights to the libraries or tables required.
    • Does your "Initial Catalog" name match the RDB name configured on the AS/400?  You can use the WRKRDBDIRE and look for the entry with a remote location of *LOCAL.  That is the name that you need to use as the Initial Catalog.  You can generally drop this parameter when connecting to the AS/400.
    • You are not specifying a library list or default collection, so the provider will default to the system library list.  These libraries may not contain the tables that you are interested in.  You can specify the "Catalog Library List=lib1,lib2,lib2..." connection string parameter to define the library or libraries that you are interesting in browsing, or the "Default Collection=lib1" parameter to specify a single library.  Note that the library list is referenced when making unqualified references to table names, and is used when prompting.  When executing queries, if you qualify the table name, then the library list does not matter.
    - Gary Patterson

    Check out my EE profile:


    Author Closing Comment

    I will check this

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    729 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

    24 Experts available now in Live!

    Get 1:1 Help Now