Skip to Content
author's profile photo Former Member
Former Member

Create BPC - Dimension with SSIS Admin Task

Hallo experts,

I am figuring out features for BPC - Packages and wanted to create a dimension within a SSIS Package.

The "Admin Task" offers this feature, but unfortunately I can't find any documentation about the SQL Source Table,

which ist needed when configuring and executing this task.

Help is appreciated about the structure, location and naming-conventions for this table.

Another Question:

Where does the Admin Console store the dimension structure e.g. of the account dim?

Thanks in advance

Jochen Kowalski

P.S: I'm working with BPC 5.1 SP3 and SQL-Server 2005

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2008 at 12:37 PM

    Hi Jochen,

    The dimension ACCOUNT is stored in the database of your SQL Server. Please follow my instructions:

    Connect to your database engine

    Databases > APPNAME > Tables

    (APPNAME being the Application set in BPC)

    In tables look for dbo.dimAccount and right click on it. Then select Open Table

    You should have everything in there.

    Nic

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2008 at 12:46 PM

    Jochen,

    The SQL source table must be MBR<dimname> or a staging table with the same layout as MBR<dimname>

    There are two tables supporting a dimension:

    MBR<dimname> stores the records as in the xls sheet but with a few extra columns (calc/hlevel)

    DIM<dimname> is created during the process of a dimension, and is used by Analysis Services. I advice never to change this table yourself.

    Drop me an email if you want an example package using a staging dimension table including some documentation.

    Alwin

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 15, 2008 at 11:24 AM

    Hallo Alwin

    thanks for your answer.

    I can start the package now and it is updating the dimension.

    To whom can I give this information for an update of the BPC - documentation?

    Jochen Kowalski

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 31, 2010 at 02:43 PM

    You may use SSIS tasks to automate the modification of the dimension member sheet. Instead of exporting the dimension sheet this will help you automate this process.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 13, 2010 at 01:48 AM

    Brian, your explanation is sound and i understand your requirements.

    Essentially you need to run a dimension process on both your Prod/Dev servers.

    At one of our clients, we customised the Dimension excel sheet with some VBA. Administrators could still process the dimension sheet normally but they could click a command button that dumped the excel file to dev and then launched an SSIS package - the main problem is that we used XPCMDSHELL to kick the package off that processed the dimension in dev.

    see http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/d4d69654-e2cc-4aa7-90f3-701b8180de36/

    After much thought we decided it was best to use SQL to keep the databases mirrored, SSIS to keep the OLAP procssed [in dev] and we used a batch script to transfer the webfolders.

    You may notice that the final solution is very much like the automated backup solutions that many BPC practitioners have gone with in the past.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      We wrote a similar solution, it allows to synchronize DEV and PROD environments (security, fact data, dimensions, business rules, work status, comments,...). So basically you can select source and destination environment (appsets) and scope of tasks. Just in case anyone is interested...

      Best regards,

      Radim

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.