OLTP access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 18 Mar 2005 11:37 GMT

Hi,
With my OLTP on another server, OLAP cube design works up to the point of
actually processing the cube, then it errors processing the dimension.
If I move the OLTP database (SQL Server 2000) onto the machine running the
OLAP it works fine.
It seems to be a rights issue but the ODBC tests work fine and the cube
design has no trouble viewing the OLTP schema.
In its 'normal' position the OLTP is on a Win2k Server that is part of a
work group.
The OLAP is on a Win2k Domain Controller.
Any clues on how to establish a successful processing of the OLTP in its
normal position would be appreciated.
Thanks
Bob

Reply to this Message

bc - 19 Mar 2005 15:53 GMT

have you made sure the services for OLTP sql server and AS on the other
server are started with the same admin account and not local?
When you are in the design in AS, you are authenticated using your login to
the sql server source so it is ok there, but when you are processing, it is
authenticated using the login that start up AS, so that login might not have
access to the sql server tables to process the dimensions.

> Hi,
> With my OLTP on another server, OLAP cube design works up to the point of
[quoted text clipped - 10 lines]
> Thanks
> Bob

Reply to this Message

Bob - 22 Mar 2005 00:27 GMT

Hi,
Thanks for your reply.
I altered the OLAP service to run under administrator but it has made no
difference.

This is extremely poor design in IMHO. If the ODBC object can connect to
the OLTP, the OLAP should be able to process, end of story.

There is no trusted connection between the OLAP machine (Win2k Domain
Controller) and the OLTP machine (Win2k Server but running as a member of a
workgroup)
So I am relying ODBC to sort out the security issues.
e.g.. Access can use ODBC to link to tables in the OLTP so how come the OLAP
is so frail?

I have tried to create an ODBC connection to the OLTP that uses SQL server
authentication but have failed . I keep getting
'Not associated with a trusted SQL server connection'.
In a perfect world I would promote the OLTP to be a domain controller of its
own domain and establish a trust relationship between the to domains. But no
can do.

I think Bill's merry men should be looking at this.
Either I am missing something fundamental or the OLAP connection needs
redesigning.
No way should it be this difficult to use in this situation.
i.e. Anything that Access can do, OLAP should be able to do. The security
implications are the same.
regards
Bob

> have you made sure the services for OLTP sql server and AS on the other
> server are started with the same admin account and not local?
[quoted text clipped - 17 lines]
> > Thanks
> > Bob

Reply to this Message