SSIS BPC Make Dimesion Task fails

[Former Member]

Hi

I haven't heard of BPC until 2 days back.. we have an SSIS (SQL Server 2008 SP1) package failing due to 'Make Dimension Task 2008' (attached image bpc_fail.png) error.

Not much detail regarding cause of error, even after checking 'Verbose' under Reporting when executing the package.

Nothing in sql logs.

I tried changing existing user (COMPANY\OldUser) to COMPANY\User under Server\UserID within the task, but got (attached image bpc_invalid.jpg) error.

Both OldUser & User have dbo permissions on AppSet db.

AppSet is database name, right? Then how to specify server name?

Is it sql db or ssas db? Does the task use any connection manager?

I ran a trace on the server I thought it connects to and yes, the task runs.

It creates mbrPeople, dimPeople tables., updates them, then a cursor runs and then it fails without giving reason.

up_ParseMemberHierarchy - is probably the SP where it fails.

We don't know when the task ran successfully. If at all it did, probably 6 months back, because OldUser has left company (but his AD account is still active).

Any ideas.. either to fix 'Task Failed' issue or 'Invalid User or no server response' issue ?

[Former Member]

any ideas or pointers?

Any way to get more details from the error ? Does SSIS logging help?

[former_member186498]

Hi,

first of all you can create the new dim also manually with the admin console from "dimension library\add a new dim" and import data from SQL through run packages.

I never used the make dimension task, but

"AppSet is database name, right? " yes

"Then how to specify server name?" in server\userid I think (probably full domain server name)

"Is it sql db or ssas db?" aren't it on the same server?

"Does the task use any connection manager??" it doesn't seems so.

Kind regards

     Roberto

[Former Member]

first of all you can create the new dim also manually with the admin console from "dimension library\add a new dim" and import data from SQL through run packages

where is this 'admin console' ?

i'm using Visual Studio, and I can either use 'Admin Task 2008' or 'Make Dimension Task 2008' from the Toolbox. Both give same 'Invalid UserID or server is not responding' error.

"AppSet is database name, right? " yes
"Then how to specify server name?" in server\userid I think (probably full domain server name)

That Server is the Domain of the user (like COMPANY\User).

I meant where to specify the sql server to connect to ? like SQLAS3.EmployeeDB, where SQLAS3 is the server name, and EmployeeDB is the database name in AppSet.

"Is it sql db or ssas db?" aren't it on the same server?

Yes, they are on the same server. I was just wondering which one the task uses. Both?

I know from the trace I ran that it definitely uses the sql db. Not sure about the ssas db..

[former_member186498]

Hi,

"where is this 'admin console' ?"

admin console is a part of bpc. You can intstall it if you have the security rights for admininstrator.

On the launch page just click on the "client sw center" on page bottom and install the admin client or from the bpc server search and install the "everestAdmin.exe"

You can connect to the admin console through the connection wizard on the server, if you have the admin rights this should be work, if not there are other issues.

Click on "Dimension library" on the left and after to "add a new dimension" in the action pane (right) after you can launch a standard import package (that use also the 'dump load Task 2008').

"i'm using Visual Studio, and I can either use 'Admin Task 2008' or 'Make Dimension Task 2008' from the Toolbox. Both give same 'Invalid UserID or server is not responding' error."

To see which parameter to use I think it's better to open the package from pac "organize package list" choose an existent package, f.e. admin optimize (that use admin task), right click, mody package, and after the icon "view package" right from the file name, "advanced" and you will see the "MODIFYSCRIPT" in a more readeble form, the you will see how they pass the parameters.

Normally it easier to start from bpc to modify the package

"I meant where to specify the sql server to connect to ? like SQLAS3.EmployeeDB, where SQLAS3 is the server name, and EmployeeDB is the database name in AppSet"

the 2008 are BPC task not Microsoft, they connect through the application server and don't need to specify the DB server.

"Yes, they are on the same server. I was just wondering which one the task uses. Both?

I know from the trace I ran that it definitely uses the sql db. Not sure about the ssas db."

for you is transparent, if you check also to "perform a full process..." then surely it will use the ssas (olap).

Kind regards

     Roberto

[Former Member]

ok i asked the SAP admin to give me rights,

now i can open - http://server/osoft

But i don't see BPC itself installed.. there is just SAP Server Manager, which I can't access (looks like only the admin who installed it can access, inspite of giving me access).

I'm checking with the admin..

[former_member186498]

Hi,

it seems that you haven't installed also the client BPC.

You can check in the installation programs to see if SBOP BPC is installed, or simply open excel and see if the tab "PLANNING & CONSOLIDATION" is present, maybe it's going wrong the installation on the machine, so reinstall

You must see not only BPC Web but also for Excel, word, ppt.

To install and open the admin console you must be a BPC admin, eventually login with admin user and psw instead of yours.

Kind regards

     Roberto

[Former Member]

Fixed the problem !

The COMPANY\OldUser account which the task was using - that account was Active in AD, but the password had expired.

Like I said in first post, it didn't work even after changing it to COMPANY\User.

After talking to admin, found that we need to add COMPANY\User to admin security group in SAP BPC front-end.

Then it worked.

Silly me..

[Former Member]

looks like i was wrong about what permissions are needed.

Adding COMPANY\User to admin security group in BPC front-end - is not the real reason why the task succeeded.

The package sits on a server APP3 - whatever account is used to RDP into the box - that account needs to be sysadmin on the db server.

It doesn't matter what the UserID is specified within the Make Dimension task, it still works.