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