[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This sample works only with SQL Server 2005 and SQL Server "Katmai". It will not work with any version of SQL Server earlier than SQL Server 2005.

The Analysis Services Personalization Extensions sample is a C# program that illustrates how to create user-based personalizations. These personalizations are not permanent objects in the cube, but are objects that the client application applies dynamically during the user's session.

The Analysis Services Personalization Extensions sample dynamically creates a different object for three different users. For each user connection, the sample enables just the object that is associated with that user. Each of these personalization objects is only visible during the user session.

Default location: C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\Administrator\ASPE

Scenario

An independent software vendor (ISV) creates an interface that enables a user to design business metrics for his or her company, and then share those metrics with that user's peers. Those metrics are dynamic and adjustable to the person who uses them. For example, an ISV designs a client interface that enables a company's business analyst to design a Sales Monthly Goal Tracking indicator. This indicator calculates and tracks the monthly sales goal for each salesperson. The business analyst can then share this indicator with the company's sales team. Now, any member on the team can know how he or she is doing towards the monthly goal.

Without the personalization extensions, the indicator would have to be passed to the company's Analysis Services team. This Analysis Services team would have to incorporate the indicator directly into the cube design and then redeploy the cube to the end users. Because the indicator is an object in the cube, the indicator is not personalized to each user.

Languages

C#

Features

The Analysis Services Personalization Extensions Sample uses the following features of Microsoft SQL Server Analysis Services.

Application Area Features

AdventureWorks

ADOMD.Net Server, MDX

Prerequisites

Before you run this sample, you should have the following software installed:

  • Microsoft SQL Server, which includes the following components:

    • SQL Server Database Engine

    • Analysis Services

    • SQL Server Management Studio

    • Business Intelligence Development Studio

    • The SQL sample database: AdventureWorksDW (data warehouse)

    • Analysis Services samples

    • AdventureWorks database. An updated version of AdventureWorks database is available for download on the SQL Server Developer Center Web page

    • The SQL Server Database Engine samples. An updated version of the SQL Server Database Engine samples is available for download on the SQL Server Developer Center Web page

    • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. For more information, see the topic, "Installing the .NET Framework SDK," in SQL Server Books Online

Note:
Business Intelligence Development Studio is not supported on Itanium-based computers. The 64-bit development environment is supported on x64-based operating systems. SQL Server 2005 samples can be modified and run on computers that run either x86 or x64-based operating systems when databases used by the samples are deployed on an Itanium-based computer.

Building the Sample

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file

  1. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

    -- or --

    Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

  2. Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.
    Note:
    To determine the folder where samples are located, click the Start button, point to All Programs, point to Microsoft SQL Server, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\100\Samples.
  3. At the command prompt, run the following command to generate the key file:

    sn -k SampleKey.snk

    Important:
    For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

The Analysis Services Personalization Extensions Sample is composed of one binary file, ASPE.dll.

To build the Analysis Services Personalization Extensions Sample
  1. Compile ASPE.dll by using Visual Studio 2005 and the ASPE.sln file.

    — or —

    Compile ASPE.dll by using Microsoft MSBuild, which is included in the .NET Framework SDK 2.0. Run a command similar to the following at a command prompt:

    msbuild /nologo /verbosity:quiet /property:Configuration=Debug "CS/ ASPE.sln"

You can use other MSBuild build options depending on your needs.

Deploying the Sample

To deploy the Analysis Services Personalization Extensions sample, you have to add the assembly that you built in the previously procedure to a running instance of Analysis Services.

To add ASPE.dll assembly to a running instance of Analysis Services
  1. In SQL Server Management Studio, connect to the instance of Analysis Services.

    Localhost is assumed as default for this sample. Replace localhost in with the name of the instance that you have selected.

  2. At the server level, open the Assemblies folder and verify that the ASPE assembly is not listed.

    If the ASPE assembly is listed, remove this assembly by right-clicking the assembly and selecting Delete. Continue following the steps on the screen.

  3. Right-click Assemblies and select New Assembly.

  4. In the Register Server Assembly dialog box, select the following options:

    1. For Type, select .NET Assembly.

    2. For Filename, click the browse button […] and find the ASPE.dll file in your project folder under bin/debug folders.

    3. For Permissions, select Unrestricted.

    4. For Impersonation, select Use the Service Account.

    5. Click OK.

  5. At the server level, open the Assemblies folder and verify the ASPE assembly is listed.

  6. Restart the service to enable the correct management of all events.

Preparing the Environment to Test the Sample

On the same server where you are running Analysis Services and have deployed ASPE.dll, use the following procedures to set up the environment to test the sample.

To add three users to your current system
  1. Click Start, right-click My Computer, and select Manage.

  2. Expand Local Users and Groups to see the Users and Groups folders.

  3. Right-click the Users folder and select New User.

  4. In the New User dialog box, select the following options:

    1. For User Name, type user1.

    2. For Password, type a strong password that meets your company standards.

    3. For Confirm Password, re-type your selected password.

    4. Clear the User must change password at next logon check box.

    5. Select the Password never expires check box.

  5. Repeat step 4 for user2 and user3.

To verify Adventure Works DW database is deployed and contains Adventure Works cube
  1. On an Analysis Services instance, open SQL Server Management Studioand connect to the Server Name, localhost.

  2. Expand the Databases node and verify that the Adventure Works DW database exists.

  3. Expand the Adventure Works DW node, expand the Cubes node, and verify that the Adventure Works cube exists.

  4. Right-click the Adventure Works cube and select Properties. Verify that the cube state is Processed.

  5. If any one of the previous steps do not verify correctly, see the Books Online topic, "Running Setup to Install AdventureWorks Sample Databases and Samples," in the MSDN Library.

To add a role for the new users and allow them access to the cube and dimensions
  1. On an Analysis Services instance, open SQL Server Management Studio and connect to the Server Name, localhost.

  2. Expand the Databases node, and then expand Adventure Works DW database.

  3. Right-click Roles, and then select New role.

  4. In the Create Role dialog box, on the General page, select the following options:

    1. For Role Name, type ASPE Test Users.

    2. Select the Read Definition check box.

    3. Clear the Full Control and Process check box.

  5. On the Membership page, select the following options:

    1. Click Add.

    2. In Select Users or Groups window, for Enter the object names to select, type user1; user2; user3.

    3. Click Check Names.

      The values should now appear underlined, with the server or domain name preceding each name.

    4. Click OK.

  6. On the Data Sources page, in Access, for Adventure Works DW, select Read.

  7. On the Cubes page, select the following options:

    1. In Access, for Adventure Works, select Read.

    2. In Local Cube/Drillthrough, for Adventure Works, select Drillthrough and Local Cube.

    3. For Adventure Works, select the Process check box.

  8. On the Dimensions page, for all dimensions, select the Process check box.

  9. Click OK, and then wait until the command is processed and the new role is created.

Testing the Sample

Testing the sample demonstrates that new personalization objects are created in different user contexts and that those objects are associated with the user profile.

To verify your context
  1. In SQL Server Management Studio, connect to the Server Name, localhost.

  2. On the Standard toolbar, click Analysis Services MDX Query.

  3. Connect to the Server Name, localhost.

  4. On the SQL Server Analysis Services toolbar, select the Adventure Works DW database if it is not already selected.

  5. In the left pane of the MDX tab, select the Adventure Works cube if it is not already selected.

  6. Select the Metadata tab if it is not already selected.

  7. Expand the Measures node, expand the Internet Sales node, and verify that there is no ISV_1 folder under this node.

  8. Expand the Geography dimension, and verify that there is no ISV_1 folder in this dimension.

  9. Expand the Product dimension, and verify that there is no ISV_1 folder in this dimension.

To verify the context for user1
  1. Right-click the SQL Server Management Studio icon, select Run as, and then select the following options:

    1. Select The following user.

    2. Replace with user1. Make sure that the domain or server name is correct.

  2. Connect to the Server Name, localhost.

  3. On the Standard toolbar, click Analysis Services MDX Query.

  4. Connect to the Server Name, localhost.

  5. On the SQL Server Analysis Services toolbar, select Adventure Works DW database, if it is not already selected.

  6. In the left pane of the MDX tab, select the Adventure Works cube, if it is not already selected.

  7. Select the Metadata tab, if it is not already selected.

  8. Expand the Measures node, and then expand the Internet Sales node to see the ISV_1 folder.

    Unlike your user context, user1 does have an ISV_1 folder under the Internet Sales node.

  9. Expand the ISV_1 folder, expand the Common folder, expand the Gross Internet Profit member, expand the Reseller Sales measure group, expand the ISV_1 folder, and then expand the Operations folder.

    Notice that the Operations folder contains the Average Item Freight Cost member.

  10. Expand the Geography dimension, expand the ISV_1 folder, and then expand the Common folder to see the ISV_1 - Western Coast set.

    Unlike your user context, user1 does have access to the ISV_1 - Western Coast set.

  11. Expand the Product dimension, expand the ISV_1 folder, and then expand the Operations folder to see the Touring Bikes set.

    Unlike your user context, user1 does have access to the Touring Bikes set.

To verify the context for user2
  1. Right-click the SQL Server Management Studio icon, select Run as, and then select the following options:

    1. Select The following user.

    2. Replace with user2. Make sure that the domain or server name is correct.

  2. Connect to the Server Name, localhost.

  3. On the Standard toolbar, click Analysis Services MDX Query.

  4. Connect to the Server Name, localhost.

  5. On the SQL Server Analysis Services toolbar, select the Adventure Works DW database if it is not already selected.

  6. In the left pane of the MDX tab, select the Adventure Works cube if it is not already selected.

  7. Select the Metadata tab if it is not already selected.

  8. Expand the Measures node, expand the Internet Sales node, expand the ISV_1 folder, expand the Common folder, expand the Gross Internet Profit member, and then select the following options:

    1. Expand the Reseller Sales measure group, and verify that there is no ISV_1 folder in this group.

      Only user1 has an ISV_1 folder in the Reseller Sales measure group.

    2. Expand the Exchange Rates measure group, expand the ISV_1 folder, and then expand the Finance folder to see the Rate Difference member.

      For user2, it is the Exchange Rates measure group that has the ISV_1 folder.

  9. Expand the Geography dimension, expand the ISV_1 folder, and then expand the Common folder to see the ISV_1 - Western Coast set.

    user2, just like user1, has access to the ISV_1 - Western Coast set.

  10. Expand the Product dimension, expand the ISV_1 folder, and then expand the Finance folder to see the Mountain Bikes set.

    While user2 has access to the Mountain Bikes set, user2 does not have access to the Touring Bikes set in the Operations folder. Only user1 has access to the Touring Bikes set.

To verify the context for user3
  1. Right-click the SQL Server Management Studio icon and select Run as, and then select the following options:

    1. Select The following user.

    2. Enter user3.

    3. Make sure that the domain or server name is correct.

  2. Connect to the Server Name, localhost.

  3. On the Standard toolbar, click Analysis Services MDX Query.

  4. Connect to the Server Name, localhost.

  5. On the SQL Server Analysis Services toolbar, select Adventure Works DW database, if it is not already selected.

  6. In the left pane of the MDX tab, select the Adventure Works cube, if is not already selected.

  7. Select the Metadata tab, if it is not already selected.

  8. Expand the Measures node, expand the Internet Sales node, expand the ISV_1 folder, expand the Common folder, expand the Gross Internet Profit member, and then select the following options:

    1. Expand the Reseller Sales measure group, and verify that there is no ISV_1 folder in this group.

      Only user1 has an ISV_1 folder in the Reseller Sales measure group.

    2. Expand the Exchange Rates measure group, and verify that there is no ISV_1 folder in this group.

      Only user 2 has an ISV_1 folder in the Exchange Rates measure group.

    3. Expand the Reseller Orders measure group, expand the ISV_1 folder, and then expand the Marketing folder to see the Reseller Discount per Order member.

      For user3, it is the Reseller Orders measure group that contains the ISV_1 folder.

  9. Expand the Geography dimension, expand the ISV_1 folder, and expand the Common folder to see ISV_1 - Western Coast set.

    user3, just like user1 and user2, has access to the ISV_1 - Western Coast set.

  10. Expand the Product dimension, expand the ISV_1 folder, and then expand the Marketing folder to see the Road Bikes set.

    While user 3 has access to the Road Bikes set, user3 does not have access to the Touring Bikes set in the Operation folder. Only user1 has access to the Touring Bikes set. user3 also does not have access to the Mountain Bikes set in the Finance folder. Only user2 has access to the Mountain Bikes set.

Troubleshooting the Sample

Use the following set of procedures to troubleshoot the Analysis Services Personalization Extensions sample.

To define debug breakpoints in the code
  1. In Visual Studio 2005, open ASPE.sln.

  2. Open the source code for the ASClientExtensions.cs source file, and then set a breakpoint on the opening brace of the ASClientExtensions constructor.

  3. Open the source code for the SessionMgr.cs source file, and then set the following breakpoints:

    1. Set a breakpoint on the opening brace of the SessionMgr constructor.

    2. Set a breakpoint on the opening brace of the CubeOpened method.

  4. Open the source code for the AuthoringAndManagement.cs source file, and then set the following breakpoints.

    1. Set a breakpoint on the opening brace of the AuthoringAndManagement constructor.

    2. Set a breakpoint on the opening brace of the DefineMembers method.

    3. Set a breakpoint on the opening brace of the DefineSets method.

    4. Set a breakpoint on the opening brace of the DefineKPIs method.

To attach to the running instance of Analysis Services
  1. In the menu bar, select Debug, and then select Attach to process.

  2. In the Available Process pane, browse to find msmdsrv.exe, and then select it.

  3. Click Attach, and then wait until the debugger attaches to the running process.

To run through the breakpoints
  1. Repeat all the steps in the procedure, To verify the context for user1.

    Notice that SQL Server Management Studio does not immediately appear. Instead, Visual Studio opens.

  2. To see the sequence of events, press F11 repeatedly.

    If there are no more events, check SQL Server Management Studio to continue with the steps in the procedure, To verify the context for user1.

Removing the Sample

Use the following procedure to remove the Analysis Services Personalization Extensions Sample.

To remove the roles and assembly from the current Analysis Services instance
  1. Open SQL Server Management Studio.

  2. Connect to the Server Name, localhost

  3. Expand the Databases node, and then expand Adventure Works DW database.

  4. Expand Roles, right-click ASPE Test Users, select Delete, and then click OK.

  5. At server level, expand Assemblies, right-click ASPE, select Delete, and then click OK.

To remove the user created for this sample
  1. Click Start, right-click My Computer, and select Manage.

  2. Expand Local Users and Groups to see the Users and Groups folders.

  3. Expand the Users folder.

  4. Remove user1, user2, user3 by right-clicking each user, selecting Delete, and then clicking OK.

To remove the ASPE sample
  • From your solution folder, in the ASPE folder, remove the bin and obj folders.