update the dimension and it members using admin_task or make_dim

[Former Member]

Hi Experts, I am using BPC 7.5 MS with SQL server 2008 on multi server platform. I am trying to update the dimension and it members using admin_task or make_dim but not successful using SSIS. No error log is generated so difficult to troubleshoot. Has anyone been able to implment this.

[Former Member · Accepted solution]

Did you check the Windows Event Viewer? There usually are entries in the Application logs that may shed some light.

Also, did you register the tasks with SSIS?

[Former Member · Accepted solution]

Thanks Bill. I looked at the Event Viewer and does not contain error also I have register all SSIS tasks.

[JohnL · Accepted solution]

Hi Sampada,

Use the Admin_Makedim.dtsx package.

What are the columns in your mbr<Dimension> table and the columns in your source dimension table?

Thanks,

John

[Former Member · Accepted solution]

Dimension table colums

SEQ1 nvarchar(50) Checked

CALC1 nvarchar(1) Checked

EVDESCRIPTION1 nvarchar(50) Checked

HLEVEL1 nvarchar(2) Checked

SCALING1 smallint Checked

ACTIVE1 nvarchar(1) Checked

BILL_TYPE1 nvarchar(5) Checked

BUSINESS_AREA1 nvarchar(5) Checked

COSTCENTER1 nvarchar(12) Checked

CPFEE1 nvarchar(5) Checked

CUST1 nvarchar(15) Checked

GROUP1 nvarchar(50) Checked

IAC_PMO1 nvarchar(2) Checked

ID1 nvarchar(20) Checked

SEQ2 nvarchar(50) Checked

CALC2 nvarchar(1) Checked

EVDESCRIPTION2 nvarchar(50) Checked

HLEVEL2 nvarchar(2) Checked

SCALING2 smallint Checked

ACTIVE2 nvarchar(1) Checked

BILL_TYPE2 nvarchar(5) Checked

BUSINESS_AREA2 nvarchar(5) Checked

COSTCENTER2 nvarchar(12) Checked

CPFEE2 nvarchar(5) Checked

CUST2 nvarchar(15) Checked

GROUP2 nvarchar(50) Checked

IAC_PMO2 nvarchar(2) Checked

ID2 nvarchar(20) Checked

SEQ3 nvarchar(50) Checked

CALC3 nvarchar(1) Checked

EVDESCRIPTION3 nvarchar(50) Checked

HLEVEL3 nvarchar(2) Checked

SCALING3 smallint Checked

ACTIVE3 nvarchar(1) Checked

BILL_TYPE3 nvarchar(5) Checked

BUSINESS_AREA3 nvarchar(5) Checked

COSTCENTER3 nvarchar(12) Checked

CPFEE3 nvarchar(5) Checked

CUST3 nvarchar(15) Checked

GROUP3 nvarchar(50) Checked

IAC_PMO3 nvarchar(2) Checked

ID3 nvarchar(20) Checked

SEQ4 nvarchar(50) Checked

CALC4 nvarchar(1) Checked

EVDESCRIPTION4 nvarchar(50) Checked

HLEVEL4 nvarchar(2) Checked

SCALING4 smallint Checked

ACTIVE4 nvarchar(1) Checked

BILL_TYPE4 nvarchar(5) Checked

BUSINESS_AREA4 nvarchar(5) Checked

COSTCENTER4 nvarchar(12) Checked

CPFEE4 nvarchar(5) Checked

CUST4 nvarchar(15) Checked

GROUP4 nvarchar(50) Checked

IAC_PMO4 nvarchar(2) Checked

ID4 nvarchar(20) Checked

ORGANIZATION nvarchar(3) Checked

Mbrdimension columns

SEQ numeric(8,0)

ID nvarchar(20)

NEWID nvarchar(20) Checked

EVDESCRIPTION nvarchar(50) Checked

PARENTH1 nvarchar(50) Checked

SCALING nvarchar(2) Checked

Bill_Type nvarchar(5) Checked

Business_Area nvarchar(5) Checked

Cust nvarchar(15) Checked

Costcenter nvarchar(12) Checked

[Group] nvarchar(10) Checked

CPFEE nvarchar(5) Checked

Active nvarchar(1) Checked

IAC_PMO nvarchar(2) Checked

CALC nvarchar(1) Checked

DIMCALC nvarchar(1) Checked

ISBASEMEM nvarchar(1) Checked

HIR nvarchar(50) Checked

[JohnL · Accepted solution]

Hi Sambada,

I see your Mbrdimension table has 18 columns.

How many columns does your Source table have? Do you have a source table? Note that this is not your dim<Dimension> table which you describe above.

You need a source table in which to read your dimension member definitions from.

Usually I create a table using an exact copy of the MBRDimension table.

For example, if my dimension is called Entity then I would create a source Entity table called SrcENTITY using the following SQL statement:

SELECT * INTO SrcENTITY from mbrENTITY

This SrcEntity table is then used as the source of the new dimension data that you want to load into your dimension.

If I add a new row (representing a new entity dimension member) to the SrcEntity table and then run the Admin_MakeDim package, my Entity dimension should get updated with this new entity dimension member.

Thanks,

John

[Former Member]

Hi,

Did you use Admin_MakeDim2008 Task or the old Admin_Makedim task?

Karthik AJ

[Former Member]

Actually, I didn't use the makeDim tasks at all.  I did most of my changes using SQL Scripts and SSIS processes and then the actual cube processing and optimization I did using the AdminTask2008.

Kevin Thurgood

[Former Member]

I have been able to do this in the same environment 7.5 with SQL Server 2008.  What are your steps?  I do the following.

1- Update mbr(Dimension)

2- Export to excel member file

3- process dimension using Admin Task.

Are you on a 32 bit operating system or a 64 bit operating system.  I have 64 and I had to change the flag to run as a 32 bit application. 

Goto Properites of solution > Debugging > Run64BitRuntime = False.

[Former Member]

Thanks Kevin, John and Madis.

I have followed as per your all suggestions:

a)Created the source mbr table as mbrdime table

b) My Modifyscript variable inside the task Admin_makedim looks like below

'DEBUG(ON)

<BR>TASK(Admin_Makedim,DESCRIPTION,Admin_Makedim)

<BR>TASK(Admin_Makedim,APPSET,Alion)

<BR>TASK(Admin_Makedim,APP,app)

<BR>TASK(Admin_Makedim,USERID,user)

<BR>TASK(ADMIN_Makedim,PROCESSMODE,4)

<BR>TASK(Admin_Makedim,DIMENSIONNAME,contract)

<BR>TASK(Admin_Makedim,SOURCETABLE,src_mbrcontract)

<BR>TASK(Admin_Makedim,BAPPLICATIONPROCESS,1)

<BR>TASK(Admin_Makedim,BTAKESYSTEMAVAILABLE,1)

<BR>TASK(ADMIN_Makedim,BSECURITYPROCESS,0)

c)Verified the SSIS package is using 32bit.

d) When executed generated the error

[JohnL]

Hi Sampada, were you able to get this working?

If not, what errors do you get?

Thx,

John

[Former Member]

Hi,

You can use Admin_Makedim package also alongside with Admin console. When processing dimension select "To be scheduled" option.

After this the system creates TempDimension (e.g. TempAccount) table in the database which is the source table fro Admin_Makedim (you have to provide this info in the prompt). Try this first to see if the package works correctly.

After this you can examine the TempTable and use its design for your needs - its basically a copy of mbrTable.

Hope this helps,

Madis