Profit Margin Sample

The Profit Margin sample is an event-driven Microsoft SQL Server Notification Services application that produces notifications about profit margins for individual employees and sends this data to subscribed users. This sample uses the SSAS event provider to query an SSAS database and return the results as events to the Notification Services application.

Scenario

A company has an SSAS database that is used to analyze sales performance. Employees in the Sales department can subscribe to the Profit Margin application to receive a notification when any of their resellers has exceeded their profit margin goal.

On a schedule specified in the application, the SSAS event provider runs an MDX query to gather events. When the generator runs, these events are added to a chronicle. The generator then produces notifications from the event chronicle when the following conditions are met:

• The name of the subscriber matches the name of the employee in the event.
• The reseller's gross profit margin is greater than the reseller's gross profit margin goal.

The resulting notification is formatted by using an XSL transform, and then sent to a file.

Languages

XML, XSD, XSLT, and Microsoft Visual C# or Microsoft Visual Basic.

Features

The SSAS sample uses the following features of Notification Services:

Prerequisites

Before you run this sample, make sure that the following software is installed:

• Microsoft SQL Server 2005, including the following components:
  • Database Engine.
  • Notification Services.
  • SSAS.
  • SQL Server Management Studio.
  • Business Intelligence Development Studio.
  • The AdventureWorksDW database. This database is included with SQL Server 2005, and is also available at the SQL Server Developer Web site. For more information, see Running Setup to Install AdventureWorks Sample Databases and Samples.
  • Notification Services samples. These samples are included with SQL Server 2005. You can download the latest version of the samples at the SQL Server Developer Web site.
  • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See Installing the .NET Framework SDK.

Building the Sample

To build the sample, you will need to do the following:

• Deploy the SSAS database.
• Create the instance of Notification Services.
• Register the instance of Notification Services.
• Grant SQL Server and database permissions.

The following procedures show how to do each of these tasks for the Profit Margin sample.

Step 1: Deploy the Adventure Works DW OLAP database

1. Open SQL Server Business Intelligence Development Studio, click File, point to Open, and then click Project/Solution.

2. Browse to C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project, select the Adventure Works DW.dwproj file, and then click Open.

3. From Solution Explorer, right-click Adventure Works DW and select Deploy.

Step 2: Create the instance of Notification Services

1. Open SQL Server Management Studio and connect to an instance of SQL Server.

2. In Object Explorer, right-click the Notification Services folder, and then select New Notification Services Instance.

3. In the New Notification Services Instance dialog box, click Browse and select the InstanceConfig.xml file in the Profit Margin sample's root folder.

4. In the Parameters box, enter values for the three parameters:

   • SampleDirectory is the path of the Profit Margin sample's root folder. The default path is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\ProfitMargin.
   • NotificationServicesHost is the local server name.
   • SQLServer is the name of the SQL Server instance.

5. Select the Enable instance after it is created check box.

6. Click OK..

7. When Notification Services has finished creating the instance, click Close.

Step 3: Register the instance of Notification Services

1. In SQL Server Management Studio, open the Notification Services node, right-click ProfitMarginInstance, point to Tasks, and then select Register.

2. In the Register dialog box, select the Create Windows service check box.

   The Windows service runs the instance of Notification Services on this computer.

3. Under Service logon, enter a Windows account and password. This is the Windows account under which the service will run. If you use Windows Authentication to access SQL Server, the Windows service will also use this account to connect to SQL Server.

4. If you must use SQL Server authentication to access SQL Server, select SQL Server Authentication under Authentication, and then enter a SQL Server login name and password.

   We recommend using Windows Authentication.

5. Click OK..

6. When Notification Services has finished registering the instance, click Close.

Step 4: Grant SQL Server and database permissions

1. In Object Explorer, expand the Security node.

2. If you have to create a new database login account for the Windows service, right-click Logins, select New Login, and then create the Login account:

   • To use Windows Authentication, select Windows authentication and enter the same Windows account you specified when registering the instance.
   • If you must use SQL Server authentication, select SQL Server authentication, and then enter the same SQL Server login and password that you specified when you registered the instance.

3. If the login that is used by the Windows service already has access to SQL Server, right-click the login and select Properties.

4. In the left pane of the Login dialog box, select User Mapping.

5. Grant permissions for the ProfitMarginInstanceNSMain database:

   1. In the Users mapped to this login box, select the box that is adjacent to the ProfitMarginInstanceNSMain database.
   2. In the Database role membership for: ProfitMarginInstanceNSMain box, select NSRunService.

6. Grant permissions for the ProfitMarginInstanceProfitMargin database:

   1. In the Users mapped to this login box, select the box that is adjacent to the ProfitMarginInstanceProfitMargin database.
   2. In the Database role membership for: ProfitMarginInstanceProfitMargin box, select NSRunService.

7. Click OK to apply SQL Server permissions.

8. Grant permissions for the Adventure Works DW database:

   1. In SQL Server Management Studio, connect to SSAS.
   2. Expand the SSAS Database folder, and then expand Adventure Works DW.
   3. Right-click Roles and select New Role.
   4. In the Role name box, enter ProfitMarginNS.
   5. In the left pane, click Membership and add the account that is used to run this sample's Notification Services Windows service.
   6. In the left pane, select Cubes.
   7. For the AdventureWorks Cube, click the Access field and select Read.
   8. Click OK to apply cube permissions.

9. Configure security for the Events folder:

   1. In Windows Explorer, locate the Profit Margin sample's Events folder.
   2. Right-click the Events folder, select Sharing and Security, and then select the Security tab.
   3. Click Add and add the account that is used by the Windows service.
   4. In the Group or user names box, select the account that you just added.
   5. In the Permissions for box, select the Read and Modify permissions.
   6. Click OK to apply the changes.

10. Configure security for the Notifications folder:

    1. Locate the Profit Margin sample's Notifications folder.
    2. Right-click the Notifications folder, select Sharing and Security, and then click the Security tab.
    3. Click Add and add the account that is used by the Windows service.
    4. In the Group or user names box, select the account that you just added.
    5. In the Permissions for box, select Write.
    6. Click OK to apply the changes.

Generating a Strong Name Key File

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.

Running the Sample

To run the sample, do the following:

• Start the instance.
• Add subscribers and subscriptions.
• Add an event to the SSAS data warehouse.

The following procedures show how to perform these tasks and how to view the resulting notifications.

Step 1: Start the instance

1. In Object Explorer, open the Notification Services folder.

2. Right-click ProfitMarginInstance, and then click Start.

Step 2: Add subscribers and subscriptions

1. Build the ProfitMargin or ProfitMargin_VB Visual Studio Solution

   If you are using the.NET Framework SDK, do the following:

   1. On the Start menu, point to All Programs\Microsoft .NET Framework SDK v2.0, and then click SDK Command Prompt.
   2. Locate the Profit Margin's sample's root folder. Type the following command for the default location:
      cd \Program Files\Microsoft SQL Server\90\Samples\Notification Services\ProfitMargin
   3. Type the following to build the solution
      [C#] 
      msbuild ProfitMargin.sln
      [Visual Basic] 
      msbuild ProfitMargin_VB.sln

   If you are using Visual Studio 2005, do the following:

   1. Open the solution file of your choice (ProfitMargin.sln or ProfitMargin_VB.sln).
   2. Press F6 to build the solution.

2. Run AddSubscribers.exe.

   The default location for this file is C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\ProfitMargin\AddSubscribers\language\AddSubscribers\bin\Debug.

3. Run AddSubscriptions.exe.

   C:\Program Files\Microsoft SQL Server\90\Samples\Notification Services\ProfitMargin\AddSubscriptions\language\AddSubscriptions\bin\Debug.

Step 3: Add an event to the Analysis Services data warehouse

1. Open a New SQL Server Query window in SQL Server Management Studio.

2. Open the TriggerEvent.sql file in the ProfitMargin/SQL Scripts folder to add data to the AdventureWorks data warehouse. This data will generate an event.

   Note

   If you run this script again, you must change the SalesOrderNumber by changing the SELECT @SalesOrderNumber character value to add a new sales order.

3. Reprocess the cube:

   1. In SQL Server Management Studio Object Explorer, click Connect, and then select Analysis Server.
   2. In the Connect to Server dialog box, type the server name that hosts the AdventureWorksDW cube in the Server name box, and then click Connect.
   3. In Object Explorer, open the Analysis Server node, open the Databases folder, and then locate Adventure Works DW/Cubes/Adventure Works/Measure Groups/Reseller Sales.
   4. Right-click Reseller Sales, and then select Process.
   5. Click OK to reprocess the Reseller Sales cube.

Step 4: View notifications

1. Give Notification Services approximately one minute to produce notifications.

2. In Windows Explorer, locate the Profit Margin sample's Notifications folder. You should find a file named FileNotifications.txt. This file contains the notifications.

3. Locate the folder where e-mail messages are dropped. This is typically in one of the folders in C:\Inetpub\mailroot. Depending on the SMTP server state, the notifications should be in either the Pickup folder or Queue folder. If the SMTP service is running, the messages might be moved to the Badmail folder.

Removing the Sample

Use the following procedure to remove the Profit Margin sample.

To remove the ProfitMargin sample

1. In the Object Explorer pane, open the Notification Services folder.

2. Right-click ProfitMarginInstance, and then select Stop.

3. Right-click ProfitMarginInstance, point to Tasks, and then select Unregister.

4. Right-click ProfitMarginInstance, point to Tasks, and then select Delete.

See Also

Other Resources

SQL Server Notification Services Samples
Securing Notification Services

Help and Information

Getting SQL Server 2005 Assistance

Change History