Greetings, fellow data analysts!
A member property is a typical example of an abstract database term that has a very practical use in applications. In DeltaMaster, member properties come to life as images in reports, weight or size information for products, population numbers of regions, cost center names (in place of numbers), the way you address report recipients, and in many other situations in which you describe report objects (members) in more detail. DeltaMaster offers vast support for member properties – so much so that we will break down this topic into three separate issues of clicks!. This first part will show how member properties can enhance reports and give practical tips how your applications can profit from them.
Your Bissantz & Company team
The analysis models of DeltaMaster use measures and members to describe your business world. To describe these members, you can also use attributes called member properties, which are created and maintained in the underlying database. As you can see in the following examples, you can use member properties in many different ways:
Common usage scenarios for member properties include:
- Additional information for individual objects: You can enhance customer data, for example, by including the address, name of the respective purchasing manager, the homepage, and the path to an image file of the logo and use them in reports.
- Classifying attributes: You can, for example, classify customers by creating industry keys, different statuses, or other properties that define a group. DeltaMaster can then group them in virtual hierarchies that extend the analysis model with new structures without having to change the database.
- Alias: You can, for example, create short and long names for accounts or use the name, number, or a combination of both. Aliases are also common in the time dimension to label reports in different date formats (e.g. “Nov 2015”, “2015-11”, “Nov”, “N”, or “11”). For more tips on using concise labels, either with or without member properties, please read DeltaMaster clicks! 08/2014.
- Multilanguage applications: This role is very similar to aliases, yet there are differences in handling. With aliases, you want to use either this name or that one for an individual dimension or level – in a single report. You configure language, however, as a one-time setting for the entire application. The alias sets of DeltaMaster make this possible – and much more. See DeltaMaster clicks! 04/2007 for more information.
- Technical information: For example, you could create for customers a master directory for exported documents on the file server or add the email address of the respective account manager. DeltaMaster is able to include this information in its processing rules, for example, when running jobs in Report Server. Technical information also includes attributes that have a certain function in the analytic methods of DeltaMaster. Examples include: latitude and longitude coordinates in a Location Analysis, reference elements in a Geo Analysis, or the so-called BI factor in account dimensions.
- Modeling of multidimensional databases: You can use member properties to map complex relationships in a KPI scheme (by means of unary operators and user-defined aggregation rules). These types of topics, however, are not covered in clicks!.
An important trait of member properties is that they count as master data. Their values rarely change, if at all, over time.
Some member properties only make sense on a certain dimension level while others can be used on all levels. For example, you could have one homepage or one sales director for an individual customer but not for the customer’s region or country, which many others share as well. In the case of accounts, however, you could easily use translations as well as long or short names on any level.
The database you use determines if the properties apply to a specific level or all levels:
- Member properties are defined separately for each level in all regular, level-based hierarchies of Microsoft Analysis Services and all relational models.
- Member properties are defined for all levels in IBM Cognos TM1, Infor BI OLAP, Oracle Essbase, SAP BW as well as parent-child hierarchies in Microsoft Analysis Services.
DeltaMaster can work with both variations and offers special options to improve the way you work with level-specific properties.
So much for theory – let’s take a look at a real example. Where do you find member properties in DeltaMaster? This edition of clicks! will start with general settings and Pivot Tables while the next one will focus on analytic methods, modeling, and ReportServer.
Showing member properties as an alias
One central setting is located in the Dimension Browser: You can set an Alias for each level in Pivotizer, Analyzer, and Miner modes as well as in the Model mode of DeltaMaster 6. The selection list contains all available member properties. If you select a member property, DeltaMaster will display its values as alias in place of the member name throughout the entire application – in other words, in all analyses, reports, and dialog boxes. DeltaMaster also uses aliases in place of member names in the search function, for example, in the View window when you hold the Shift key while clicking on a member name.
Multilanguage applications through member properties in alias sets
DeltaMaster also uses member properties to administer several languages simultaneously in one application. This initially works the same way as aliases (see description above): For each language, there is a field allotted in the database to store the translation of the member name. If you want to use an application in a different language, you can combine the desired aliases in an alias set instead having to select them individually for all levels. This way, you only have to define which member properties should be used as aliases e.g. in the alias set “German” one time. You can then select the desired set to display the respective aliases in all dimensions and levels at once.
Using alias sets, you can also administer other namable components of a DeltaMaster application in several languages. These include the names of measures, measure groups, dimensions, dimension groups, hierarchies, levels, report folders, and reports. DeltaMaster uses a type of translation table for these objects so that the translated names can be directly maintained in DeltaMaster. Dimension members have a special role. Since dimension members are usually very large in number, they are not part of alias sets; instead, you create a reference to the translations in the database. For more information on multilanguage applications, please read DeltaMaster clicks! 04/2007.
Display in Pivot Tables
You can use member properties in many different ways in Pivot Tables. The Axis Definition contains a separate tab devoted to them. This tab displays a list of all member properties that are available in the respective dimension. In the case of level-based hierarchies as shown in the screenshot on your right, both the level and the member property are listed. Otherwise, you will only see the property. From this list, you select which properties should be displayed in the table. You can change the display order using drag and drop. Alternatively, you can display the member properties directly in the report through the context menu of the axis.
You can define the type of View individually for each member property.
- As column or As row: DeltaMaster displays the selected member properties in fixed columns or fixed rows in the table, behind or below the member name. This adds one or more additional fields to the column or row heading. These fields behave the same way as member names. For example, they use the same settings for the column width (Table Properties, General tab) and you can search for them using the context menu. This most commonly used option is also the default setting.
The member properties in the example above are numerical. To show that these numbers are captions and not part of the analysis data, DeltaMaster displays them in black type.
- As tooltip: DeltaMaster only displays member properties when you mouse over the member name. This option is very useful when you don’t need the properties as orientation in the table, but you would like the option to access additional information here and there. By displaying member properties as a tooltip, you can integrate more information in the table without needing more space. When you export the Pivot Table to HTML format (e.g. as an email or with ReportServer as a file), the tooltips remain intact. They are not supported in other export formats.
- The table above shows the same properties from the past screenshot as a space-saving tooltip.
- As caption: DeltaMaster replaces the name of the member as if you had selected an alias in the Dimension Browser but only for the respective Pivot Table. If both are activated, the display as caption supersedes the alias from the Dimension Browser.
The caption is the first entry in fixed columns or rows. You can also use this option to change the display order so that other properties are placed at the front or up top instead of the name. DeltaMaster can only display one property as a caption on each hierarchy level.
The screenshot above uses the German names as a caption for the main products while the rest of the table is in English.
- As column/row and as image:DeltaMaster interprets the member properties as the addresses of image files (URL or path/file name), loads these images (bitmaps), and displays them as additional columns or rows in the table. That creates highly vivid presentations, especially when you can easily differentiate the report components and they can be well displayed as a photograph, which is the case with physical products. For more information, please read DeltaMaster deltas! 5.5.7, feature #10.
- As caption and as image: DeltaMaster once again interprets the values as addresses of image files but places them at the first position in the column or row in place of the member names. With this option, you can design a table with just images and no text.
Options for level-specific member properties
As mentioned above, member properties are defined for each level in certain databases. This can make designing data-dense reports a challenge – especially when you need to show several levels in a report. Each member property occupies a separate column but only returns a value for the members of a specific level. This creates gaps in the caption columns.
With DeltaMaster, you can counter this effect using two options in the Axis Definition. These options are located on the right side of the member property list.
- You may want to merge related member properties when working with aliases and languages as in the example above. This option ensures that DeltaMaster outputs values of different members in one and the same column and “fills the holes” so to speak. The table needs less space because related properties all use one column instead of being distributed across their own columns. Which properties are related depends on their names. To combine them, select the desired property for one level. DeltaMaster will automatically determine which member properties are related to it. For more information, please read DeltaMaster deltas! 5.5.5, feature #15.
- You can also close the gaps in captions by repeating the member properties of higher levels on lower levels. This does not make the table smaller, but fills it. This option is helpful if you are working with objects on lower levels and want to show their higher levels in context (without adding them as an aggregated member in the Pivot Table) or you want to “pass on” properties to lower levels and display them there. For more information, please read DeltaMaster deltas! 5.5.5, feature #12.
You can use these options separately or in combination.
Filter and sort options in Pivot Tables
You can use member properties to refine the selection of the members displayed in the Pivot Table. In the Axis Definition on the Filter tab, you can define rules which member should be included in the axis. Since member properties are often the data type “text”, DeltaMaster offers the comparative operators “begins with”, “ends with”, “contains” as well as the respective negations. For more information, please read DeltaMaster deltas! 5.3.3, feature #16.
To sort the table by a member property, select it from the Axis Definition on the Ranking tab. Here it does not matter if you can see the property in the Pivot Table or not.
Member properties in analytic methods
DeltaMaster also offers member properties in three analytic methods: Ranking (see screenshot below), Cross Table Analysis, and PowerSearch (provided that you are only analyzing one level).
Once the analysis is calculated, you can show member properties by activating the respective entries in the context menu.