Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 17, 2009

Attribute Discretization: Customize Grouping Names - Page 4

By William Pearson

Procedure: Employ the Naming Template to Support Custom Attribute Group Naming for Attribute Members in Analysis Services

The client representatives with whom we are working state that they are pleased with the overall results, but then they make one further request: they tell us that they would prefer that each of the “buckets” we have created to group the Vacation Hours be assigned a more explicit label. They tell us that they would rather that the label be “beefed up” to include more explanatory verbiage – in accordance the following example:

Instead of:

“0 - 8”

they would rather see:

“8 & Under”

and, for all members of the “intermediate ranges,” instead of, say,

“9 - 19”

They would rather see:

“Between 9 & 19 hours (incl)”.

Moreover, instead of

“90 - 99”

they would rather see:

“90 & Over”

We inform our client colleagues that Analysis Services generates member group names automatically when it creates the groups, based upon a default naming template, which is used unless we specify another naming template within the Format setting for the NameColumn property of an attribute, as we shall see. A separate naming template can be defined for each language specified in the Translations collection of the respective column underlying the NameColumn property of the attribute.

The Format setting uses the following string expression to define the naming template:

<Naming template> ::= <First definition> [;<Intermediate definition>;<Last definition>]

<First definition> ::= <Name expression>

<Intermediate defintion> ::= <Name expression>

<Last definition> ::= <Name expression>

The <First definition> parameter applies only to the first (or only, if there is only one) member group generated by the discretization method. If the optional parameters <Intermediate definition> and <Last definition> are not provided, the <First definition> parameter is used for all member groups generated for that attribute.

The <Last definition> parameter applies only to the last member group generated by the discretization method. The <Intermediate bucket name> parameter applies to every member group other than the first or last member group generated by the discretization method. (If two or fewer member groups are generated, this parameter is ignored.) The <Bucket name> parameter is a string expression that can incorporate a set of variables to represent member or member group information as part of the name of the member group, as presented in Table 1 below:

Variable

Description

%{First bucket member}

The member name of the first member to be included in the current member group.

%{Last bucket member}

The member name of the last member to be included in the current member group.

%{Previous bucket last member}

The member name of the last member assigned to the previous member group.

%{Next bucket first member}

The member name of the first member to be assigned to the next member group.

%{Bucket Min}

The minimum value of the members to be assigned to the current member group.

%{Bucket Max}

The maximum value of the members to be assigned to the current member group.

%{Previous Bucket Max}

The maximum value of the members to be assigned to the previous member group.

%{Next Bucket Min}

The minimum value of the members to be assigned to the next member group.


Table 1: Set of Variables Incorporated by the <Bucket Name> Parameter

Use the Naming Template to Support Custom Attribute Group Naming

Let’s put what we have learned to work to help our client colleagues reach their objectives in providing more descriptive names for Employee Vacation Hours.

1.  Click the Employee.dim tab to return to the Dimension Designer for the Employee dimension.

2.  Click the Dimension Structure tab, if necessary, within the Dimension Designer to expose, once again, the Attributes pane, as we did earlier.

3.  Click the Vacation Hours attribute, once again.

4.  In the Properties pane, scroll to the Source section, focusing upon the KeyColumns and NameColumn settings, as shown in Illustration 19.

KeyColumns and NameColumn Settings for the Vacation Hours Attribute
Illustration 19: KeyColumns and NameColumn Settings for the Vacation Hours Attribute

We note that the NameColumn setting is empty, leaving us to conclude that the member names are being derived from the KeyColumns setting.

5.  Click the ellipses (“ ... ”) button to the immediate right of the KeyColumns property setting box, as depicted (encircled in red) in Illustration 20.

Click the Ellipses (“ ... “) Button to the Right of the KeyColumns Property
Illustration 20: Click the Ellipses (“ ... “) Button to the Right of the KeyColumns Property

The DataItem Collection Editor appears.

6.  Type (or cut and paste) the following into the Format property.

%{Last Bucket member} & Under; Between %{First Bucket member} & %{Last Bucket member} (incl); %{First Bucket member} & Over

An example of the intended output of each of the three delimited sections in our syntax above is presented in Table 2 below.

Syntax Section

Example Intended Output

%{Last Bucket member} & Under

“8 & Under”

Between %{First Bucket member} & %{Last Bucket member} (incl)

“Between 9 & 19 hours (incl)”

%{First Bucket member} & Over

“90 & Over”


Table 2: The Three Sections of Our Syntax and their Intended Output ...

Our syntax partially appears within the Format property box of the DataItem Collection Editor as shown (modifications surrounded by the red box) in Illustration 21.

Our Syntax with the Format Property of the DataItem Collection Editor (Partial View)
Illustration 21: Our Syntax with the Format Property of the DataItem Collection Editor (Partial View)

7. Click OK on the DataItem Collection Editor to save our input and to dismiss the Editor.

We are now ready to process the Analysis Services database, and then to examine the results of our handiwork with the browsers, as before.

Process the Analysis Services Database to Update for Our Custom Attribute Group Names

We will process the Analysis Services database within which we have been working, as we did earlier, and then browse the Vacation Hours attribute members, once again to confirm that we have adequately provided support for the custom naming requirements that our client colleagues have requested.

1. Right-click the Analysis Services project atop the tree in the Solution Explorer, as we did earlier.

2. Select Process ... from the context menu that appears, once again.

3. Click Yes on the dialog asking if we would like to save changes, as before.

Information updates on the server, and then the Process Database dialog appears.

4. Click the Run button on the dialog, once again.

The Process Progress viewer appears, and generates periodic status updates for various processing events, as we noted earlier. When processing is complete, we see a Process succeeded message appear in the Status bar in the lower part of the viewer, once again.

5. Click the Close button on the Process Progress viewer to dismiss the viewer.

6. Click the Close button on the Process Database dialog to dismiss the dialog.

We are now ready to return to the Dimension Designer browser to examine the results of our handiwork.

Browse the Newly Named Attribute Groups with the Dimension Browser

1. Click the Browser tab in the Dimension Designer.

2. Ensure that Vacation Hours remains selected in the Hierarchy selector atop the Browser tab.

3. Click the Reconnect button atop the tab, as we did in the earlier section.

The browser details update, and (assuming the All Employees level remains expanded in the browser), we see ten groups appear, as depicted in Illustration 22.

The Custom-Named, Discretized Attribute Member Groups
Illustration 22: The Custom-Named, Discretized Attribute Member Groups

We see the groups appear, as expected. Moreover, we note that the ten groups created by Analysis Services via the Automatic Discretization method, display the names in the format requested by the client representatives with which we have been working.

4.  Close the Employee Dimension Designer, as desired.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM