How to Import Custom Member Properties?

­­General Discussions
1

Hi All,

I’ve just added an alternate hierarchy to the Account dimension of a cube. The custom member property name is Account Group.

This existing cube has about 200 members in the Account Dimension. There is an established parent-child hierarchy and other custom properties that were populated when the cube was originally built.

I have a table in an Excel file that has Account Group property value mapping that I want to populate for about 150 of the 200 members.

It’s not clear to me how I can easily populate the Account Group property for these members while maintaining the existing Account dimension properties.

When doing a Dimension Import from a flat file, Prophix gives the choice of “Add to Existing” or “Overwrite Existing”.

The former causes the Import Dimension process to generate a rejection message that the member already exists.

The latter will delete any members or properties that I don’t include in my Dimension Import table.

It’s possible that I could export all my existing Account Dimension properties, then merge the new Account Group property values, then Import that merged table using the Overwrite Existing. I’m hesitant to do that unless there is no better alternative for fear of the consequences if I do this incorrectly.

I’m also not sure how to export all the existing Account Dimension properties outside of Prophix so it can be merged. The Export Dimension process allows the dimension properties to be exported to a TXT file which resides in the Process Files folder, but cannot be saved outside of Prophix.

Thanks in advance for any advice!

2

Hey @jerry.sullivan,

You’re on the right track. Since you have 150/200 accounts to update with the Account Group member property, your best option would be to re-import the entire Account hierarchy with all properties (key, name, member property 1, member property 2, etc).

Do you mind telling me if you are on Cloud or On-Prem? If you are on Cloud, you will need assistance from a Prophix resource. These would be the steps:

  1. The Consultant would run an Export Dimension process for your Account dimension, to a table
  2. Consultant will access your back-end tables to retrieve the exported Account table, and copy and paste it into Excel
  3. Excel document will be sent to you to add the new Account Group property (v-lookup will probably be the quickest way to fill this out)
  4. I would definitely advise to keep a copy of the excel file with the original Account dimension export. Although this is a fairly simple process, should anything go wrong, you have this back-up copy to re-import into Prophix.
  5. With the updated file, you can then import the Account dimension into Prophix using “Overwrite Existing”. In the Target Mapping tab of the import, all fields should easily populate with the “auto map” feature.
  6. Validate in Model Manager that everything looks correct!

If you have an On-Prem solution, you can still follow the steps above, the only difference being steps 1 and 2:

  1. You (not the Consultant) will run an Export dimension process for your Account dimension, to a table
  2. You will need access to your back-end (SQL Server Management Studio) or ask your IT to find the exported table and copy & paste it into excel.
  3. Steps 3-6 will be the same.

Whether you are Cloud or On-Prem, this would be a good case to reach out to a Consultant with your CSP who can help guide you, if you are not comfortable making the changes on your own.

If you do not have CSP, I would reach out to your CSM and ask them to point you in the direction. OR, the export feature in the Process Files folder is an opt-in feature. Details on this opt-in feature can be found here. If you would like to go through this process, then you can export the Account dimension to a TXT file, export the file from Process Files, and follow steps 3-6.

If you are not comfortable at all with re-importing the dimension, your other option would be to manually fill in the Account Group property for all necessary accounts, in Model Manager.

hope this helps!
Lilian

3 Likes
3

Just went into the Support portal and checked to see that you are a Cloud customer. You may ignore the steps for the on-prem solution and involving your IT, but other steps remain the same :slight_smile:

2 Likes
4

Hi Lilian,

Thanks for the very clear response. Yes, we are on Cloud, so it sounds like our choices are to work with our CSP or to do the opt-in to download TXT files. I think we can handle the export-import once we can access the TXT file. I mostly wanted to make sure I wasn’t missing an obvious shortcut.

Our CSP consultants have been excellent. I like participating in help forums when possible since I tend to learn more that way.

Thanks much Lilian! :smile:

2 Likes
5

No problem at all. Awesome to hear that you’ve been engaging with both the CSP & Red Carpet!! Cheers

1 Like