Using SAS Information Map Studio - SAS BI

SAS Information Map Studio Interface

The main window of sas information map studio is used to view, create, and modify information maps and contains the following elements:

• Menu Bar
• Toolbar
• Repository Tree
• Presentation Tab
• Properties Tab
• Relationships Tab.

Menu Bar	Contains the drop-down menus for SAS Information Map Studio. Many of the menu functions are also available from the toolbar and from pop-up menus
Toolbar	Contains buttons that enable quick access to many of the functions that are available from the menu bar.
Repository Tree	Displays the information maps in the metadata repository. You can display or hide the Repository Tree by clicking the View Repository button on the toolbar.
Presentation Tab	Displays the physical data source, data items, and filters for the information map that is currently open. The Physical Data tree displays the physical data source that you selected for the map. The Information Map tree displays the information map’s data items and filters.
Properties Tab	Contains the properties table for the information map’s data items and filters. You can select a data item or filter in the left pane to display its properties in the right pane. From the right pane, you can edit the properties.
Relationships Tab	Displays tables, their columns, and the relationships (joins) between tables. You can create and modify the relationships between tables. You can also add or remove tables for your information map

Information maps can be created from one or more data tables or a single OLAP cube.

Joining Tables

When joining tables, the default relationship is an inner join, which returns only matching rows.

Outer joins can also be used to return non-matching rows.
The modifiers left, full, and right determine which table contributes the non-matching rows.

Information Map Data Items

• An information map is made up of data items. Each data item has metadata to specify its properties, such as
• data item name
• description
• classification
• aggregation
• format
• permissions
• value generation method

Property	Description
Data item name	specifies a descriptive name for the data item; the maximum length is 60 characters
Description	specifies a description for the data item, which can be viewed by the information map consumer.
Classification	specifies whether the data item is a category or a measure. The classification of a data item determines how the data item will be processed in a query.
Aggregation	specifies aggregate functions that the information map consumer can use for summarizing measure values.
Format	specifies a format for the data item. A description of the format and an example of a formatted value are displayed to the right of format type and name.
Permissions	specify or deny permissions (rank or sort) for the data item.Permissions are applicable only to relational data items and OLAP data items that represent hierarchies. the method that is used to generate a list of data item values for a filter.When this data item is selected for a filter, the method that you choose is used to populate a selection list that the user of a filter definition window can select values from.
Value Generation	Value generation options apply only to relational data items that do not have an expression type of Date, Time, or Timestamp.

Information Maps – Filters

A filter is an information map item that contains criteria to enable the information map consumer to subset a result set.
Having filters stored in an information map can be useful when the filter is complex and difficult for an end user to build without advance knowledge of data values.

Information Maps – New Data Items

>>Information map data items can refer to a physical data field or a calculation.
>>When you create a new data item, the underlying data sources are not changed. You are creating a field that is created dynamically every time the information map is accessed. You are not physically saving the result in the data. It is only a metadata instruction on how to build the field.
>>This can be an important distinction for performance reasons. If you have a data item that is complicated to calculate and the map is tied to large data sources, it may be faster and more efficient if the data item is created in the data warehouse or physical data source. If storage space is important, it may be better to calculate the field in the information map because the value is not stored physically.

Using the SAS Information Map Studio

This demonstration shows how to use SAS Information Map Studio to build a simple information map.

1. Invoke Information Map Studio by selecting Start Ð All Programs Ð SAS ÐSAS Information Map Studio 1.0.1.
2. Choose the metadata profile BI Architecture.

3. Log in with the username and password provided by your instructor.

Information Map Studio is initialized and the interface is displayed:

Information Map Studio opens without an information map loaded. To create a new information map, start by inserting a new data source.
You can also access a wizard to create a new information map by selecting File Ð New using Wizard from the pull-down menus.
To load an exiting information map, select it from the Repository Tree.
4. Select  to add data.

The Data Type selection allows you to select either tables or cubes. You can build an information map from one or more table on the same server, or one cube.
1. Expand Orion Star Gold Library and select CUSTOMER_DIM.

The Physical Data Previewer displays the columns in the selected data source.
Select 
The selected data source is displayed in the Physical Data tree.

Numeric fields have a blue circle icon ( ) and character fields use a red triangle ().
Begin creating the information map by selecting fields in the Physical Data tree and moving them to the Information Map tree.
6. Select to select all the fields from the CUSTOMER_DIM table.

Data items in the information map are initially created

• using the column label as the display name
• with a default classification of CATEGORY

The properties of each data item can be edited using its properties window, which can be accessed by

• double-clicking on the field name
• right-clicking on the field and selecting Properties
• selecting the Properties tab.

7. Right-click on the Customer Age data item and select Properties from the pop-up menu.

a. Select the Classifications/Formats tab.
b. Change the Classification to Measure which activates the Aggregations selections.
c. Change the Default aggregation to AVG(argument).

It is important to choose the proper classification for each data item in the information map because this setting cannot be changed in the applications that use the information map.
Select  to close the properties window for Customer Age.
The icon for Customer  Age changes to something that looks like a ruler ) – this indicates it is a measure.

Testing the Information Map

The Test Query button on the toolbar opens a window that allows you to run a basic query against the information map to make sure that it works as desired.
8. Select  on the toolbar.
         a. Select the following: Customer   Age   Group 

                                      Customer Gender

                                      Customer Age
         b. Select Display detailed list of all values.
         c. Select Limit the query result to and accept the default value of 100.

Select .
9. If the query is successful, a results window is displayed:

10. Select  to view the SQL statements:

You cannot edit the SQL code shown in the Show Generated Query window, but you can copy the code into another application and modify it there.
Select  when done viewing the code.
If Group  by  category values is selected instead of Display detailed list of values, the query results will be grouped by the various category values (Customer Gender and Customer Age), and the measures will use the default aggregation (average Customer Age in this example).

11. Select  to close the Results window.
12. Select  to exit the Data Selection and Query Options for Test window.
13. Select File Ð Save to save the information map. The Save As window opens:
a. Select the location to save the map by selecting BIP Tree Ð ReportStudio Ð Maps.
b. Enter Customer List in the Name field.
c. Enter a Description of Orion Star Gold Customer Listing.

If this map is intended for use in SAS Web Report Studio, you must  save your information maps in the Foundation/BIP Tree/ReportStudio/Maps folder.
Select  to save the information map and return to the main window.
The newly saved information map shows up in the Repository Tree.

Creating Filters

Create a filter to subset data by age and gender. Because Customer Age is already defined in the information map as a measure, it needs to be added again so it can be used to create a categorical filter.
14. Select Customer Age in the Physical Data tree then select  to add it to the Information Map tree.

Duplicate data items are automatically renamed.
15. Double-click Customer Age1 to open the Data Item Properties window. Change the Data item name to Age Category and select 

The new data item is displayed in the Information Map tree.

16. Select  to define specifics for this new filter:
a. Specify a Filter Name of Young Males.
b. Specify a Description of Men age 21 and under.
c. Select the field Age Category from the drop-down list for the Subset field.
d. Select the Is less than or equal to item from the drop-down list for the Condition field.
e. Specify a Value of 21.

f. Since this filter is based on multiple criteria, select  to display the filter combination’s user interface.
Select  to move the expression just built into the Filter combinations box.

The expression built on Age Category is cleared from the Subset list at the top of the window and should now appear in the Filter Combinations area.
g. Create the second portion of the filter by selecting Customer Gender from the drop-down list for the Subset field.
h. Select Is equal to from the drop-down list for the Condition field.
i. Specify a Value of ‘M’.
Character values used in filters are case sensitive and must be enclosed in quotes.
j. Select  to add the expression to the Filter combinations area.

k. Select  to save the filter.
17. The filter now appears as an item in the information map:

18. To test the filter, select 
a. Select Customer Gender, Customer Birth Date, and Customer Age.
b. Select Young Males.
c. Select Display a detailed list of values.
d. Select Limit the query result to 100 rows.

e. Select 

Related Article: SAS BI Interview Questions and Answers

f. Select  to close the Results window.
g. Select  to exit the Data Selection and Query Options for Test window.

Creating New Data Items

Create a new data item for the customer’s birthday month. The value for this data item will be derived using the customer’s birthday.
19. Select  to define specifics for this new data item:
a. Specify Customer Birthday Month as the Data  item  name.

When a new data item is created, you must define the expression before you can enter any properties for that data item (other than the name).
b. Select  to open the Expression Editor.
c. Specify Month the  customer  was  born as a Description.
d. On the Functions tab, locate the function category Date and Time.
e. Scroll to Month(date) and select 

f. Select the Data Sources tab, expand Physical Data and CUSTOMER_DIM, locate Customer Birth Date from the Customer List information map and select add to expression

Both Business Data and Physical Data are available so that you can create an expression for a new data item based on physical data that may not be in your information map.
g. Select  to ensure the syntax is correct.  A message window displays:

Select  OK
h. Select OK to close the Expression Editor window and return to the properties window for the new data item.
i. Select the Classifications/Formats tab and change the format type from Date/Time to Numeric, specify BEST as the format, and set the width to 2.

j. Select OK to close the properties window for the data item.
k. Test the new data item by selecting TEST QUERY
1) Select Customer First Name, Customer Birth Date, and Customer Birthday Month.
2) Select Display a detailed list of values.
3)Select Limit the query to 100 rows.

4) Select RUN TEST

5) Select CLOSE to close the Results window.
6) Select CANCEL to exit the Data Selection and Query Options for Test window.
20.Save the information map by selecting File Ð Save (or by selecting  on the toolbar).

Adding an Additional Data Source to the Information Map

21. Select INSERT DATA SOURCE
22. Expand the Orion Star Gold Library and select ORDER_FACT.

Select OK

23. Add the following fields to the information map:

• Date Order was Placed by Customer
• Total Retail  Price  for  This  Product
• Cost Price  Per  Unit.

24. Right-click on Total Retail  Price  for  This  Product and set the Classification to Measure.

25. Right-click on Cost Price  Per  Unit and set the Classification to Measure.

26. Select the Relationships tab to define how the tables will be joined together.
Select Customer ID from the CUSTOMER_DIM table, drag and drop on top of Customer ID in the ORDER_FACT table.  By default, this creates an inner join on the two columns.

If your information map contains more than one table, you must define relationships for the tables.
This tab is disabled if your information map is based on an OLAP data source.
By default, this creates an inner join on the two columns.To change the type of relationship, right- click on the line connecting the tables and select Properties from the pop-up menu.

27. Select TEST QUERY to test the join.
a. Select Customer Name, Date Order was Placed by Customer, and Total Retail Price for This Product.
b. Select Display detailed list of all values.
c. Select Limit the query result to and keep the default of 100.

d. Select RUN TEST

e. Select CLOSE to close the Results window.
f. Select CANCEL to close the Data Selection and Query Options for Test window.
28. Select File Ð Save As to save the information map with a new name.
     a. Specify Customer Orders for the Name.
     b. Retain the folder specification of BIP Tree/ReportStudio/Maps/.
     c. Specify Orion Star  Gold  Customer  Order  Information as the Description.

=

29. Select SAVE