When you build a table or visualization in Planning Analytics, you are usually focusing on a particular part of your business –specific products, specific accounts, specific geographies. You do that by picking the members from each of the dimensions you are interested in. One of the choices you have when you do so is whether to define the members dynamically or pick a static list. Your choice depends on what you want to do with that list.
Do you want a specific set of members that will never change? That’s easy, you want a static set.
Do you want the list of members to update automatically as new members are added and deleted? You want a dynamic set. There are several ways of constructing a dynamic set. You may even be creating them without knowing it. Let’s look at an example.
Our company sells mobile phones. We want a report that shows us all the 4G phones. We could do it as a simple static set, picking the phones individually:
From the overview bar a static set will have the following icon:
Static sets look the same in the Dynamic view and Hierarchy view of the Set Editor. If you look at the MDX, you’ll see each member is listed.
Dynamic Sets based on Hierarchical Selections
That’s fine if we’re only ever going to sell those same phones. More than likely our product line will change over time, so instead, we would probably add the parent member “4G Smart Phones” dropping it from the tree or adding it in the set editor with the insert with children. In the Hierarchy View it looks like the same list, but if you switch to the Dynamic or MDX view, you’ll see that the CHILDREN key word.
In the Overview Bar a dynamic set has a filter icon on the tile.
If you only want to see the children, you can use the Remove option on the Context menu either in the Set Editor or the Visualization itself. Your dynamic expression will then include an EXCEPT.
Other Dynamic Insertions
Other dynamic sets based on hierarchical insertions include insert with D
escendants, which includes everything below the selected member,
Leaf Nodes which inserts the lowest level of the hierarchy and
Ancestors, which inserts all the members above the selected member.
Dynamic Sets based on Searches
What if the members you want are not neatly organized under a single parent? You can always use multiple dynamic insertions to insert the children or other levels, or if the members you want can be identified by a part of their name or an attribute, you can also use the search to build your dynamic set. In the example below we searched for products that contained °32GB° in their name, and added the search rather than the results to the set. This shows up in the MDX as a FILTER expression.
All of these dynamic sets will be update as new phones are added. You can even sort a dynamic set, but dynamic sets do have one important limitation. Dynamic sets do not allow you to control the order of members.
Converting to Static - Taking a Snapshot
If you try to reorder members of a dynamic set in the set editor you’ll notice that the Move options are greyed out. If you need to present members in a specific order other than the initial result, you’ll have to take a snapshot of the set to make it static. This is true also for calculated members. In the example below the 16GB Contribution calculation has been added to show the percentage of 4G phones that are 16GB. By default this calculation has been inserted after the last member in the calculation. If you wanted to move it to you would have to convert it to static.
You could Sort the list. In this case, this coincidentally moves the calculation to the end (and provides a workaround for calculations that can be renamed). But if you want a very specific order the static snapshot is your best option
In most cases dynamic sets will suit your analysis needs better. They allow your report to react to changes in the cube. If you don’t want your view’s rows and columns updated to reflect changes, then opt for static.
You can mix Dynamic and Static
Remember too that it’s not all or nothing. You can mix dynamic and static definitions in the same set.
#administration#administrator#analyst#book#consumer#createBoard#dashboard#dynamic#ExpertPost#exploration#GettingStarted#modeler#modeling#pahome#pa-home#PlanningAnalyticswithWatson#planninganalyticstrial#PlanningAnalyticsWorkspace