Welcome  |  Sign in
Skip Navigation Links
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Development
 Readme_Analysis Services Personaliz...
SQL Server 2008 Books Online
Readme_Analysis Services Personalization Extensions Sample

This sample works only with SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2008.

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 only the object that is associated with that user. Each of these personalization objects is only visible during the user's session.

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. The metrics are dynamic and adjustable to the person who uses them. For example, the 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 easily know how he or she is doing towards the monthly goal, as the indicator is automatically tailored to the goals of each salesperson.

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 would be an object in the cube, the indicator would not be personalized to each user.

C#

The Analysis Services Personalization Extensions Sample uses the following features of SQL Server 2008 Analysis Services (SSAS).

Application Area Features

AdventureWorks

ADOMD.NET Server, MDX

Before you run this sample, you should have SQL Server and the following SQL Server components installed:

  • SQL Server Database Engine
  • Analysis Services
  • SQL Server Management Studio
  • Business Intelligence Development Studio
    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.

  • Updated versions of the following SQL Server sample databases: the AdventureWorks data warehouse and the AdventureWorks database.
    You can download these sample databases from the Microsoft SQL Server Samples and Community Projects Web site. For more information about how to download and install sample databases, see Installing SQL Server Samples and Sample Databases.
  • Updated versions of the following SQL Server samples: Analysis Services samples and SQL Server Database Engine samples.
    You can download these samples from the Microsoft SQL Server Samples and Community Projects Web site. For more information about how to download and install samples, see Installing SQL Server Samples and Sample Databases.

You also need .NET Framework SDK 2.0 or Microsoft Visual Studio 2005 installed. You can obtain .NET Framework SDK free of charge. For more information, see Installing the .NET Framework SDK

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

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

  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.

  1. Rename SampleKey.snk to ASPE.snk and move the file to the same location where ASPE.sln is.

  2. Compile ASPE.dll by using:

    • Visual Studio 2005 and the ASPE.sln file.

    — or —

    • Microsoft MSBuild, which is included in the .NET Framework SDK 2.0. Change directory to the location where the solution file ASPE.sln is located. Run a command similar to the following at a command prompt:

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

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

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

  1. In SQL Server Management Studio, connect to the instance of Analysis Services.

    Localhost is assumed as the default instance 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. To enable the correct management of all events, open SQL Server Configuration Manager, select SQL Server Services, and then select the SQL Server Analysis Services row. Click the restart button to restart the service

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.

  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.

  1. On an Analysis Services instance, open SQL Server Management Studio and 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 cannot be completed, see the Books Online topic, Running Setup to Install AdventureWorks Sample Databases and Samples, in the MSDN Library.

  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 the Adventure Works DW database.

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

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

    1. For Role name, type ASPE Test Users.
    2. Select the Read definition check box.
    3. Clear the Full control (Administrator) and Process database check boxes.

  5. Click the Membership page, and select the following options:

    1. Click Add.
    2. In the 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 in front of each name.
    4. Click OK.

  6. Click the Data Sources page. In the Access column for the Adventure Works DW row, select Read.

  7. Click the Cubes page, and select the following options for the Adventure Works row:

    1. In the Access column, select Read.
    2. In the Local Cube/Drillthrough Access column, select Drillthrough and Local Cube. Click OK to accept the message.
    3. In the Process column, select the check box.

  8. Click the Dimensions page. For each of the rows in Dimensions, select the Process column check box.

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

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

  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, in the Cube list, 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.

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

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

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

  1. Users running Windows XP or Windows Server 2003. 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.
    3. Proceed to step 3.

  2. Users running Windows Vista or Windows Server 2008. Open a command window and run the following command:runas.exe /profile /user:<Domain>\user1 "<Path-to-ssms>\Ssms.exe"

    1. Replace <Domain> with the correct domain or server name.
    2. Replace <Path-to-ssms> with the complete path to ssms.exe, the executable file for SQL Server Management Studio.
      Note:
      Windows Vista and Windows Server 2008 no longer have the Run as option when you right-click an application.

  3. Ensure that Server type is set to Analysis Services, then connect to the Server name, localhost.

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

  5. Connect to the Server name, localhost.

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

  7. In the left pane of the MDX tab, in the Cube list, select the Adventure Works cube, if it is not already selected.

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

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

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

  10. Expand the ISV_1 folder, expand the Common folder. Noticethe Gross Internet Profit member is visible to this user.

  11. Return to the Measures node and 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.

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

  13. 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 has access to the Touring Bikes set.

  1. Users running Windows XP or Windows Server 2003. 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.
    3. Proceed to step 3.

  2. Users running Windows Vista or Windows Server 2008. Open a command window and run the following command:runas.exe /profile /user:<Domain>\user2 "<Path-to-ssms>\Ssms.exe"

    1. Replace <Domain> with the correct domain or server name.
    2. Replace <Path-to-ssms> with the complete path to ssms.exe, the executable file for SQL Server Management Studio.
      Note:
      Windows Vista and Windows Server 2008 no longer have the Run as option when you right-click an application.

  3. Ensure that Server type is set to Analysis Services, then connect to the Server name, localhost.

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

  5. Connect to the Server name, localhost.

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

  7. In the left pane of the MDX tab, in the Cube list, select the Adventure Works cube if it is not already selected.

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

  9. Expand the Measures node, expand the Internet Sales node, expand the ISV_1 folder, and expand the Common folder. Notice the the Gross Internet Profit member is also visible to this user.

  10. Return to the Measures node and 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.

  11. Return to the Measures node and expand the Exchange Rates measure group, expand the ISV_1 folder, and then expand the Finance folder to see the Rate Difference member.

    Notice that for user2 only, the Exchange Rates measure group has an ISV_1 folder.

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

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

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

  1. Users running Windows XP or Windows Server 2003. 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 user3. Make sure that the domain or server name is correct.
    3. Proceed to step 3.

  2. Users running Windows Vista or Windows Server 2008. Open a command window and run the following command:runas.exe /profile /user:<Domain>\user3 "<Path-to-ssms>\Ssms.exe"

    1. Replace <Domain> with the correct domain or server name.
    2. Replace <Path-to-ssms> with the complete path to ssms.exe, the executable file for SQL Server Management Studio.
      Note:
      Windows Vista and Windows Server 2008 no longer have the Run as option when you right-click an application.

  3. Ensure that Server type is set to Analysis Services, then connect to the Server name, localhost.

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

  5. Connect to the Server name, localhost.

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

  7. In the left pane of the MDX tab, in the Cube list, select the Adventure Works cube, if is not already selected.

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

  9. Expand the Measures node, expand the Internet Sales node, expand the ISV_1 folder, and expand the Common folder. Notice that the Gross Internet Profit member is visible to this user.

  10. Return to the Measures node and 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.

  11. Return to the Measures node and expand the Exchange Rates measure group, and verify that there is no ISV_1 folder in this group.

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

  12. Return to the Measures node and 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.

    Notice that for user3 only, the Reseller Orders measure group has an ISV_1 folder.

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

  14. Expand the Product dimension, expand the ISV_1 folder, and then expand the Marketing folder to see the All Terrain Bikes set.

    While user3 has access to the All Terrain 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 has access to the Mountain Bikes set in the Finance folder. It is user2 who has no access to the All Terrain Bikes set.

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

  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.

  1. In the menu bar, select Debug, and then select Attach to process.

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

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

  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.

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

  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.

  6. Close SQL Server Management Studio.

  7. Restart the service

  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.

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

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Processing
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker