Greetings, fellow data analysts!
Ali Mitgutsch, an author and graphical artist based in Munich, is famous for his children’s books portraying highly detailed scenes. Kids can gaze in his books for hours and enjoy the many little stories without tiring from searching for all of the things prompted by one of the characters. This patience and pleasure we once had in searching often passes with time; plus, we have less and less time as well.
As a PC worker, you probably already spend a good chunk of your day searching for things. If someone asks you to analyze the numbers on a few customers, products, etc., you have to rummage through masses of data. And the more objects you are searching for, the harder it gets. The time starts ticking away – but you can’t exactly call that fun and you rarely learn much from it either. That is, unless, you count on your trusted companion DeltaMaster! DeltaMaster makes it easier to conduct a search by combing through entire hierarchies – not only in search of a single term but also several at the same time. As a result, you can process a comprehensive search list in a single batch. And you can create information-dense reports and analyses that can occupy your readers for hours with pleasure.
Your Bissantz & Company Team
What starts as a simple, harmless request can sometimes quickly snowball into a complicated, time-consuming project. Controllers, for example, often receive e-mails to the likes of ‘Could you run an analysis on a few customers for me? See attachment for customer numbers. Thanks!’ Yet oftentimes, a ‘few’ is turns out to be 100-200. The same can also happen for almost every imaginable report component in BI applications. You need to create a report for a group of objects that were defined by someone else, for example, the 76 new products that were just launched at a trade fair, the 220 customers who visited the booth there, the 47 real estate properties where you need to examine the operating costs or the 320 spare parts that you don’t need to keep on stock after this year. In cases like these, searching for these objects manually would normally be a time-consuming and error-prone task. That’s why we suggest that you use DeltaMaster instead!
DeltaMaster has an advanced search function in which you can enter multiple search terms at once, for example, using copy and paste. You can then display the elements that match these search terms in pivot tables, named sets, or user-defined hierarchies for further analysis.
We will use our well-known “Chair Corp.” reporting and analysis application to explain this advanced search function. Let’s assume that we wanted to select and analyze certain selected customers in a report that contain the following four terms: Center, Chair, Home, and Living.
Manually searching for the right members from a dimension (which might contain thousands of members) is not a feasible option. Even a normal search function as described in DeltaMaster clicks! 12/2006 quickly reaches its limits. After all, we are searching for multiple terms. We could, of course, perform four different searches one after the other and simply add the found results to the previous selection. The risk of resetting this type of selection by accident, however, is rather high. Plus, if we were searching for dozens or even hundreds of members instead of just four terms as in this case, a successive search would be too time-consuming and error-prone.
In cases like these, DeltaMaster’s advanced search functions come into play. These search the respective dimension for all entered terms at the same time. To use this feature, open the Find members function in the Dimension browser of the context menu or the I want to… menu (short cut: Ctrl+F) and Expand the dialog box.
The Find members dialog box now splits the view into two columns. In the left column you can enter the members that you want to find – one in each row. Using the context menu or the Ctrl + V short cut, you can insert members from your clipboard. This makes it easy to copy and paste search terms from an Excel spreadsheet or a text file.
To start your search, simply click on Find next.
During a search, DeltaMaster takes the options that are located in the bottom part of the window into account. Here, you can determine which hierarchy levels it should search, how it should compare the search terms (i.e. the entire name or the beginning, end or a part of the name) and if it should Match [the] case.
If the respective dimension has multiple hierarchies or aliases, DeltaMaster will conduct the search on the hierarchy or alias that was selected in the Dimension browser prior to opening the search.
DeltaMaster will then list the Found members in the upper right-hand side of the window. Below these columns you can also see the total number of search terms, found search terms and found members. If DeltaMaster could not find a search term, it will then cross out this term in the search list so that you can quickly see where something went wrong.
Selecting found members
DeltaMaster does not automatically select the found members. That means that they have not been taken over into the view yet and have no effect on the displayed reports, cockpits and analyses for the time being. But you can either select a portion of the found members or work with the entire hit list:
In most cases, you would Select the members by clicking the respective button in the search dialog field. The three options describe how the search results will affect the selection that was displayed in the Dimension browser prior to the search:
- If you choose the first option Replacewith found members, DeltaMaster will select all of the members in the hit list and remove all of the members that were previously selected.
- If you choose the section option Add found members, DeltaMaster will keep the previous selection and simply include the members from the hit list as well. You can use this option to add a member selection successively.
- If you choose the third option, DeltaMaster will Remove all found members from the current selection in case they were selected previously.
- When you click on an entry in the hit list, DeltaMaster will display the respective member in the Dimension browser. This remains opened in the background where you can use it at any time. This way you can locate and select individual members from the hit list. (To make a multiple selection, simply hold the Ctrl key while clicking the desired members.)
No matter how you choose the found members, DeltaMaster will deliver a select set of members in the Dimension browser. This is a static member list based on the data model at the time of the search. If you later add new members that would fit the search criteria to your model, DeltaMaster will not automatically select them.
Working with search results
You can work with a member selection that was created using a multiple search just like with any other view. In most cases, you will probably use one of the following four options:
a) Selection in the view window
When you open the Dimension browser in the View window, DeltaMaster will take over the selection into the current view. This affects the current cockpit, the current report or the current analysis in all user levels where you can change the view (i.e. from Viewer to Miner). When you save the cockpit, report or the analysis as a report in the Briefing book, DeltaMaster will include the view as always. This applies to the user levels in which you can save reports (i.e. Pivotizer, Analyzer and Miner).
In Miner mode you also have the option to save the view to your Favorites (Start view menu in the View window). In the History window you can assign a User-defined title for that view (context menu of a view, View properties) to replace the long list of selected members with a more self-explanatory description.
The selection function in the View window is very easy to use and also works in Viewer. However, you do not have many different options to reuse the search results.
b) Selection in the axis definition of a pivot table
In the Pivotizer, Analyzer and Miner modes, you can also create a Member selection in the Axis definition of pivot tables (see DeltaMaster clicks! 4/2009). In this case, simply open the Dimension browser from the Member selection in the Axis definition instead of from the View window.
Scenarios a) and b) are particularly useful when you primarily want to create a list of the individual members and their respective measures, for example, revenues for individual case groups, customers or products. If you want to create an aggregated view of the members as well, you should use c) and d) as described below instead.
c) Selection in a named set
This scenario gives you more flexible options to reuse your search results. If you save your search results as a named set in the Pivotizer, Analyzer, or Miner level, you can access them in other pivot tables as well as in calculations with user-defined MDX expressions, iterations for creating Small multiples, or even ReportServer.
To reuse your search result as a named set, simply save the selection in the Axis definition of pivot tables from the I want to… menu.
If you want to save your search results without visualizing them in a pivot table beforehand, use the Named set browser (Model menu) instead. You can also Add [a] named set here (context menu, I want to… menu) by creating a Member selection in the Editor for named sets and filling it with the advanced search results. For more information on named sets, please read DeltaMaster clicks! 5/2009.
d) Selection in a user-defined hierarchy
In Miner mode, you can create a User-defined hierarchy (I want to… menu in the Dimension browser) based on your Member selection.
The user-defined hierarchy is even more flexible than a named set especially during analysis. For starters, DeltaMaster generates an aggregated member so that you can further analyze the sum as an independent member in sparklines, with the pivot navigation, or in one of the many built-in analytical methods. Beyond that, you can use the new hierarchy (just as you can use the other ones as well) in multiple analysis methods, for example, in Rankings, the ABC and Portfolio analysis, PowerSearch and Geo Analysis.
If all you need is the simple proof for a sum or an average without having to expand this value as an independent measure, you can do that with a named set and simply show the desired Row, Column or Table aggregations.
Searches are no replacement for systematic thinking
The procedures described above are designed to help you create an ad hoc analysis for a set of members that were defined outside of your DeltaMaster applications. Please note, however, that the more terms you want to find and the more members that are in the dimension which you are searching has, the longer the search will take. Selecting a large number of hits in the Dimension browser and calculating reports containing this selection can take a considerable amount of time. If you regularly want to access a large group of members in a dimension, you might want to consider reorganizing your model and meeting these requirements through a new hierarchy in your database or with the help of member properties.
Just contact your Bissantz team for more information.