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 BarContains 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
ToolbarContains buttons that enable quick access to many of the functions that are available from the menu bar.
Repository TreeDisplays 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 TabDisplays 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 TabContains 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 TabDisplays 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

Map Studio Interface

sub string

launch activity

script language

Relationships Tab

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

MindMajix Youtube Channel

Joining Tables

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

Joining Tables

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.

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

Information Map Data Items

PropertyDescription
Data item namespecifies a descriptive name for the data item; the maximum length is 60 characters
Descriptionspecifies a description for the data item, which can be viewed by the information map consumer.
Classificationspecifies 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.
Aggregationspecifies aggregate functions that the information map consumer can use for summarizing measure values.
Formatspecifies 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.
Permissionsspecify 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 GenerationValue 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.

SAS Information Map Studio

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.

meta data profile

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

login window

Information Map Studio is initialized and the interface is displayed:

map studio connection interface

map studion connection interface 2

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 insert data source to add data.

New data source wizard

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.

insert data source 2

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

physical data tree

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

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.

customer age data

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).

data from properties

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 ok 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.

information map

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 test query 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.

data selection query

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

results window

10. Select show query to view the SQL statements:

show generated query

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 close 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).

results

11. Select close button to close the Results window.
12. Select cancel button 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.

file destination

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 save button to save the information map and return to the main window.
The newly saved information map shows up in the Repository Tree.

repository

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 categorical filter to add it to the Information Map tree.

creating filters

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 OK

date item properties

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

information map2

16. Select insert filler button 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.

insert filler

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

insert filler main window

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 add button to add the expression to the Filter combinations area.

sas information map studio

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

information map 2

18. To test the filter, select test query 1
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.

data selection 1

e. Select run test button 1

final results

Related Article: SAS BI Interview Questions and Answers

f. Select close button to close the Results window.
g. Select cancel  button 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 insert data item to define specifics for this new data item:
a. Specify Customer Birthday Month as the Data  item  name.

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 edit button 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 add to expression button

expression editor

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

Expression editor 1

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 validate expression button to ensure the syntax is correct.  A message window displays:

valid expression test

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.

Data item properties

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.

data selection list

4) Select RUN TEST

output values

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 ICON 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.

insert data source

Select OK

data settings

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.

data item properties 3

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

classification format

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.

BIP Tree report

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.

relationship properties

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.

Query options for test

d. Select RUN TEST

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

About Author

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.

read less
  1. Share:
SAS BI Articles