Home > Articles > Data > SQL Server

Like this article? We recommend

Continuing with Calculated Members

To this point, we have focused on the most common form of calculated member that we can expect to encounter in the business environment, the calculated measure (a member of the measures dimension). In the next section, we will explore the creation of a calculated member of a standard dimension and work through some exercises to see why this is useful.

Creating an Aggregate Member

One of the best uses of a calculated member within a standard dimension is as a grouping device, from which aggregations can be based, as we will see in our next practice set. We create non-measure calculated members by specifying the dimension to which they belong, as well as the point within the hierarchy that they inhabit. Although the process is a bit more involved than the corresponding set of steps for a calculated measure, you will get a good understanding of how to derive and use such a calculated member in a sequenced set of steps.

TIP

Saving the step as a separate query, with each progressive stage of the build process, provides a way to return to prior steps for review; or, in the case of a misstep, provides an easy return path from which to recover. This is the process I use in many development efforts in which I am encountering new concepts and I want to reinforce my foundation at every opportunity.

Let's get started by returning to the Sample Application and by setting up a core query from which we will build the next key elements of MDX. We will begin where we left off, with the query we previously saved as Step6.

  1. Modify the comment line in the query to read -–Step2-1.

  2. Save the query as Step2-1 before making further alterations (just as a precaution).

  3. Within the SELECT statement of the query, modify the section in which we define the rows of our result set (just after the ON COLUMNS section) to the following:

    Step6 Affected Section:

    { [Measures].[Store Sales], 9
      [Measures].[Store Cost], 9
      [Measures].[Store Profit]} ON ROWS

    New Section:

    CrossJoin({ [Measures].[Store Sales], 9
      [Measures].[Store Cost], 9
      [Measures].[Store Profit]}, 9
    {[Product].[All Products].Children}) ON ROWS

The modified query should appear in its entirety in the Query pane as shown in Figure 1.

Figure 1Figure 1 Step 2-1, with modifications annotated, as it appears in the Query pane.

The effect of the change above is to nest all children of the Product dimension in our row axis. Our entry also provides a review of the CrossJoin function as part of fitting our result to be able to better identify the characteristics of aggregation in later steps. Our intent here is to establish a context from which we can see the practical applications of aggregation in MDX.

  1. Execute the query by clicking the Run button.

    The result set appears in Figure 2.

    Figure 2Figure 2 Step2-1 results.

    The results set shows the desired nesting, which is intended to give us a level of members to group for purposes of practicing the use of non-measure calculated members. As before, we will leave formatting as a later consideration and focus on the retrieval of data.

  2. Save the query with changes as Step2-1.

To set the backdrop of a business requirement, let's begin by assuming that information consumers have communicated a need to basically group the three types of products that we see into food and non-food groups. Since we have a non-food group already (Non-Consumable), our task is simply to group, or aggregate, the Food and Drink categories into a single group that we will call Consumable. We begin by creating a non-measure calculated member to do just that.

  1. Modify the comment line in the query to read -–Step2-2.

  2. Within the WITH statement of the query, immediately following the SOLVE_ORDER = 2, FORMAT_STRING = 'Percent' line, insert the following:

    MEMBER [Product].[All Products].[Consumable] AS 9
          '[Drink]+[Food]'
  3. At the last line (just above the FROM line) within the SELECT statement of the query, make the following modifications:

    Step2-1 Affected Line:

    {[Product].[All Products].Children}) ON ROWS

    Step2-2 Modified Line:

    {[Product Family].Allmembers}) ON ROWS

With these insertions, we are creating a new calculated member, Consumable, to group the Food and Drink categories. We are establishing the parent of the new calculated member as All Products, which coincides with the hierarchical level of the members of Product Family. The .AllMembers function (as opposed to the .Members function) is required to display calculated members.

NOTE

A study of the metadata, something that can be done conveniently from the Sample Application's Metadata tree, or from the Analysis Server console for the respective cube, is critical for an understanding of the interrelationships of the levels and members of the hierarchy. Some views display members to appear multiple times in the tree. While this is initially confusing for many who are new to the concepts involved, practice will bring a comfort level with the tool, as well as a capability to understand the levels themselves. We need to grasp the idea of parallel levels, so we can create and display calculated members to occupy equivalent levels and to obtain the results we seek.

The modified query should appear in the Query pane, as shown in Figure 3.

Figure 3Figure 3 Step2-2 with modifications annotated, as it appears in the Query pane.


  1. Execute the Step2-2 query by clicking the Run button.

    The result set appears in Figure 4.

    Figure 4Figure 4 Step2-2 results.

    We see the newly created calculated member Consumable appear, and a quick sample check of the totals of the respective Food and Drink categories confirm that the SUM function is working in conjunction with the Consumable member to produce an accurate total.

  2. Save the query with changes as Step2-2.

We have created an aggregate member for a small group of members (actually only two categories: Food and Drink), in which using the + sign to perform the aggregation is convenient. Let's look at an example within the same query, in which we can examine the best way to perform an aggregation when multiple values are involved, and the + sign route would prove to be somewhat labor-intensive. First, we need to establish a scenario in which we have multiple values to add. We do this by again seeking a lower level in the hierarchical structure via our core query.

  1. Modify the comment line in the query to read -–Step2-3.

  2. Remove the aggregate calculated member that we created in Step2-2 by removing the following from the WITH clause of the query:

    MEMBER [Product].[All Products].[Consumable] AS 9
          '[Drink]+[Food]'
  3. Within the SELECT statement of the query, at the last line (just above the FROM line), make the following modifications:

    Step2-2 Affected Line:

    {[Product Family].Allmembers}) ON ROWS

    Step2-3 Modified Line:

    {[Product Department].Allmembers}) ON ROWS

    With this modification, we establish a lower level of nesting, in order to have more items with which to work for aggregation purposes.

    Query 2-3 appears in the Query pane as shown:

    Figure 5Figure 5 Step2-3 with modifications noted, as it appears in the Query pane.

  4. Execute the Step2-3 query by clicking the Run button.

    A partial result set appears Figure 6.

    Figure 6Figure 6 Step2-3 partial results.


    We note the appearance of the Product Department members in the row axis—an excellent place from which to explore the aggregation of multiple members.

  5. Save the query with changes as Step2-3.

For now, let's say that we have been tasked with combining some of the multiple department classifications into a group (similar to that we created above) to contain only Consumable items (as opposed to non-food, non-drink merchandise). Although higher hierarchical levels make the distinction clear, we want to obtain a total, for this example, of the consumable items at the Product Department level that is displayed.

We begin again by creating a non-measure calculated member to do just that.

  1. Modify the comment line in the query to read -–Step2-4.

  2. Within the WITH statement of the query, immediately following the SOLVE_ORDER = 2, FORMAT_STRING = 'Percent' line, insert the following:

    MEMBER[All Products].[Drink].[Consumable] AS 9
         'SUM([Product Family].Members)'

Again, while it may appear confusing to anyone not familiar with the hierarchical structure of the Warehouse and Sales cube, we are establishing the new calculated member, again called Consumable, at a level that is, in effect, parallel to those at the level upon which we want to report. At this juncture, Consumable is aggregating all departments. We will adjust this in the next step to exclude non-consumable items. Note, too, that the aggregation is set to occur at another dimensional level (Product Family). I chose this level because there is less ambiguity in consumable versus non-consumable product classifications, but there are certainly other ways to accomplish the same task.

The modifications to the CrossJoin function within the SELECT clause, which we made in the last step when we removed the first Consumable calculated member, remain adequate for our display purposes, so no further alterations will be required at this point.

The modified query should appear in the Query pane, as shown in Figure 7.

Figure 7Figure 7 Step2-4, with calculated member creation circled, from the Query pane.


  1. Execute the Step2-4 query by clicking the Run button.

    The result set partially appears in Figure 8.

    Figure 8Figure 8 Step2-4 partial results.


We see the aggregate calculated member Consumable appear, and a quick sample check of the totals of the respective Product Department row items confirms that the SUM function is working in conjunction with the modified Consumable member to produce a total.

Now, let's make a further enhancement to exclude the non-consumable items and cause Consumable to reflect accurately the combined values of the food-related items, in accordance with the requirement we are attempting to meet.

  1. Save the query with changes as Step2-4.

  2. Modify the comment line in the query to read -–Step 2-5.

  3. Within the WITH statement of the query, immediately following the MEMBER[All Products].[Drink].[Consumable] AS line, modify the existing definition of the calculated member Consumable, as shown below:

    Step2-4 Affected Line:

         'SUM([Product Family].Members)'

    Step2-5 Modified Line:

         'SUM(EXCEPT([Product Family].Members,{[Non-Consumable]}))'

The modification above acts to make the SUM function include all except the members that are classed as Non-Consumable at the Product Family level. Again, we are referencing Product Family here (another hierarchical level entirely) because it conveniently classifies the non-consumables, unlike the level upon which we are required to report. As mentioned earlier, a grasp of the hierarchical structure is key to the employment of such efficiencies; we can thus avoid a cumbersome exclusion mechanism and leverage attributes of the data that although perhaps not foreseen as useful at our specific level during cube design, can still be useful in getting us where we need to be.

Query 2-5 appears in the Query pane, as shown in Figure 9.

Figure 9Figure 9 Step2-5, with modification noted, as it appears in the Query pane.


The SELECT portion of the query remains adequate for our immediate retrieval needs.

  1. Execute the Step2-5 query by clicking the Run button.

    The result set appears partially displayed in Figure 10.

    Figure 10Figure 10 Step2-5 partial results.


A quick test of the math again verifies that our modifications to the Consumable calculated member have done the job. We are effectively excluding the non-consumable items at the Product Department level, based upon their membership in the Non-Consumable group at the Product Family level, and thus delivering a total amount for consumables at the Product Department level. Once again, we can meet the business requirements with our MDX query.

  1. Save the query with changes as Step2-5.

The results of our latest sequence of steps have shown the capabilities that we have available when we need to aggregate a group of dimension members for a single query. Many iterations of the approach can be applied and various nuances conceived to gain the advantage offered by aggregation members. Options might include use of a calculated member, such as the one we have derived as a filter, to "narrow" the selection of data in an existing report to focus on a key group of members—be it geographical, organizational, or any number of other possibilities that might be supported by a good hierarchical design in the cube.

WARNING

Be careful of injudicious use of aggregation in the WHERE clause. Performance can be adversely affected if we make too heavy use of this approach. If alternate aggregation is a recurring requirement, creation of a second hierarchy for the affected dimension might mean better performance.)

We could substitute the AGGREGATE function name for SUM in our query above and obtain the same results. The AGGREGATE function allows us to give more detailed instructions for defining the type of aggregation we require (such as SUM, MIN, MAX, AVG, and so forth), which calls for more than the characteristics of a simple summary function. For more information, see the Books Online reference that is installed as a part of a Typical MSSQL Server 2000/Analysis Services installation, or that is available from the product CDs, the Microsoft site, or other sources.

A Quick Overview of Cumulative Totals

A natural extension of our exploration into aggregations lies in the area of cumulative totals. We can extend the SUM function that we have used to create calculated measures on standard dimensions above, and accomplish the same effect with a new calculated measure. A common use of the SUM function to do so would be to create a cumulative total.

The calculation of a cumulative, or "running," total is a common requirement in the business environment. We examine ways to deliver this analytical component to our information consumers in the following steps. In addition, we will discuss the functions that MDX provides for needs just such as these. We begin with a new query this time, so be sure to save the work we have accumulated (no pun intended) to this point for later reference, if appropriate.

  1. Create a new query.

  2. Insert the following via the Query pane:

    --Step3-1
    SELECT 9
      [Month].Members ON COLUMNS, 9
      {[Measures].AllMembers} ON ROWS 9
    FROM [Warehouse and Sales]
  3. Execute the query by clicking the Run button.

    The result set appears partially displayed in Figure 11.

    Figure 11Figure 11 Step3-1 partial results.

After the manner of our earlier examples, we have initially established a core query upon which we will perform a short series of steps to gain an understanding of larger concepts. Notice that all months appear (the numbers identifying them are the actual Member Names in the Warehouse and Sales cube). Our use of .AllMembers in the second line of the SELECT statement allows us to see all members, including calculated members. The results are a bit overwhelming at present.

  1. Save the query with changes as Step3-1.

Let's add a calculated member to get closer to our objective of producing a running total.

  1. Modify the comment line in the query to read -–Step3-2.

  2. Add the following before the SELECT statement:

    --Step3-2
    WITH
      MEMBER [Measures].[Cumulative] AS 9
    'SUM([Time].[1997].[Q1].[1]: 9
    [Time].CurrentMember, [Store Sales Net])' 9

The addition above contains a SUM function whose syntax contains two arguments that we are exploring for the first time. The layout of the function appears as follows:

Sum(«Set»[, «Numeric Expression»])

We used the first argument, which is required, in our groupings in the last example. The second argument, << Numeric Expression>>, is optional and virtually never used when creating a non-measure calculated member, as was the case in our earlier use of the function. However, any time we create a calculated measure, as we are doing with Cumulative above, we would expect to use the second argument; the <<Numeric Expression>> argument instructs the function about which measure to use as the basis in creating the new measure.

We placed the basis measure, Store Sales Net, in the <<Numeric Expression>> position and added a set that defined the time period range we wanted to include, separating the beginning time period ([Time].[1997].[Q1].[1]) and ending time period ([Time].CurrentMember) of the range we wanted with the colon (:) we see above. The .CurrentMember placement is the key behind the roll forward effect we obtain in each of the columns we will see next.

Our query appears in the Query pane, as shown in Figure 12.

Figure 12Figure 12 --Step3-2, with addition noted, as it appears in the Query pane.

  1. Execute the query by clicking the Run button.

The result set appears partially displayed in Figure 13.

Figure 13Figure 13 Step3-2 partial results.

We can see that the Cumulative values are, indeed, accumulating the Store Sales Net values—each month adding the current month's value to the total and delivering the "running total." This accumulation continues until the first month of 1998, after which we see the Cumulative total remaining the same for the months that follow because the cube contains no values for Store Sales Net after Month 12 of 1997.

  1. Save the query with changes as Step3-2.

MDX provides functions to calculate cumulative values. One of the most commonly used is the PeriodsToDate function, an instance of which we will build into our query as the next step to illustrate its use. Moreover, we will restrict the data we return to make the result set more compact and hopefully easier to use by information consumers.

  1. Create a new query.

  2. Type the following into the Query pane:

    --Step3-3
    WITH 9
      MEMBER [Measures].[YTD Shipped] AS 9
    'SUM(PeriodsToDate([Time].[Year]), [Units Shipped])' 9
    SELECT 9
      [Month].Members ON COLUMNS, 9
      {[Measures].[Units Shipped],[YTD Shipped]} ON ROWS 9
    FROM [Warehouse and Sales] 9

While the above is intended to create a cumulative total like the last example, we give the cumulative calculated member a name that makes sense to the user (YTD Shipped) based upon what it is (a cumulative total whose basis is Units Shipped) and what it does (provides a running total over the months of the year.) In the PeriodsToDate function, we set Year as the parent. The function behaves just as it did with the date range we inserted in the last example, creating a set whose ending point is the current member of what has been defined as the Time dimension.

Our query appears in the Query pane as partially shown in Figure 14.

Figure 14Figure 14 Step3-3 as it partially appears in the Query pane.

  1. Execute the query by clicking the Run button.

    The result set appears partially displayed in Figure 15.

    Figure 15Figure 15 Step3-3 partial results; "reset point" selected for YTD shipped.

My intent in the above partial view is to show the "reset" of the YTD Shipped value at the Month 12, 1997 to Month 1, 1998 transition. Within the PeriodsToDate function, the beginning member of the set becomes the first member with the same common parent as the ending member. The cumulative feature begins again at the emergence of a new parent, as in the case above, with the change of the Year to 1998.

  1. Save the query with changes as Step3-3.

Let's further illustrate the operation of the PeriodsToDate function by making a change in the argument (the portion of the function enclosed by parentheses) whose role in the "reset" point placement within the result set will be illustrated once again.

  1. Modify the comment line in the query to read -–Step 3-4.

  2. Within the WITH statement of the query, modify the existing definition of the calculated measure, as shown below:

    Step3-3 Affected Lines:

    MEMBER [Measures].[YTD Shipped] AS 9
    'SUM(PeriodsToDate([Time].[Year]), [Units Shipped])'

    Step3-4 Modified Lines:

    MEMBER [Measures].[QTD Units Shipped] AS 9
    'SUM(PeriodsToDate(), [Units Shipped])'

    The name of the calculated measure is changed to QTD Units Shipped, and the PeriodstoDate function has the argument removed, leaving only empty parentheses.

  3. Within the SELECT statement of the query, modify the following:

    Step3-3 Affected Lines:

    {[Measures].[Units Shipped],[YTD Shipped]} ON ROWS

    Step3-4 Modified Lines:

    {[Measures].[Units Shipped],[QTD Units Shipped]} ON ROWS

The modification here is simply to adjust the displayed results set for the new calculated member name (QTD Units Shipped), as defined in the WITH statement above.

Query 3-5 appears in the Query pane in its entirety, as shown in Figure 16.

Figure 16Figure 16 Step3-5, with modifications noted, as it appears in the Query pane.

The SELECT portion of the query remains adequate for our immediate retrieval needs.

  1. Execute the Step3-5 query by clicking the Run button.

    The result set appears partially displayed in Figure 17.

    Figure 17Figure 17 Step3-5 partial results, with "reset" examples circled.

We see that the function now causes a reset at the quarter breaks. The effect of removing the argument, and therefore not specifying the parent involved in the time dimension, results in the "lowest common parent" being assumed; the quarter therefore assumes the role. Thus, we see the operation of the PeriodsToDate function illustrated once more.

  1. Save the query with changes as Step3-5.

Any time we need to query week-to-date, month-to-date, quarter-to-date, or year-to-date periods specifically, we will find that the richness of the MDX language is again apparent: We can use the following functions as shortcuts to achieve results like those we have explored in this section:

  • WTD(<member>)
  • MTD(<member>)
  • QTD(<member>)
  • YTD(<member>)

Selecting and Retrieving Member Properties

The final section of this lesson focuses on the selection of member properties within our MDX queries. MDX queries allow for the selection of member properties, which are defined for the dimensions themselves, or on individual member levels. We can make use of the member properties within calculated members. Member properties can be useful, as we will see with the next example.

Special considerations come into play, however, in using member properties in calculated members, not the least of which is the fact that the values stored at the member property level, even numeric values, are stored as text strings. This means that conversion of the string to a number is required to perform any operations in which the property is supposed to behave like a number. We will illustrate this set of circumstances, as well as a means of handling it, to achieve "rollup" of the properties to higher levels in the simple exercise that follows.

One of the member properties that occur within a dimension of the Warehouse and Sales cube will serve well to illustrate the selection and retrieval of member properties. A great place to get a bird's-eye-view of the metadata of a cube is the Cube Editor in Analysis Services. A key activity for getting a good understanding of the cube structure is obtaining an understanding of the metadata. A quick review of the Store dimension within the tree in the Cube Editor (right-click the cube and select Edit to reach the view from the Analysis Manager console) reveals the presence of member properties for the Store dimension, as shown in Figure 18.

Figure 18Figure 18 A Cube Editor view of the Store Dimension Member properties.


The member properties include an example of a numerical value, Store SqFt, which contains the square footage area for the respective store members. As we have discovered, although numeric in nature, the member property information is stored as a text string. This provides us with an excellent example for our exercise in selecting and retrieving such properties in a way that will make them useful to Information Consumers.

  1. Create a new query in the Query pane of the Sample Application.

  2. Type the following into the Query pane:

    --Step4-1
    SELECT 9
      {[Measures].[Store Sales]} ON COLUMNS, 9
      {[Store].[Store Name].Members} ON ROWS 9
    From [Warehouse and Sales]

The above query forms the core of our next exercise.

Our query appears in the Query pane as partially shown in Figure 19.

Figure 19Figure 19 The first step of the new query as it appears in the Query pane.

  1. Execute the query by clicking the Run button.

    The result set appears partially displayed (scrolled to the bottom) in Figure 20.

    Figure 20Figure 20 Step4-1 partial results.


The purpose of the first-step core query is simply to set up the axes for the enhancements we will make to allow the retrieval and display of member properties. As a useful way to contrast the behavior of the text-string property that we will be attempting to present as a number, we have included a numerical value from the cube, Store Sales, simply to compare to the Store SqFt member property that we will be retrieving.

  1. Save the query with changes as Step4-1.

We now add the syntax required to create a calculated member based upon the Store SqFt member property.

  1. Modify the comment line in the query to read -–Step4-2.

  2. Add the following before the SELECT statement:

    WITH
      MEMBER [Measures].[Store Size] AS 9
      'Val(Store.CurrentMember.Properties("Store Sqft"))'

The syntax addition above contains Val, a VBA function that returns the numbers contained in a string as a numeric value of the appropriate type.

  1. Modify the first line of the SELECT statement as follows:

    Step4-1 Affected Lines:

      {[Measures].[Store Sales]} ON COLUMNS,

    Step4-2 Modified Lines:

    {[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,

Our query now appears in the Query pane, as shown in Figure 21.

Figure 21Figure 21 Step4-2, with additions noted, as it appears in the Query pane.

  1. Execute the query by clicking the Run button.

The result set appears partially displayed in Figure 22.

Figure 22Figure 22 Step4-2 partial results.


In the results set, we note that the new calculated member, Store Size, appears with zeros filling the fields for which no square footage is defined in the cube (the "nulls" are converted to zeros). We can see that the text strings that occupy the Store SqFt member property have been converted to numerical values because the nulls that exist in the cube for certain members have become zeros instead.

  1. Save the query with changes as Step4-2.

Next, let's change the SELECT clause to retrieve and display not only the Store Names (the lowest level of the Store dimension), but to retrieve and display the "rollup" levels in the hierarchy of Store City, Store State, and Store Country.

  1. Modify the comment line in the query to read -–Step 4-3.

  2. Within the SELECT clause of the query, immediately following the {[Measures].[Store Profit], [Measures].[Store Size]} ON COLUMNS, line, modify the next existing line as follows:

    Step4-2 Affected Line:

    {[Store].[Store Name].Members} ON ROWS

    Step4-3 Modified Line:

    {[Store].Members} ON ROWS 

The modification above changes the SELECT clause to retrieve the members of the Store dimension in general, which will include all members—not just Store Names. This will mean that the individual stores, as well as the level members to which they belong, will be retrieved.

The modified query appears in the Query pane in its entirety, as shown in Figure 23.

Figure 23Figure 23 Step4-3, with modification circled, as it appears in the Query pane.

  1. Execute the Step4-3 query by clicking the Run button.

The result set appears partially displayed in Figure 24.

Figure 24Figure 24 Step4-3 partial results.


At the rollup levels, we have #ERR appearing as the result in the affected fields. This occurs because although we have converted the Store SqFt member property to a numeric value, we have yet to make provision for an aggregation to occur; the square footage values are held at the member level, not at the various dimension levels to which the stores belong in our cube. We handle this with the next step, in which we create a calculated member, based upon the Store dimension, to sum the individual store square footages at the appropriate rollup levels.

We create a calculated member that correctly handles the summing while leaving our original calculated member in place for reference purposes.

  1. Save the query with changes as Step4-3.

  2. Modify the comment line in the query to read -–Step 4-4.

  3. Within the WITH statement of the query, immediately following the 'Val(Store.CurrentMember.Properties("Store SqFt"))', line, add the following syntax to create a new calculated member for Total Store Area:

    MEMBER[Measures].[Total Store Area] AS 9
      'SUM(Descendants([Store].CurrentMember, [Store Name]) , [Store Size])' 
  4. In the first line within the SELECT statement of the query, modify the syntax to include selection of the new calculated member, Total Store Area:

    Step4-3 Affected Line:

    {[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,

    Step4-4 Modified Line:

    {[Measures].[Store Sales], [Measures].[Store Size], 9
      [Measures].[Total Store Area]} ON COLUMNS,

The modified query appears in the Query pane, as shown in Figure 25.

Figure 25Figure 25 Step4-4, with modification circled, as it appears in the Query pane.

  1. Execute the Step4-4 query by clicking the Run button.

The result set appears partially displayed in Figure 26.

Figure 26Figure 26 Step4-4 partial results.


The "parallel calculated member," Total Store Area, appears alongside its predecessor—and appears to have overcome the limitations of the Store Size calculated member, as well. We see rollups occurring accurately, in that the individual stores' square footages now add correctly in the corresponding Store City, Store State, and Store Country levels.

  1. Save the query with changes as Step4-4.

Were this a "real-world" report for a client, I would perform formatting at this stage after removing the Store Size calculated member from the SELECT statement, so as not to display it. We will leave the query at this stage, however, with an invitation to all readers to play with the layout further as time is available.

With this example, we have advanced another step with our calculated member, retrieving a member property and returning it after a conversion with the val function, in a way that allows it to deliver more value to the information consumers. We have taken steps to leverage the property beyond its standard capability to store information for the member that contains it, and to provide rollup values through the SUM function. This demonstrates additional possible uses for the aggregation of values using a non-measure calculated member.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020