When defining an attribute form, we're not limited to column names: as said, we can use constants or choose from a huge list of functions ready to use. These functions are meant for the most common uses: arithmetic, date manipulation, string functions to name a few. Let's see an example with the Customer attribute.
We are going to use the DimCustomer table for the next recipe. This table has a primary key (CustomerKey) and two description fields (FirstName and LastName).
Follow these steps to create a new attribute:
- As in the previous recipe, go to the Attributes folder and right-click on the right pane.
- Select New | Attribute from the context menu.
- Create the ID form with the CustomerKey column of the DimCustomer table, set Mapping method to Manual, and click on OK.
- In the Create New Attribute Form window, check only DimCustomer, which is bold, and click on OK.
- In the Attribute Editor, click on the New button and you're again into the Create New Form Expression window.
- This time, click on the Insert Function button which has a small f(x) label to bring up the Select Function dialog.
- Open the Select a category combobox and choose String and then click on ConcatBlank in the Select a function list. And now click on Next.
- In the Arguments dialog, type
FirstNamein String1 andLastNamein String2, and then click on Finish. The Form expression text area looks like:ConcatBlank(FirstName, LastName). - Set Mapping method to Manual and click on OK.
- In Source tables, check DimCustomer and click on OK and then on Save and Close, give it the name of
Customerwhen prompted. - Remember to update the schema (press Ctrl + U).
In MicroStrategy Suite, you will find out-of-the-box functions for common transformations; these functions are later translated to their corresponding SQL syntax when running the queries. Different RDBMS may have different way of doing the same function (string concatenation in Oracle is done with the || operator, while SQL Server uses CONCAT or the + operator, and so on). The SQL engine generates the correct syntax according to the dialect of the DBMS type, set in the database connection when creating the database instance configuration (see Chapter 1, Getting Started with MicroStrategy).
Mapping method can be automatic or manual. MicroStrategy uses the column name to detect when the same field appears in different tables and automatically uses as source all the tables where that column appears. If the column naming convention is enforced and consistent, this is a very useful and time saving feature. In other cases, when several columns of different tables have the same name but different meaning, the automatic discovery can lead to suboptimal SQL. This is why I always use the manual mapping method, especially in the beginning phase of a project, when I want to force the generated SQL to use a specific table.
If you go to the Data Explorer | System Hierarchy folder and press F5, you will find the two attributes. You can browse customer names by double-clicking on the yellow icon. The ConcatBlank function inserted a space between the two string arguments that we set earlier.