Reporting Server > Using the Adapter for Microsoft SQL Server Analysis Services Tabular Data Model > Managing Microsoft SQL Server Analysis Services Tabular Data Model Metadata

Managing Microsoft SQL Server Analysis Services Tabular Data Model Metadata

Topics:

When the server accesses a data source, it needs to know how to interpret the data stored there. For each data source the server will access, you create a synonym that describes the structure of the data source and the server mapping of the Microsoft SQL Server Analysis Services data types.

Creating Synonyms

Reference: Synonym Creation Parameters for Microsoft SQL Server Analysis Services Tabular Data Model

The following list describes the synonym creation parameters for which you can supply values.

Select the Database

Provides a drop-down list of models and perspectives available for synonym creation. The default selection is All Databases.

Filter by Object (Model or Perspective) Name

Selecting this option opens a text box in which you can enter a name or a pattern. Type a string for filtering the object names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select all objects whose names begin with the letters ABC; %ABC to select all whose names end with the letters ABC; %ABC% to select all whose names contain the letters ABC at the beginning, middle, or end.

Click Next to open the Available Objects for SSAS Tabular Model page. The following parameters are available:

Application

Select an application directory. The default value is ibisamp.

Prefix/Suffix

If you have tables with identical table names, assign a prefix or a suffix to distinguish them. For example, if you have identically named human resources and payroll tables, assign the prefix HR to distinguish the synonyms for the human resources tables. Note that the resulting synonym name cannot exceed 64 characters.

If all tables and views have unique names, leave the prefix and suffix fields blank.

Default Synonym Name

This column displays the name that will be assigned to each synonym. To assign a different name, replace the displayed value.

Database

Is the name of the database.

Object Name

Is the name of the underlying object.

Type

Is the type of object, either MODEL or PERSPECTIVE.

Select Objects

Select objects for which you wish to create synonyms:

  • To select all objects in the list, select the Select All check box.
  • To select specific objects, select the corresponding check boxes.

Click Create Base Synonyms on the ribbon.

Example: Sample Generated Synonym

An Adapter for Microsoft SQL Server Analysis Services Tabular Data Model synonym comprises a Master File and an Access File. This is a synonym for the perspective named WF Retail Tabular Cube.

Generated Master File wf_retail_tabular_cube.mas

FILENAME=, SUFFIX=TMDAX   , $
  SEGMENT=WRD_WF_RETAIL_SALES, SEGTYPE=S0, $
    FIELDNAME=SUM_OF_COGS_US, ALIAS='Sum of COGS_US', USAGE=D18.2C, ACTUAL=D8,
      TITLE='Sum of COGS_US',
      PROPERTY=MEASURE,  $
    FIELDNAME=ID_SALES, ALIAS=ID_SALES, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_SALES', $
    FIELDNAME=ID_STORE, ALIAS=ID_STORE, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_STORE', $
    FIELDNAME=ID_CURRENCY, ALIAS=ID_CURRENCY, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_CURRENCY', $
    FIELDNAME=ID_CUSTOMER, ALIAS=ID_CUSTOMER, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_CUSTOMER', $
    FIELDNAME=ID_DISCOUNT, ALIAS=ID_DISCOUNT, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_DISCOUNT', $
    FIELDNAME=ID_PRODUCT, ALIAS=ID_PRODUCT, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_PRODUCT', $
    FIELDNAME=ID_TIME, ALIAS=ID_TIME, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_TIME', $
    FIELDNAME=COGS_LOCAL, ALIAS=COGS_LOCAL, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='COGS_LOCAL', $
    
FIELDNAME=COGS_US, ALIAS=COGS_US, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='COGS_US', $
    FIELDNAME=DISCOUNT_LOCAL, ALIAS=DISCOUNT_LOCAL, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='DISCOUNT_LOCAL', $
    FIELDNAME=DISCOUNT_US, ALIAS=DISCOUNT_US, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='DISCOUNT_US', $
    FIELDNAME=GROSS_PROFIT_LOCAL, ALIAS=GROSS_PROFIT_LOCAL, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='GROSS_PROFIT_LOCAL', $
    FIELDNAME=GROSS_PROFIT_US, ALIAS=GROSS_PROFIT_US, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='GROSS_PROFIT_US', $
    FIELDNAME=MSRP_LOCAL, ALIAS=MSRP_LOCAL, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='MSRP_LOCAL', $
    FIELDNAME=MSRP_US, ALIAS=MSRP_US, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='MSRP_US', $
    FIELDNAME=QUANTITY_SOLD, ALIAS=QUANTITY_SOLD, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='QUANTITY_SOLD', $
    FIELDNAME=REVENUE_LOCAL, ALIAS=REVENUE_LOCAL, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='REVENUE_LOCAL', $
    FIELDNAME=REVENUE_US, ALIAS=REVENUE_US, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='REVENUE_US', $
  SEGMENT=WRD_WF_RETAIL_CUSTOMER, SEGTYPE=U, PARENT=WRD_WF_RETAIL_SALES,
    JOIN_WHERE=WRD_WF_RETAIL_SALES.ID_CUSTOMER EQ WRD_WF_RETAIL_CUSTOMER.ID_CUSTOMER;, $
    FIELDNAME=ID_CUSTOMER, ALIAS=ID_CUSTOMER, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_CUSTOMER', $
    FIELDNAME=ID_AGE, ALIAS=ID_AGE, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_AGE', $
    FIELDNAME=ID_EDUCATION, ALIAS=ID_EDUCATION, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_EDUCATION', $
    FIELDNAME=ID_GEOGRAPHY, ALIAS=ID_GEOGRAPHY, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_GEOGRAPHY', $
    FIELDNAME=ID_INCOME, ALIAS=ID_INCOME, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_INCOME', $
    FIELDNAME=ID_INDUSTRY, ALIAS=ID_INDUSTRY, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_INDUSTRY', $
    FIELDNAME=ID_MARITAL_STATUS, ALIAS=ID_MARITAL_STATUS, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_MARITAL_STATUS', $
    FIELDNAME=ID_OCCUPATION, ALIAS=ID_OCCUPATION, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_OCCUPATION', $
    FIELDNAME=ID_TIME_MIN, ALIAS=ID_TIME_MIN, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_TIME_MIN', $
    FIELDNAME=ID_TIME_MAX, ALIAS=ID_TIME_MAX, USAGE=P20, ACTUAL=P10,
      MISSING=ON,
      TITLE='ID_TIME_MAX', $
    FIELDNAME=EMAIL_ADDRESS, ALIAS=EMAIL_ADDRESS, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='EMAIL_ADDRESS', $
    FIELDNAME=FIRSTNAME, ALIAS=FIRSTNAME, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='FIRSTNAME', $
    FIELDNAME=FULLNAME, ALIAS=FULLNAME, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='FULLNAME',
      WITHIN=POSTAL_CODE, $
    FIELDNAME=GENDER, ALIAS=GENDER, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='GENDER', $
    FIELDNAME=LASTNAME, ALIAS=LASTNAME, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='LASTNAME', $
    FIELDNAME=INCOME, ALIAS=INCOME, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON,
      TITLE='INCOME', $
    FIELDNAME=POSTAL_CODE, ALIAS=POSTAL_CODE, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='POSTAL_CODE',
      WITHIN=CITY,
      GEOGRAPHIC_ROLE=POSTAL-CODE,  $
    FIELDNAME=CITY, ALIAS=CITY, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='CITY',
      WITHIN=STATE_PROVINCE,
      GEOGRAPHIC_ROLE=CITY,  $
    FIELDNAME=STATE_PROVINCE, ALIAS=STATE_PROVINCE, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='STATE_PROVINCE',
      WITHIN=COUNTRY,
      GEOGRAPHIC_ROLE=STATE,  $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='COUNTRY',
      WITHIN=CONTINENT,
      GEOGRAPHIC_ROLE=COUNTRY,  $
    FIELDNAME=CONTINENT, ALIAS=CONTINENT, USAGE=STRING, ACTUAL=STRING,
      MISSING=ON,
      TITLE='CONTINENT',
      WITHIN='*CUSTOMERS',
      GEOGRAPHIC_ROLE=CONTINENT,  $
 DIMENSION=CUSTOMERS, $
  HIERARCHY=CUSTOMERS, HRY_DIMENSION=CUSTOMERS, $

Generated Access File wf_retail_tabular_cube.acx

MODELNAME=WF Retail Tabular Cube, 
  CONNECTION=CON01, 
  CATALOG=WF Retail, $
 SEGNAME=WRD_WF_RETAIL_SALES, 
   TABLENAME='''WRD_WF_RETAIL_SALES''', $
  FIELD=SUM_OF_COGS_US, 
    MEASURE=YES, $
 SEGNAME=WRD_WF_RETAIL_CUSTOMER, 
   TABLENAME='''WRD_WF_RETAIL_CUSTOMER''', $

The following request reports against this synonym.

TABLE FILE wf_retail_tabular_cube
SUM COGS_US REVENUE_US
BY CONTINENT
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
END

The output is shown in the following image.

Reference: Managing Synonyms

Once you have created a synonym, you can right-click the synonym name in the Adapter navigation pane of either the Web Console or the Data Management Console to access the available options.

For a list of options, see Synonym Management Options.

WebFOCUS

Feedback