by John Hormaechea , Director, Product Marketing at Pyramid Analytics

When I was a presales engineer I’d come across a number of customer requests. One common one was how do I quickly create a set of specific large list of elements. My first reaction was always to recommend the Advanced Set Designer. But often the request would come in the form of an Excel spreadsheet or email. Dragging and dropping a few members is fine. But doing that for 10, 100, or thousands of items can be incredibly time consuming and prone to error. Fortunately Pyramid Analytics has an easy way to import in a set list of items. You can also take random sample of members, let’s say products.

Open the List Builder

The list builder function is available as a choice in the Custom MDX menu drop down from the Query Ribbon Tab. You will have two options: Cutting and pasting content from a source. For example, you may have members sent to you via email. As a best practice, I always recommend pasting the items in Notepad and copying the items for pasting to remove any unnecessary formatting. You can also import a delimited text file or .csv. Remember that the spellings must be EXACTLY the same to reference members correctly.

Figure 1. Import or Cut and Paste members

Note: Here you also can use the Generated List options to create a random sample list. This is great if you are asked for an average list of items: products, customers, or regions to analyze.

Separating the content

Depending on the cleanliness of your import you can select different delimiters to split the data. Only one column of data can be imported and used at one time.

 Figure 2. Separate the elements

Choosing the location

On the final part of the wizard you have three areas to complete.

The Cube Structure Target Users need to select the target of the import. The target indicates which dimension, hierarchy and level the list applies to. Users must also select which aspect of a member the list applies to by selecting a matching property.

Figure 3. Choose the location and name

The Type allows you to choose whether these members will be used to create a Custom Set or Member. If you choose Custom Member, you can choose the formulation (ie Aggregate).

Finally, you can provide the Meta Data for Name and whether it gets stored for Private access or Public access. The set and member created are static so if you need to add additional elements you will either need to go through the same process again with the new elements or edit the MDX directly.

And that is it! You can now use the set or member in any of your analyses. Consider using the List Builder when you have many elements to bundle together. It can save you a lot of time in the long run.