Export (0) Print
Expand All

Chapter 28 - A Digital Dashboard Browser for Analysis Services Meta Data

Introduction

Cc917655.spacer(en-us,TechNet.10).gif Cc917655.spacer(en-us,TechNet.10).gif

This chapter steps through creating a digital dashboard browser to view Microsoft® SQL Server™ 2000 Analysis Services meta data. Using interactive digital dashboard concepts, the components are created using Active Server Pages (ASP), Microsoft Visual Basic® Scripting Edition (VBScript), and Decision Support Objects (DSO). The completed dashboard will allow the user to connect to a selected analysis server, choose a database, and view meta data about cubes, dimensions, roles, and other objects made available by the DSO object model.

Digital dashboards are a means of assembling various pieces of HTML and scripts, called Web Parts, into a single Web interface. Web Parts are reusable components that may contain pieces of HTML, XML, or full Web pages. In this example, individual ASP files, which could function as stand-alone pages, combine into a single Web application using the dashboard as a wrapper. The Web Parts provide a means of coordinating the data within each of the pages using the Digital Dashboard Service Component (DDSC) to register for and/or raise events in each part.

In this chapter, you will learn how to:

  • Access DSO using ASP and VBScript. 

  • Create a dashboard, incorporating the ASP pages. 

  • Use DDSC in VBScript to update each part as other parts change. 

The dashboard created in this chapter displays simple top-level meta data on the Analysis server. It can easily be modified to display multiple levels of meta data and data from Microsoft SQL Server™ and Analysis Services. The Digital Dashboard Browser for Analysis Services Meta Data (MetaDataDashboard) sample files discussed in this chapter are available on the SQL Server 2000 Resource Kit CD-ROM in the folder, \ToolsAndSamples\MetaDataDashboard.

Requirements

Cc917655.spacer(en-us,TechNet.10).gif Cc917655.spacer(en-us,TechNet.10).gif

The server used for this chapter will need Microsoft Windows® 2000 Server, SQL Server 2000 with Analysis Services and Digital Dashboard Resource Kit 2.01. Individual clients and the development computer require only Internet Explorer 5.5 on any supported operating system.

Windows 2000 Server

The Digital Dashboard Resource Kit 2.0 requires a Windows 2000 server running Internet Information Services (IIS) 5.0 as the Web server. Any of the Windows Server family may be used: Windows 2000 Server, Advanced Server, or Datacenter Server.

SQL Server 2000 with Analysis Services

This example uses a SQL Server Digital Dashboard, which stores dashboard definitions in a SQL Server database. Analysis Services is also required because the dashboard will be accessing meta data from the Analysis Server. It is recommended that the SQL and Analysis server installation exist on the same computer as the Web server. If a separate installation is desired, DSO must be installed on the Web server. For information on how to install DSO without installing Analysis Services, see "Redistributing Decision Support Objects" in SQL Server Books Online.

Digital Dashboard Resource Kit (DDRK) 2.01

In this chapter, you will be using a digital dashboard as the front end for viewing Analysis Services meta data. The Digital Dashboard Resource Kit (DDRK) is available for download at http://www.microsoft.com/business/default.mspx. After the DDRK is downloaded and installed, open it on the computer running Windows 2000 and IIS 5.0. Select Building Digital Dashboards then choose Install the Microsoft SQL Server 7.0 Digital Dashboard to begin installation.

Part of the installation will set up a new database on SQL Server. You will need administrator rights on your SQL Server in order to complete this step.

When installation is complete, the DDRK will automatically open the Welcome page in Internet Explorer. Select Administration to view the Administration page, where you will be creating your dashboards. You may want to bookmark this page for future reference. Alternately, you can access your dashboard's Welcome page through the http://SERVERNAME/Dashboard, where SERVERNAME is the name of your Web server.

Internet Explorer 5.5

While the DDRK requires Internet Explorer 5.0 and later, certain Web Parts in this dashboard will only function on Internet Explorer 5.5.

DDSC Versions

When you open a DDRK page for the first time on a client computer, the Microsoft Digital Dashboard Service Component (DDSC) will be installed. It is important that the correct version of the DDSC be installed; an incorrect version can cause various problems. One version in particular raises display issues with embedded content in Web Part Properties.

If the DDSC is installed while opening your new dashboard page (from DDRK 2.01), you should have the correct version. However, if you need to check the version of DDSC, open \WINNT\Downloaded Program Files, right-click on the DDSC Class file, and choose Properties. On the Version tab, under Version, you should see 2000,0,176,0, the version shipped with 2.01. If you have another version, and wish to replace it, right-click on the DDSC Class file again and click Remove. Then, open your dashboard page again. The correct version will then be installed.

Setup

Cc917655.spacer(en-us,TechNet.10).gif Cc917655.spacer(en-us,TechNet.10).gif

Before creating your dashboard, you will need to update the Web server with the appropriate settings and files.

Copy Files

The Digital Dashboard Browser for Analysis Services Meta Data (MetaDataDashboard) sample files discussed in this chapter are available on the SQL Server 2000 Resource Kit CD-ROM, in the folder \ToolsAndSamples\MetaDataDashboard. This procedure places the included files into the appropriate directories:

  1. Copy the five .txt files (1_serverconnect.txt, 2_dbselect.txt, 3_collselect.txt, 4_memberselect.txt, and 5_metadata.txt) to a temporary directory. 

  2. Create a directory on the Web server. This directory will become a virtual directory in IIS. 

  3. Copy the five .asp files (Serverconnect.asp, Dbselect.asp, Collselect.asp, Memberselect.asp, and Metadata.asp) to the directory created in the previous step. 

Set Up an IIS Virtual Directory

The previously copied files now need to be accessible by IIS. To do this, set up a virtual directory:

  1. Open Internet Services Manager. 

  2. Right-click on Default Web Site and select New – Virtual Directory

  3. When prompted for Alias, type AnalysisMetaData. For Directory, type the directory you created in Step 2 of "Copy Files" above, and for Access Permissions, leave the defaults (Read, Run scripts). 

  4. Close Internet Services Manager. 

Grant Permissions

In order for IIS to access Analysis Server data, the IIS anonymous login must be a member of the OLAP Administrators group.

  1. In Control Panel, in Administrative Tools, open Computer Management.

  2. Expand Local Users and Groups and select Users. Make a note of the user name for the Internet Guest Account (typically IUSR_SERVERNAME, where SERVERNAME is the name of the IIS computer). 

  3. Under Local Users and Groups, select Groups, and open OLAP Administrators. Select Add to add a new user to the group. 

  4. Enter the name of the Internet Guest Account. 

You may also need to stop and restart the Web server and the Analysis server for the new security settings to take effect. To do so:

  1. In Control Panel, in Administrative Tools, open Computer Management

  2. Expand Services and Applications, and select Services.

  3. Right-click World Wide Publishing Service and click Restart

  4. Right-click MSSQLServerOLAPService and click Restart

There may be security concerns regarding the use of the anonymous login within the OLAP Administrators group. It is possible to set up IIS so your digital dashboard uses a different anonymous login, or requires Windows Authentication. See your Windows 2000 documentation for more information.

Creating the Digital Dashboard

Cc917655.spacer(en-us,TechNet.10).gif Cc917655.spacer(en-us,TechNet.10).gif

This section outlines setting up the digital dashboard and incorporating various Web Parts. The text files referred to in this section are the files you copied to your temporary directory earlier in this chapter.

Set Up the Dashboard

The first phase in creating your digital dashboard is defining the dashboard itself. To accomplish this, follow these steps:

  1. Open the DDRK Administration screen using the bookmark you saved earlier. (The URL should look something like this: http://SERVERNAME/dashboard/dashboard.asp?DashboardID=http: //SERVERNAME/Sqlwbcat/Welcome/Administration/.) 

  2. Select Sqlwbcat under Dashboard View and click New

  3. Select the new dashboard (for example, NewDashboard1). 

  4. Under Dashboard Properties, enter a name of AnalysisMetaData and a title of Analysis Meta Data.

  5. Leave the defaults for the remaining properties and choose Save

Create the ServerConnect Web Part

This procedure adds the first Web Part, called ServerConnect:

  1. Under Web Part List, click New

  2. Enter the following information inthe General section of Web Part Properties

    Name: ServerConnect 

    Title: Connect to Server 

    Position on the Page: Left column 

    Position within the Column:

    Fixed Size: No for both width and height (other parts will use fixed height). 

  3. In the Advanced section of Web Part Properties, copy the contents of 1_serverconnect.txt into the Embedded Content text box, replacing the string "SERVERNAME" with the name of the Web server. 

  4. Turn off (uncheck) Allow users to remove this Web Part from the dashboard and Allow users to minimize this Web Part on the dashboard

  5. Click Save to save the Web Part. 

Create the DBSelect Web Part

This procedure adds the second Web Part, called DBSelect:

  1. Under Web Part List, click New

  2. Enter the following information in the General section of Web Part Properties

    Name: DBSelect 

    Title: Select Database 

    Position on the Page: Left column 

    Position within the Column:

    Fixed Size: fixed height of 85 pixels 

  3. In the Advanced section of Web Part Properties, copy the contents of 2_dbselect.txt into the Embedded Content text box, replacing the string "SERVERNAME" with the name of the Web server. 

  4. Turn off (uncheck) Allow users to remove this Web Part from the dashboard and Allow users to minimize this Web Part on the dashboard. 

  5. Click Save to save the Web Part. 

Create the CollSelect Web Part

This procedure adds the third Web Part, called CollSelect:

  1. Under Web Part List, click New

  2. Enter the following information in the General section of Web Part Properties

    Name: CollSelect 

    Title: Select Collection 

    Position on the Page: Left column 

    Position within the Column:

    Fixed Size: fixed height of 85 pixels 

  3. In the Advanced section of Web Part Properties, copy the contents of 3_collselect.txt into the Embedded Content text box, replacing the string "SERVERNAME" with the name of the Web server. 

  4. Turn off (uncheck) Allow users to remove this Web Part from the dashboard and Allow users to minimize this Web Part on the dashboard

  5. Click Save to save the Web Part. 

Create the MemberSelect Web Part

This procedure adds the fourth Web Part, called MemberSelect:

  1. Under Web Part List, click New

  2. Enter the following information in the General section of Web Part Properties

    Name: MemberSelect 

    Title: Select Collection Member 

    Position on the Page: Left column 

    Position within the Column:

    Fixed Size: fixed height of 85 pixels 

  3. In the Advanced section of Web Part Properties, copy the contents of 4_memberselect.txt into the Embedded Content text box, replacing the string "SERVERNAME" with the name of the Web server. 

  4. Turn off (uncheck) Allow users to remove this Web Part from the dashboard and Allow users to minimize this Web Part on the dashboard

  5. Click Save to save the Web Part. 

Create the MetaData Web Part

This procedure adds the fifth and final Web Part, called MetaData:

  1. Under Web Part List, click New

  2. Enter the following information in the General section of Web Part Properties

    Name: MetaData 

    Title: Meta Data 

    Position on the Page: Right column 

    Position within the Column:

    Fixed Size: fixed height of 485 pixels 

  3. In the Advanced section of Web Part Properties, copy the contents of 5_metadata.txt into the Embedded Content text box, replacing the string "SERVERNAME" with the name of the Web server. 

  4. Turn off (uncheck) Allow users to remove this Web Part from the dashboard and Allow users to minimize this Web Part on the dashboard

  5. Click Save to save the Web Part. 

Test the Dashboard

Open your new digital dashboard (the URL should look something like this: http://SERVERNAME /Dashboard/dashboard.asp?DashboardID=http://SERVERNAME/Sqlwbcat/AnalysisMetaData). All five ASP files should load in their separate Web Parts. If they do not, verify that you have replaced the string "SERVERNAME" with the name of your Web server in the embedded content of each of your Web Parts, and that the virtual directory AnalysisMetaData is set up and available.

Note There is an issue with the height and width properties of Web Parts that may affect your dashboard. For more information see "Sizing of Web Parts" in the "Known Issues" section below.

Using the Dashboard

Cc917655.spacer(en-us,TechNet.10).gif Cc917655.spacer(en-us,TechNet.10).gif

Initially, only the first Web Part, titled "Connect to Server," is available when you first open the page. This is because you must log in before you can perform any other tasks. To log in to Analysis Services using the dashboard, enter the name of the Analysis server into that first Web Part. Your Web server will then log in and display connection status. If there is a problem with the analysis server or the name given, an error message will appear, giving you the opportunity to fix the problem and try again.

The remaining Web Parts become available as you progress down each Web Part. The database selection part becomes available after you log in. The collection part becomes available after you select a database, and the collection member part displays after you have chosen a collection. The meta data itself will appear only after you have made selections in all the other Web Parts.

Sample Files

Cc917655.spacer(en-us,TechNet.10).gif Cc917655.spacer(en-us,TechNet.10).gif

Sample code has been provided to assist you in creating a digital dashboard to access your analysis server. Each ASP file is placed in a single Web Part to perform a specific function. The first connects to the analysis server, and the second retrieves the databases available on that server. The third displays the available collections (data sources, cubes, etc.) while the fourth retrieves the members of the selected collection. The fifth and final page displays the meta data itself, based on the information chosen in the previous four pages. All five parts are displayed in a single digital dashboard. The DDSC coordinates the five parts so a change in one part refreshes the other parts as required. This refresh ensures that each part (specifically, the ASP file within each part) is updated with the most recent information.

Text Files (Embedded Content)

Each text file contains HTML and VBScript code that displays the ASP file and sets up DDSC to refresh the page as individual parts change. The first portion of each file includes HTML that places an individual ASP file in an IFRAME. This is an example from the serverconnect.txt file:

<IFRAME ID="ConnectFrame_WPQ_" 
SRC="http://SERVERNAME/AnalysisMetaData/serverconnect.asp" HEIGHT="100%" WIDTH="100%" 
NAME="ConnectFrame"></IFRAME>

The ID attribute identifies the frame to the DDSC. The _WPQ_ (Web Part Qualifier) suffix on the ID allows for unique identification of the frame within the Web Part; _WPQ_ becomes a unique number for the part assigned by the DDSC. This is useful if there are multiple parts with identical object or variable names: ConnectFrame_WPQ_ becomes ConnectFrameWPQ1 in one part, and if it is repeated in another part, it could become ConnectFrameWPQ2 for that part. This helps avoid confusion when there are many parts with many objects included in a single dashboard Web page.

The HEIGHT and WIDTH attributes tell the frame how much of the page or part to take up. In this example, they are both 100 percent; however in some of the other files the height is reduced to 85 percent. This, combined with the height property on the dashboard administration page, helps fit all of the Web Parts onto a single screen.

The second portion of each text file contains script that sets up a raise event for the DDSC:

<SCRIPT FOR="ConnectFrame_WPQ_" LANGUAGE="VBScript" EVENT="onreadystatechange">
Dim strConnected
strConnected = 
document.frames("ConnectFrame_WPQ_").document.forms("frmConnect").item("txtConnected").
value
If strConnected = "Yes" Then
DDSC.RaiseEvent "urn:Change", "onUpdate"
End If
</SCRIPT>

Any time the frame raises the onreadystatechange event (for instance, the user submits a form within the ASP file) the script raises another event using DDSC.RaiseEvent. In this case, it raises the event onUpdate. Any part registered for the onUpdate event would then trigger any code written for that event. All text files except metadata.txt contain this script (the MetaData part cannot be modified by the user, so it will never raise an event).

The third portion of the file contains script that registers for a DDSC event:

<SCRIPT language="VBScript">
Sub RefreshPage_WPQ_ (ByVal param)
location.reload(True)
End Sub
DDSC.RegisterForEvent "urn:Change", "onUpdate", GetRef ("RefreshPage_WPQ_")
</SCRIPT>

This sets the part up to "listen" for an event. When any part raises the onUpdate event, this part will run a subroutine defined within the script (for example, RefreshPage_WPQ_). For this dashboard, only the ServerConnect part registers. This is because the subroutine refreshes the entire page, including all parts. If separate effects were desired for individual parts, then each part could use DDSC.RegisterForEvent.

Note the use of the ByVal param parameter when declaring the RefreshPage_WPQ_ function, and the use of GetRef in DDSC.RegisterForEvent when calling that function. Both of these are required in VBScript in order for RegisterForEvent to work (unlike JavaScript, which does not require this syntax).

ASP Files

Each of the five included ASP files has a specific function within the meta data browser application. Separate ASP files are used in this example to illustrate how each can be incorporated into individual Web parts and still work together. Each part assesses the state of other parts using ASP session variables and displays its own content as appropriate. For example, the Memberselect.asp file contains code that displays grayed-out text until a certain condition is met; that is, when the user selects a collection in the Select Collection part. When the user does this, Collselect.asp stores the collection type in an ASP session variable. Upon refresh, Memberselect.asp detects this variable and displays a list of collection members for the user to select.

Each ASP file is discussed below, and each is written using VBScript. The code, with full comments, can be viewed by opening the files in any text editor. For information about the DSO object model used in these ASP files, see "Decision Support Objects" in SQL Server Books Online.

Serverconnect.asp

This file contains code that logs into the analysis server. A form with a single text box and button is displayed, allowing the user to enter an analysis server name. If the name is blank, or an error occurs when trying to connect, the user is alerted and the form is displayed again. At this point, a value of "No" is stored in the ASP session variable strConnected. As long as this value is "No," Serverconnect.asp will continue to display the server form.

Only when a successful connection is made will the server name be stored in an ASP session variable named dsoServer. At this time, a value of "Yes" is stored in ASP session variable strConnected, and three new variables (strSelectDB, strSelectColl, and strSelectMbr) are initialized for use by other parts.

Because "Yes" is now stored in strConnected, serverconnect.asp displays a connection status message instead of the server connect form. This message will continue to display as long as the session is active.

Dbselect.asp

The code on this page is designed to display a read-only page with a gray Database message until it detects a value of "Yes" in the ASP session variable strConnected, as set by the Serverconnect.asp file. When it does detect a connection has been made through this variable, dbselect.asp uses the connection stored in the ASP session variable dsoServer to retrieve a list of available databases. This list is then displayed in a combo box within a form.

When the user selects a database from this combo box, Dbselect.asp stores the name of the database in the ASP session variable strSelectDB. Since the user can select a database at any time, even after other selections have been made, the ASP session variable resets the value in strSelectMbr to prevent Metadata.asp from erroneously retrieving data from a member not belonging to the selected database. (It essentially resets the Select Member and Meta Data parts so the user has to select the member again after changing the database.)

Collselect.asp

This ASP file, like Dbselect.asp, displays a read-only page with a gray View message until it detects a value from an ASP session variable, this time from strSelectDB (as stored by dbselect.asp). Unlike the others, however, it does not retrieve or store information about the analysis server or any of its objects once this variable is detected. It simply displays a fixed list of collection types in a combo box: Data Source, Cube, Shared Dimension, Mining Model and Database Role. When the user chooses a collection type, Collselect.asp stores the selected collection type in another session variable, strSelectColl.

Memberselect.asp

This ASP file also displays a read-only page with a gray View message until it detects a value in the ASP session variable strSelectColl. When it detects a value, Memberselect.asp retrieves a list of collection members based on the values selected in previous parts, and displays these members in another combo box. When the user selects a member, this member name (for example, Sales from the Cubes collection) is stored in the ASP session variable strSelectMbr.

Metadata.asp

The Metadata.asp file is where all the previously selected information comes together. This code displays a gray no data message until a value from the final part, Memberselect.asp, is stored in the strSelectMbr ASP session variable. When it detects this variable, it displays the meta data for the selected member. It uses all the variables stored by Serverconnect.asp, Dbselect.asp, Collselect.asp, and Memberselect.asp to retrieve the appropriate information about the selections.

For example, the user may have entered DDTest as the server, FoodMart 2000 as the database, Cube as the collection, and Sales as the collection member. Using all this information, Metadata.asp displays meta data for the Sales cube in the FoodMart 2000 database of the DDTest server.

To display this information, Metadata.asp code uses the various DSO object properties to create a string. The composition of this string is based on the selected collection type, since the available meta data can differ between collection types. For example, the EstimatedSize property can apply to cubes, but not data sources, so the string built for data sources cannot include this property. There are a few properties that are only valid if another property contains the appropriate value; for instance, a cube's LastProcessed property is not available if the cube's State property is 0 (never processed). If you attempt to access LastProcessed for a cube that has not been processed, an error will be generated. It is important to understand the DSO object model in order to avoid these situations.

Another factor to be aware of is that VBScript does not support the use of enumerations and constants available from the DSO object model. This means that the integer values stored in some properties must be translated within the code itself. For example, the SubClassType property is used often throughout Metadata.asp. In a language like Visual Basic, you could use the constants like sbclsRegular and sbclsVirtual to help read the DSO properties. However, since VBScript does not support these constants, you must use the integer values directly (for example, 0 for sbclsRegular and 1 for sbclsVirtual).

Known Issues

Cc917655.spacer(en-us,TechNet.10).gif Cc917655.spacer(en-us,TechNet.10).gif

Unable to Connect to the Registry

IIS may have difficulty accessing the Analysis Server if IIS is controlling the anonymous login password. In this case, the following error may occur:

DSO (0x80040031)
Unable to connect to the registry on the server (SERVERNAME), or you are not a member of the OLAP Administrators group on this server.

This error occurs because the Internet Guest Account used by IIS cannot access the Analysis Server hidden share or registry settings. The most likely cause is lack of permissions or logon failure by IIS.

To avoid the error, ensure that the Internet Guest Account (a logon typically beginning with "IUSR_") on the IIS server is a member of the OLAP Administrators group. Turn off the Password Synchronization or Allow IIS to Control Password option for the account used for anonymous access. For more information on this issue, see KnowledgeBase article 216828: "Password Synchronization/Allow IIS to Control Password May Cause Problems" at http://support.microsoft.com.

Sizing of Web Parts

There is an issue in version 2.01 of the DDRK where the value of the height property of the Web Part may be saved erroneously to the width property. This results in an incorrectly sized Web Part. If this occurs, open the Web Part in the dashboard Administration page, reset the width property to No, and enter the correct size again into the height property. Save the Web Part, then reload the dashboard page to view the change to your Web Part.

Cc917655.spacer(en-us,TechNet.10).gif

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft