I'm hoping this problem is well known but I just can't find mention of
it. I want to use SQL Server 2005 for analytic and report delivery. The
source data and the data warehouse reside in Oracle 9i. Analysis
Services 2005 is not able to work with the Oracle data satisfactorily.

I installed SQL Server 2005 Dev edition on Windows 2003 Server Std. In
the last hour, I saw that SQL Server 2005 has service pack; I have not
applied the SP yet.

Ok, back to my story: Using the BI Workbench, I set about creating a
cube over a dimensional model in Oracle. I had no trouble setting up
the Data Source and no problem setting up the Data View. I can even
right-click the tables in the view and explore the data source data
that is living in Oracle.

But I cannot deploy anything. I can design the dimensions but when I
try to process & deploy, an error message tells me that "the user is
not set up in Analysis Services or the password is bad" (paraphrased
error message). I saw some posts to this group that had similar
problems but not quite. And I did not try some of the solutions like
setting up a local user account with the same name as my Oracle
user/schema name.

I've continued with my work by cheating. I migrated the data mart to a
SQL Server 2005 database on the same server as AS2005 and replaced the
AS project's data source with a new data source -- OF THE SAME NAME --
that ponged to my SQL Server 2005 data mart. Everything just worked.
The dimensions I designed were able to process without a hiccup.

Like I say, this is such a clear cut Oracle data source / credentials
problem, I hope it is well documented and somebody can help me find
answers. I'll install the Service Pack tomorrow and repost with an
update. But let me know if you have run into this and have found a
solution.

Many thanks,
David

how the credentials are setup to access your source?
have you installed the Oracle client and crete a TNSNAME? if yes, then
insure that the account used to run AS can access the ora92 folder.



<davidb (AT) mercurydata (DOT) com> wrote

The Data Source is set up to use the Oracle client just below the
top-level SQL Server client choice (Sorry...I'm working from memory
this evening). I specify the fully qualified name of the instance
IBDEV.xxx.NET and then the schema name and password. I checked the box
to save the password page. On the Impersonate tab, I specified the same
schema name and password. The option for using the current credentials
was not an option. When I set that option and tried to process a
dimension, it told me my data source used an impersonation option that
was not supported. So I assumed that the credentials they wanted was
for the Oracle schema.

It's worth noting that the Oracle schema is not at all tied to a
Windows AD user account. It's a schema set up for the data warehouse:
DWHS.

About the AS service account accessing tnsnames.ora...I certainly see
how that is very important. But wouldn't I have faillures to access
Oracle data everywhere?

Thanks for your quick response and great ideas!

-David

if other services run under a the system account this account can access the
ora92 folder
if you run under a specific user account, maybe this account doesn't have
enough rights on this folder.

Allow "Authenticated users" to read this folder should solve the issue.

<davidb (AT) mercurydata (DOT) com> wrote

I did a foolish thing: I changed two things at once and the problem was
resolved...so what did the trick.

(1) I applied the service pack
(2) I changed my settings on the Impersonation tab of the data source
definition to use the Service Account rather than specify a user
account.

I suspect that the problem was actually resolved by (2). I think I was
making trouble for myself on the Impersonation page by selecting a
specific user and then providing credentials for the Oracle schema. I
think I just misunderstood what the impersonation tab is there for. It
is to specify a Windows account that will be in charge of managing the
data source. So I needed to specify an account that has the rights to
the Oracle client and can successfuly provide the Oracle credentials.
Am I on the right track?