This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
.gif)
Microsoft Office 2000: Create Dynamic Digital Dashboards Using Office, OLAP, and DHTML
Todd Abel
|
Code for this article: Abel0700.exe (41KB)
Digital Dashboards provide users with one single interface through which they can view information from a variety of sources that have been chosen specifically for that user. In addition, dashboards allow a user to view the information offline, adding portability to the mix.
This article discusses options for building a dashboard based on the Microsoft Outlook folder home pages feature. It covers culling the data from disparate sources and storing it using the MSDE. It then discusses the creation of nugget definitions for structuring the data, and providing a synchronization mechanism to update to the data stores.
.gif)
digital dashboard is like a nerve center that allows you to view information consolidated from various sources. Digital dashboards are customized to allow you to access personal files, e-mail, company databases, Web sites, and moreâ€"all in one place.
A digital dashboard offers the user more than a Web portal offers. While Web portals also supply users with large amounts of information from disparate sources, the user has to figure out how to put this information together in a manner that is useful to him. A dashboard, on the other hand, is a personalized portal that provides consolidated information for an individual user. Since dashboards are based on Microsoft® Office 2000 technology, they provide analytical and collaborative tools to help knowledge workers use information to make decisions. Additionally, the information supplied by a dashboard is available offline as well as online, which isn't the case with a Web portal. For more information on dashboards, see https://www.microsoft.com/digitaldashboard.
In this article I'll discuss different ways to build a digital dashboard, covering the pros and cons of various implementations. I'll also explore how you retrieve and store various types of data on your dashboard and how data manipulation takes place.
Design Considerations
A dashboard consists of a number of nuggetsâ€"chunks of HTML and script that make the HTML specific to that particular client. Each nugget displays information to the user in its own way. For example, a weather nugget would display weather information in a format that is appropriate for weather-related data. Since each user's idea of useful information is different, nuggets have the ability to be customized at a user's discretion. Users should be able to go to a nugget repository and select the nuggets they want to run, then drag them into the dashboard and use them. This isn't in place right now, but a flexible framework currently gives users the ability to choose nuggets and perform some simple customization.
A number of different dashboard architectures have been put forward, but the Digital Dashboard Starter Kit (https://www.microsoft.com/digitaldashboard) was the first. The framework behind this dashboard is based on the use of the Microsoft Outlook® folder home pages feature. A folder home page is a way to associate a Web page with a folder so that when the user selects the folder, the right pane of Outlook displays a Web page rather than individual messages. Using this technique, each Web page is coded to display information based on the contents of the folder. This method primarily employs client-side code interacting with the Outlook object model. For instance, you would embed the Outlook View control (more about this later) in the nugget to display a view of the contents of an Outlook folder.
Deployment to a large number of people is pretty easy with this model. Just put the pages on a server, set the folder home pages for each of the folders that have content, and off you go. The unfortunate thing about this design is that when you are disconnected from your network, you have a static view of the information. This static view is generated only if you configure each folder home page to be available for offline use and you synchronize the folder home pages before unplugging. Outlook only caches the last version of the HTML for the folder when it's taken offline. In this case, however, the user can't interact with the data. To get around this, you can embed the information in Office documents, then use the Office Web Components (I'll cover these later) to display it so the user can interact with the content.
The problem with using Office documents to store content is that there can be many small changes in the document, so for large documents synchronization can take quite a while. This is especially a problem if your users are allowed to change the data. If you provide read/write access while disconnected, you must implement some form of conflict resolution to merge the changes back into the online data. Because of the offline capabilities, user customization of a site implemented this way can be more difficult to manage. User customization is accomplished using the same techniques that you use to customize a Web site. Products like the Microsoft Site Server Membership service would be ideal for providing a nicely customizable solution. You could also store a cookie on the client that tells the server which nuggets the user is interested in including in her dashboard.
If you don't want to use static Web pages, you could abstract a nugget into a nugget definition. A nugget definition is the code that is required to provide the contentâ€"it defines the presentation. The code that makes the nugget function will be wrapped around the nugget definition later to make a functioning nugget. You then store the definitions of the nuggets on the client machine or in a server-based repository. Using this architecture, a central repository to hold the nugget definitions and a synchronization process to get them to the client will be required to provide central management. You will also need at least one HTML page that knows how to load and interact with the nugget definitions. This HTML page performs a number of critical functions within the dashboard and it provides a framework in which the nugget definitions reside. It also knows how to convert the definition of a nugget to actual HTML and script that will interact with a user.
With this architecture, users can download the nuggets that they are interested in or to which they have access. You could also set up central administration of the nugget definitions to restrict or enforce the loading of nugget definitions on the client. To implement this design, you'll need to use some kind of local store such as XML, disconnected recordsets, public folders, or the Microsoft Data Engine (MSDE) to hold the nugget definitions. MSDE ships with Office 2000 and is compatible with Microsoft SQL Serverâ„¢ 7.0. You can even use MSDE as a client for SQL Server replication. For more information on MSDE see https://www.microsoft.com/office/access/MSDtaEng.htm.
Since I'm going to use a local store for the nugget definitions, I could also store the data for the dashboard in the local store. I could then present information to a user whether they are connected or not. This approach was taken to implement a dashboard for a number of users within Microsoft. MSDE was used as the local store since some of the data I wanted to display already resided in a SQL Server database.
.gif)
Figure 1 Anatomy of a Dashboard
The dashboard shown in Figure 1 has three data sources to draw from for displaying information to the user: the Internet, Microsoft Exchange, and relational data in a SQL Server table. All of these sources are available offline as well as online. Exchange data is synchronized through the Outlook client, Internet content is cached using the Microsoft Internet Explorer caching mechanism, and since MSDE is local, all relational data is brought down to the local level using SQL Server replication. Since most of the data is stored locally in SQL Server or cached files, you get the same performance whether online or offline. The only time you have a difference in performance is when you are online and connected to public folders. In this case, online access is slower than offline access because it is necessary to connect to the Exchange server, which incurs a network latency penalty.
Implementing a Nugget
Nuggets that use information from the Internet can be pretty simple; they just refer to a URL. For instance, if you wanted to include a view of traffic you might include a link to a traffic camera such as https://images.wsdot.wa.gov/nwflow/cctv532.jpg.
This doesn't work as well if you want to include Internet content beyond a single graphic, or if you want to include just a portion of a Web page. Normally you would include a frame in the Web page and display whatever contents you wanted. In a dashboard scenario, this poses a problem because you would be displaying the entire Web page to the user, not just the traffic conditions content.
In this case you can do screen scraping of Internet sites using an ActiveX® control or script. For instance, if you wanted to supply the weather forecast from MSNBC, you would look at the source of the page, find the content that interests you, then use the WebBrowser control and the Internet Explorer Document Object Model (DOM) to extract the HTML tags that make up the content and paste them into your page. This gives you the ability to get the data dynamically when you want it. Figure 2 contains a Visual Basic®-based function, GetForecast, that does this.
In GetForecast, the first thing you do is to create a Microsoft Internet Explorer object that will be used to get the content from the target site. Next, the code ensures that the browser is online; this won't work if the user isn't connected to the Internet. Then the Internet Explorer object is set to silent mode using the Silent property so it doesn't show any dialogs or messages. After that you just have to navigate to the site using the Navigate method.
Once you navigate to the site, you have to wait until content is ready to be viewed by checking the ReadyState property. A ReadyState of 3 indicates that the page is interactive; that is, the full Web page code you want should be there, but the related images may still be downloadingâ€"but that's good enough for my purposes here. If the ReadyState property isn't interactive, then you must wait. I chose a timeout of 30 seconds before the code gives up and returns. These 30 seconds are counted in 1/2 second intervals while continuing to poll the ReadyState.
Retrieving and Storing Data
When the browser is ready to interact with the user, you can start dissecting the HTML to look for the content that you want using the DOM. Once you find the exact content you need, you can parse it using various string functions and return it to the calling code, which pastes the HTML from the content provider into its own <DIV> section in the nugget. What you end up with is an integrated nugget that provides content from some external source, without using a frame and without any extraneous content (see Figure 3).
.gif)
Figure 3 Nugget-derived Content
Of course, the downside of screen scraping is that anytime the content provider makes a change, you must also make a change to your code. Ideally, content providers will make their content available in an XML format that can be used in any application.
Exchange public folder information is probably the easiest to deal with in Outlook because e-mail is its primary focus. Public folders are designed to store unstructured data or disparate data elements. For instance, public folders are the perfect place to store documents that are meant to be shared across a team. Conflict resolution mechanisms are built into Exchange to handle situations where two people edit the same document at the same time. Additionally, you can take the content offline without much effortâ€"you just mark the folder as being available offline and Outlook takes care of the rest when you synchronize. Public folders are also good for developing form-based applications where you want the information to be entered offline or online.
Additionally, you can store many different types of tuples (sequences of data) in a folderâ€"you aren't just limited to a single tuple type as you are in a database. This means that in one message you can have properties { A, B, C } and in the next message you can have properties { A, B, D }. This is the difference between structured and unstructured data.
Of course, there are things that databases can do and public folders can't, such as transactions. In many cases (with offline data entry by a salesperson, for example), you don't need a transaction. The other implementation detail that snags public folder users is the replication of public folders across the enterprise. If you don't have a good public folder replication strategy, then don't use public folders. The effect is like that of a Usenet group on a local server that's never updated.
Using a public folder in a dashboard is very easy. You can use the Outlook View control to display the contents of the folder by pasting an HTML object reference into a nugget wrapper.
<OBJECT classid="CLSID:0006F063-0000-0000-C000-000000000046"
codeBase="outlctlx.cab"
id="ctlForecastItems" width=100%>
<param name="Folder" value="\\Public Folders\
Favorites\Dashboard\Content">
<param name="Restriction" value="[DashboardArea]='Forecast'">
<PARAM NAME="View" VALUE="All Items">
<PARAM NAME="Namespace" VALUE="MAPI">
</OBJECT>
A number of different parameters can be set with the Outlook View control. The Namespace and Folder properties are required to make the View control work. For additional information on the Outlook View control, consult the Outlook 2000 Team Folder Wizard Kit at https://www.microsoft.com/Exchange/outlook/tfintro.htm.
As you can see in Figure 3, relational data is also available to the dashboard user. For this implementation, the data was synchronized from a back-end data warehouse. I created the n-tier data warehouse by using a variety of different data sources. If the source data format doesn't fit the data format that you have in mind, then you can preprocess the data and transform it into a more useful format. The relational data is then displayed in nugget format using ActiveX Data Objects (ADO) and client script. Once the data is in the form you want on the middle-tier server, you can use SQL Server replication to synchronize the client database. Since the data resides on the local machine, you can provide very flexible views to the user in an ad hoc manner.
Since I am controlling the format of the data that resides in the data warehouse, queries can be executed ahead of time and stored in summary tables on the client, rather than querying the database each time the user wants information. The following code shows how you would design a nugget that displays headcount for the previous three-month period or for a fiscal year.
<div style="overflow:auto">
<SPAN CLASS="topBarSpan">
<SPAN valign="middle" id="hc_timeperiod"
onclick="ChangeTime();" class="btnFolder"
style="font-weight:bold">
Last Three Months</SPAN>
<SPAN id="hc_timeperiod" onclick="ChangeTime();"
class="btnFolder">Fiscal Year</SPAN>
<SPAN id="hc_District" class="btnFolder"></SPAN>
</SPAN>
<table id="hc_headcount" border="0" cellspacing="0"
cellpadding="0">
</table>
</div>
You start by creating a <DIV> tag, adding buttons (represented here by the <SPAN> tags) so the user can choose the period to view. Then you include a table to place the data in. Next, you define some script that would execute the query, then build and fill the table (see Figure 4). Of course you will have to adapt the code in Figure 4 to work with your middle-tier data. Figure 5 shows an example of a relational data nugget. You can also display relational data using the Office 2000 Web Components (OWC) by binding the PivotTable® control to a recordset.
.gif)
Figure 5 A Relational Data Nugget
File data is typically available to a dashboard user. For my dashboard this is either an Office document or an offline OLAP cube file. Both of these data types are easily displayed in a dashboard using OWC. The following code will display a chart using the ChartSpace component:
<OBJECT classid="clsid:0002E500-0000-0000-C000-000000000046"
id="chtDepthRevenueVsPlan">
<PARAM NAME="ScreenUpdating" VALUE="-1"></OBJECT>
Of course you'll have to bind some data to the chart to display the information. (For additional information about OWC, consult Dave Stearns' book, Programming Microsoft Office 2000 Web Components, Microsoft Press, 1999.) In my dashboard this technique was used to bind offline OLAP cube files to charts and pivot tables.
As I mentioned earlier, the nugget definitions for the dashboard were stored on the client machine and dynamically loaded in response to a user action. To do this I need one HTML page that knows how to display the basic UI framework and load the nuggets from the client store. This HTML page contains some HTML for the menu along the top, a stock ticker, and a single <DIV> that is used to hold all the nugget content:
<HTML>
<HEAD>
. . .
</HEAD>
<BODY>
<DIV ID="mainMenu"></DIV>
<DIV ID="allNuggets"></DIV>
<SCRIPT LANGUAGE="javascript">
</SCRIPT>
</BODY>
</HTML>
In the main section of the script, the main menu definition is read and loaded dynamically using Dynamic HTML. Then, based on the button the user clicked, the loadNuggets function goes out and loads the nugget definitions and displays the nuggets named in the allNuggets <DIV>.
Function loadNuggets(iMenuButton) // This is not valid JavaScript
// code, just pseudocode
{
var sHTML = "";
// Load the nugget definitions from the local store
// for the menu button that the user clicked
for(var i=0; i<iNumNuggets; i++)
{
sHTML += StartNuggetWrapper(); // Wrap using generic code
sHTML += Nugget.html + Nugget.script;
sHTML += EndNuggetWrapper(); // End of code wrapper
}
document.all.allNuggets.innerHTML = sHTML;
}
Each nugget shares some amount of generic code, and in my dashboard this was wrapped around the content code just before being displayed to the user. This is accomplished using two helper functions, StartNuggetWrapper and EndNugget-Wrapper. StartNuggetWrapper takes four arguments, which are listed in Figure 6.
The main portions of the nugget content wrapper consists of two <DIV> tags (see Figure 7). One of the tags defines the title bar, while the other one contains the content. The first tag contains a table that makes the title layout easier. It also contains a <SPAN> tag that is used to hold the title name and to maximize functionality. The table also holds the minimize button that is used to minimize and restore the nugget. This is accomplished by simply hiding the second <DIV> tag that holds the nugget content. The EndNuggetWrapper function simply closes the two <DIV> tags that are defined in StartNuggetWrapper:
function EndNuggetWrapper()
{
return "</div></div>";
}
The toggleMaxMin function (see Figure 8) allows the nugget to be maximized in the right pane of Outlook. This function finds the element that caused the event to be fired, then finds the nearest <DIV> and <TABLE> that are parents of the element. Once found, if the state of the nugget is already maximized, then it is restored to its original position and size; otherwise it is set to the maximum size of the pane.
The FindParentElement function is used to find a parent element of a given type from a child element. It crawls up the document object hierarchy until it gets to the top or finds a matching tag.
function FindParentElement(child, sTag)
{
try
{
while(null != child)
{
child = child.parentElement;
if (sTag == child.tagName)
return child;
}
}
catch(e)
{
}
return null;
}
OLAP Files
One of the most powerful features that you can provide in your dashboard is the ability for a user to look at financial data in a way that is meaningful to them. OLAP cube files and the PivotTable service will let you do just that. For additional information on OLAP, see "Manipulate and Query OLAP Data Using ADOMD and Multidimensional Expressions, Part II" in the September 1999 issue of Microsoft Systems Journal.
To provide the same information when disconnected from the network, you would use an offline cube file, which is part of a view of a cube. Using an offline cube file is really pretty easy. First you declare a pivot table object using an <OBJECT> tag. Then you write script to insert the rows, columns, and data values that you want, as shown in Figure 9. With minimal code you get some impressive results. Figure 10 shows a pivot table that displays orders by time period and store and allows filtering by products.
.gif)
Figure 10 A Dynamic Pivot Table
The more difficult part is to generate the .cub file. The sample code in Figure 11 was taken from the OLAP Programmers Guide chapter "PivotTable Service" . This code will generate an offline cube file called c:\warecube.cub from the OLAP sample FoodMart. You must have a DSN called FoodMart to get this sample code to work.
Synchronization of Files
The last piece of the dashboard implementation puzzle involves getting data files from a server to the client. Windows® 2000 has the ability to mark a file as being available offline much as you would with an Outlook folder. When you are connected, you use the network version of the file directly; when offline, you use a local version. This can be a problem, especially for users who RAS in, because some of the OLAP files I used are 15 to 20MB, which is too large to pull back and forth across a phone line.
Internet Explorer 5.0 allows you to take Web pages offline. This sounds simple, but actually the Internet Explorer framework must synchronize many different data types in addition to simple Web pages to accomplish this. You can see it in action if you use SQL Server 7.0 replication; it hooks into the same program (mobsync.exe) to synchronize its databases. The utility can be found under Program Files | Accessories | Synchronize (see Figure 12).
.gif)
Figure 12 The Synchronize Utility
Since this framework is being used to synchronize the database anyway, it was logical to write a synchronization manager to allow files to be taken offline as well. Hooking into the framework is fairly straightforward. You implement the ISyncMgrSynchronize interface, register the COM component, and off you go. For this implementation, I used the database to store the list of files that needed to be synchronized, although other methods can easily be used. The IDL file is shown in Figure 13.
The header file has only a few especially noteworthy features. I declared a Node structure to store the source file path, destination file path, a GUID that is used as a Synchronization Manager identifier, the type of object to synchronize (which doesn't have to be a file, though that's all I've implemented so far), the object's ReadyState, and a pointer to the parent SyncMgr object (see Figure 14).
In the source file (see Figure 15), I used the import directive so I can use ADO to query the database. Because EOF is typically defined as -1, you have to rename the EOF property to avoid problems with type mismatches. The struct InitOle is a quick way to ensure that CoInitialize and CoUninitialize are called. I've left the debugging code in place to allow you to trace what is happening in a file. Just #define FILEOUTPUT and a file called C:\SyncMgrDebug.txt will be generated.
Conclusion
The technology behind the digital dashboard is still evolving. In the near future, Web Services will allow users to easily choose the items they want in their dashboards, and as the technology picks up momentum, more sites will offer dashboard-specific modules. Many universities and businesses are already building rich solutions with this new technology.
For related articles see:
https://www.microsoft.com/digitaldashboard
For background information see:
https://msdn.microsoft.com/library/backgrnd/html/msdeforvs.htm
https://msdn.microsoft.com/library/psdk/zaw/syncport_4tnp.htm*
Todd Abel is a program manager in the Enterprise Program Management group at Microsoft. He assists ISVs and partners to develop enterprise architecture on the newest Microsoft technologies. He wrote the MSDN Members Helping Members Web site. Todd can be reached at toddabel@hotmail.com.*
From the July 2000 issue of MSDN Magazine.