The main window of sas information map studio is used to view, create, and modify information maps and contains the following elements:
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.
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.
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. |
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 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.
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
The properties of each data item can be edited using its properties window, which can be accessed by
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.
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.
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.
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:
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
Vinod M is a Big data expert writer at Mindmajix and contributes in-depth articles on various Big Data Technologies. He also has experience in writing for Docker, Hadoop, Microservices, Commvault, and few BI tools. You can be in touch with him via LinkedIn and Twitter.