SQL Server Digital Dashboard
DDRK Documentation

SQL Server Digital Dashboard provides services for storing and accessing Web Parts and digital dashboards. As described in the Building and Deploying Digital Dashboards document, you can store the parts and dashboards in a variety of store modules. Microsoft® SQL Server™ is just one of the solutions you can use. This document contains information about the SQL Server Digital Dashboard, available in release 2.0 of the Digital Dashboard Resource Kit (DDRK). It contains the following sections:

Product Overview and Architecture

Feature Summary

Before You Start

Installing SQL Server Digital Dashboard

Troubleshooting SQL Server Digital Dashboard Setup

Creating and Accessing a Digital Dashboard

Managing Security on the SQL Server Digital Dashboard

Using Web Folders with SQL Server Digital Dashboard

Personalizing Web Parts and Dashboards

Finding More Information

Product Overview and Architecture

SQL Server Digital Dashboard provides storage and access service for Web Part and Digital Dashboard definitions. Two integrated components provide these services:

·         A Web Part catalog database. This is a SQL Server database (version 7.0 or later) that stores dashboard and part definitions and properties, including user-specific properties that determine how and whether a dashboard is rendered.

·         Web Part catalog server. This is a Microsoft Internet Information Services (IIS) extension that serves as an access layer between the database and IIS. All calls that retrieve, update, and store definitions are routed to and transformed by this layer into a format that is understood by the underlying SQL Server engine.

Whether you choose SQL Server Digital Dashboard or some other solution, the store module of a digital dashboard implementation is transparent to the dashboard user. However, each store module supports a slightly different feature set. Your choice of a store module can determine the kinds of features that you are able to expose on your dashboards.

The connection between a digital dashboard and the store module is handled by the dashboard factory. The dashboard factory connects to the store module to retrieve dashboard and part definitions when it renders the page. You can also use the Digital Dashboard Services Component to access the catalog database.

The following diagram shows how product components fit together.

Web Part Catalog Database

The Web Part catalog database stores catalog items, folders, and properties. The database is structured such that catalog items are exposed as files and folders below an IIS virtual root in a user defined namespace.

During installation, the Web Part catalog database is populated with the following tables.

Table

Description

AccessControl

Stores the Microsoft Windows NT® 4.0 or Windows 2000® security descriptors associated with a catalog item URL.

CatalogConfiguration

Stores database name and version information.

CatalogItem

Contains a definition for each item in the database.

Content

Stores content of a Web Part or dashboard. Content is stored as a binary large object (BLOB) value.

ContentClass

Defines the third-party extension that can be assigned to catalog items. The catalog server passes files with this extension to the handler registered by the third-party.

CatalogItemProperty

Associates properties with specific catalog items. This table is used to support property name and category searching.

Profile

Stores profile data that defines personalized dashboards and Web Parts.

PropertyDef

Defines the property set supported by the Web Part catalog server.

URL

Maintains path information for URL based access to items. It also represents the hierarchy of collections and items. Separating access from content allows items to exist in multiple places in the hierarchy.

When users and designers add, customize, or remove definitions using the standard approaches explained in Building Web Parts for the Digital Dashboard and Building and Deploying Digital Dashboards, the underlying store is updated accordingly.

When accessing the catalog database, always access it through the dashboard interface (that is, through your dashboard virtual root directory or through WebDAV commands). Direct access to the Web Part catalog database using SQL Server is not recommended. There is limited support for referential integrity, and you bypass the security and data management functionality of the catalog server by accessing the database with other tools.

Web Part Catalog Server

The Web Part catalog server supports the HTTP 1.1 and WebDAV methods used to manipulate Web Parts and dashboards. During installation, a virtual directory is created to expose the catalog server namespace. All access to the catalog server is subsequently made by way of the namespace. When the namespace is accessed, the catalog server is activated to process and transform requests to and from a dashboard.

Accessing Web Part Catalog Server

An easy way to view catalog contents is through Web folders. Web folders support the ability to browse and select Web Parts and dashboards through standard Microsoft Windows® user interface (UI) objects. Using Web folders eliminates the need to access the database directly. For more information, see Using Web Folders with SQL Server Digital Dashboard.

In addition to Web folders, you can use the sample digital dashboard that installs with SQL Server Digital Dashboard to update the database. For more information, see Creating and Accessing a Digital Dashboard.

Feature Summary

SQL Server Digital Dashboard supports the following features:

·         Standard Web Part and dashboard storage. Although internal implementation details vary, storing Web Parts and dashboards in a SQL Server Web Part catalog is equivalent to storage provided by other store modules that support dashboards.

·         Security. You can regulate access to Web Parts and dashboards that reside in a SQL Server Web Part catalog database.

·         Personalization. User-specific properties can be used to customize dashboard settings and layout.

·         Keyword and property searches. Users can search for Web Parts and dashboards based on keyword and standard property searches.

Before You Start

The dashboard factory provided in the DDRK 2.0 can use either Windows 2000 or Microsoft SQL Server to store and access Web Parts and dashboards. Your choice between store modules such as SQL Server, Windows 2000, and Exchange 2000 Web Store should be based on your existing investment in technology and the feature set you require. If you already have SQL Server, you can leverage your existing investment by using it to store Web Parts and dashboards. Similarly, if you own Exchange and expect to upgrade to Exchange 2000, you can use that technology to achieve the same objective. Currently, you cannot combine Web Parts from different store types on the same dashboard. However, you can copy Web Parts from one store to another so that each dashboard can contain the parts you want.

Additional prerequisites are noted in the next section. Be sure to read all of the installation instructions before you start.

Installing SQL Server Digital Dashboard

SQL Server Digital Dashboard requires SQL Server version 7.0 or later running on Windows 2000, with IIS 5.0 Web server software. You must use Microsoft Internet Explorer 5.0 or later to view digital dashboards that you create. Be sure to review the system requirements to find out about additional requirements.

The SQL Server Web Part catalog server and the catalog database must reside on same computer. Setup does not support the distribution of these components across multiple computers.

Before you install SQL Server Digital Dashboard, create an empty database so that you can keep your dashboard resources separate from your production data. During installation, Setup adds tables, stored procedures, and triggers to the database you specify.

During installation, you will be asked to provide the following information.

·         The name of the computer running the SQL Server instance and an authentication mode.

·         The name of the Web root that users will use to attach to a dashboard. The dashboard factory files will be stored in the Web root that you specify. The default virtual root name is Dashboard, on the Default Web Site server.

·         The name of the Web root that used to access the catalog. The default name is Sqlwbcat.

·         The name of the database. You must specify an existing database.

Your responses are recorded in the IIS metabase and used by the catalog server to locate the dashboard viewer and database. If you modify virtual root directory names, or the database name and location, you should reinstall your software to update the IIS metabase settings.

If you need to reinstall, remove the prior installation first. Use Remove All in the Setup program.

To install the SQL Server Digital Dashboard

·         On the Building Digital Dashboards page of the DDRK, select Install the Microsoft SQL Server 7.0 Sample Digital Dashboard. You can also run DDRK_SQL.msi from the CD-ROM. An installation wizard guides you through the process.

The installation program copies the following files to your computer.

Files

Description

Sqlcatalog.sql

A SQL Server script file that creates tables for storing definitions. This file is executed by Setup.

Sqlwbcat.dll

The SQL Server Web Part catalog server. During installation, this file is copied to the C:\Inetpub\Wwwroot\Scripts folder and registered in the IIS metabase.

\Dashboard\*.*

The dashboard factory. This is a set of files that support dynamic rendering at run time. During installation, these files are copied to the \Inetpub\Wwwroot\Dashboard folder (or to another location that you specify).

Troubleshooting SQL Server Digital Dashboard Setup

After installation completes, the SQL Server Digital Dashboard is fully configured. However, if you encounter problems while running the software, you should review the error file and verify IIS settings to ensure that the configuration is accurate and complete.

Reviewing the Log File

When IIS loads the Web Part catalog server, a log file is created to store notifications and errors that occur. The name and location of the log file is defined in IIS as a WWW services property. To view log information, right-click Properties on Default Web Site. On the Web Site tab, click Properties. The location and format of the file name is indicating By default, the log name is composed of an “ex” prefix and the date of creation in year-month-day format. If the file does not exist, the catalog did not load. In this case, you should verify your IIS settings.

Verifying IIS Settings

Because the Web Part catalog server is accessed through an IIS extension, it is exposed as a virtual directory in your Web root directory. The catalog server must reside in its own virtual directory.

By default, the location is C:\Inetpub\Wwwroot\Sqlwbcat. Using IIS, verify that your virtual directory uses the following settings: Execute Permissions set to Scripts and Executables, and Application Protection set to Low (Pooled). Configuration should have an Application Mapping that defines an asterisk (*) extension for Sqlwbcat.dll.

Verifying Dashboard Factory Configuration

The dashboard factory is also installed as a virtual directory. By default, it is located at C:\Inetpub\Wwwroot\Dashboard. Settings for this directory should include read permissions, execute permissions should be set to Scripts Only.

Creating and Accessing a Digital Dashboard

After you install SQL Server Digital Dashboard, the default sample dashboard loads automatically. This dashboard contains a Welcome message part. Exploring this sample dashboard can help you learn the basics of using, customizing, and creating your first digital dashboard. If you used the default selections during installation, you can reload the default dashboard by typing the following address: http://YourServerName/dashboard.

Using the sample dashboard, you can create new Web Parts and dashboards or customize existing parts and dashboards using the Content, Settings, and Layout pages. For more information, see Building Web Parts for the Digital Dashboard

Web clients that run digital dashboards do not need to install SQL Server Digital Dashboard. Using the browser, users can access digital dashboards and select new dashboards and Web Parts from the SQL Server Digital Dashboard that you install. Customizations that users subsequently make are saved to the catalog database on your server.

To access a dashboard that is stored in the SQL Server Web Part catalog database, specify the following address: http://YourServerName/dashboard/YourDashboardName. In this case, "dashboard" represents the virtual root for your SQL Server Web Part catalog installation.

Managing Security on the SQL Server Digital Dashboard

Because dashboards can provide sensitive or confidential information to end users, you may want to restrict access to certain Web Parts or dashboards. For this reason, SQL Server Digital Dashboard supports integrated user, group, and role level security, including the ability to set read, write, update and delete permissions on catalog items.

By default, all users can access the Web Part catalog server with Administrator privileges, with the ability to fully modify or delete items in the Web Part catalog database. This is designed to be consistent with Windows 2000. If you prefer a higher level of security, you should revise the security settings using the Administration dashboard.

In SQL Server Digital Dashboard, security applies to a dashboard, a Web Part, and content (that is, the HTML, XML, JScript®, and so on that contains rendering information about the part). It does not apply to linked content. The security strategies you set in place for SQL Server are separate from those you establish for dashboards and parts. Security for Web Parts is one piece of a larger security framework. For Web Parts, security is the ability to explicitly control the following:

·         Whether the Web Part can be viewed

·         Whether the Web Part can be modified

In the SQL Server Digital Dashboard, security is enforced by the Web Part catalog server, using Access Control Lists (ACL). ACL-based security for Windows NT 4.0 and Windows 2000 is documented in Building and Deploying Digital Dashboards. No additional security features are implemented by way of the SQL Server engine. In fact, accessing the catalog database directly, through SQL Server, bypasses the security provided by the catalog server.

In SQL Server Digital Dashboard, access control lists are based on the URL table. If you retrieve a dashboard from a specific URL, you inherit the security that applies to that dashboard.

SQL Server login and database access is handled by the Web Part catalog server, on behalf of dashboard users. When a user accesses a dashboard, the catalog server logs on to SQL Server using a connection string that was establishing during installation. This connection string is stored in the IIS metabase. Because access to SQL Server is handled by the catalog server, users are shielded from having to know which authentication mode and log on values to use.

Using Web Folders with SQL Server Digital Dashboard

After you install the SQL Server Digital Dashboard, you can use Windows Web folders to work with the catalog and perform basic administrative tasks. You can access the Web Part catalog server using Web folders and then view database contents the server exposes.

Web folders provide a simple, UI-based way to see what items are available. You can also double-click an item to open it in a browser window. In addition, right-click commands allow you to rename, move, delete, and create items in the Web Part catalog.

To use Web folders, follow these steps.

1.      Double-click My Network Places on the desktop.

2.      Double-click Add New Network Place.

3.      Type the URL (for example, http://MyServer/sqlwbcat). This is the URL used to access the catalog. The catalog will subsequently access the database and present its contents in a Web folder.

Adding a non-Web Part item to a catalog database does not make it a Web Part because none of the properties of the Web Part schema have been populated. For more information about defining Web Parts and Web Part properties, see Building Web Parts for the Digital Dashboard and the Web Part Schema Reference.

Personalizing Web Parts and Dashboards

Personalization is a feature of the digital dashboard environment that allows users to configure dashboards, Web Parts and related content on a user-by-user basis. Personalization is supported through user-specific properties the user sets and the server maintains in the catalog database. Customized properties are applied when the page is rendered, overriding the default properties that are defined for the catalog item. For each URL, a set of default properties determines the initial presentation and behavior of the dashboard.

User-specific properties override the default properties when the page is rendered. If profile definitions are unavailable, default properties are used. If a dashboard developer or administrator changes the default global property set, those changes do not affect the personalized settings made by the user. Only security settings or functionality changes override the personalization layer of a digital dashboard environment.

When a user first accesses a dashboard, an entry is automatically made for that user in the Profiles table, which subsequently stores any custom property settings that the user defines. If the user does not personalize a dashboard, the storage is unused.

Not all properties can be personalized. For more information about customizable properties, see the Web Part Schema Reference and Dashboard Schema Reference provided in this release. The user must also have permission to personalize properties through dashboard security.

To customize a dashboard, users indicate preferences in the Content, Layout, or Settings pages. For more information about working with these pages, see Building and Deploying Digital Dashboards

Finding More Information

Additional documentation is provided in the DDRK. Documents range from highly specific reference material to general-purpose white papers that walk you through the process of design and development. Each section contains links to the documents that are most relevant to the section.

If more documentation is not what you need, you can test-drive the sample Web Parts and dashboards in the Web Part Gallery.