VBDSS is an MDI Visual Basic and SQL Server project template that demonstrates the decision support architecture concepts of this presentation. It is designed to provide a sample shell of all the presentation concepts for easy modification to your own specification.
It utilizes and requires the other reusable components of this presentation: VBODBC, VBSEC, and VBEXCEL. It also utilizes the pubs database as the source for its DSS table.
1. VBDSS.MAK Visual Basic project make file
2. VBDSS.BAS Visual Basic code module
3. VBDSS1.FRM Visual Basic project MDI container form
4. VBDSS2.FRM Visual Basic project "New SnapShot Manager" child form
5. VBDSS3.FRM Visual Basic project "SnapShot" child form
6. VBDSS.SQL SQL Server pubs script
7. (Requires other files from VBODBC, VBSEC, and VBEXCEL libraries.)
1. Log on to SQL Server as sa and execute the VBDSS.SQL script. This will:
Create the sample logins and pubs accounts dss_manager, dss_rep, and dss_programmer with passwords the same as the IDs.
Create the sample pubs groups dss_managers, dss_reps, and dss_programmers.
Create a sample table, pubs..dss.
Create a data warehouse "refresh" stored procedure, sp_dss_refresh.
Create client-application stored procedures, sp_dss_detail, sp_dss_sum, and sp_dss_states.
Assign permissions to client-application stored procedures.
Execute stored procedure sp_dss_refresh to populate the pubs..dss table and simulate an overnight "batch refresh".
2. Log on to SQL Server as sa. Execute the VBSEC.SQL script against the pubs database. This will install the sp_security stored procedure, used by VBSEC to query user security rights.
3. Install an ODBC data source on the client workstation that points to the SQL Server.
4. If the SQL Server supports only integrated security, you will need to create Windows NT accounts for the SQL Server users dss_manager, dss_rep, and dss_programmer with passwords the same as their IDs.
5. Verify that Microsoft Excel 5.0 exists on the client workstation.
1. Start the application. Choose your SQL Server data source. Log on as dss_rep with password dss_rep.
2. A MDI form appears with a single child form, the New SnapShot Manager. This form manages the client requests for server "SnapShots," or result sets. The stored procedure sp_dss_states was used to populate the States list box.
3. Notice how the "Detail" radio button is disabled, because the dss_rep user doesn't have access to the corresponding stored procedure, sp_dss_detail.
4. Click the SnapShot button. A child form is created, the sp_dss_sum stored procedure is executed, the result set is loaded into an OLE Automation workbook, and the results are displayed in a grid.
5. The Pivot Fields list box lists all the pivot fields in the pivot table. The Orientation combo box shows the value of the Orientation property for the select pivot field. Try changing the pivot fields' orientation value and notice the changes reflected in the grid.
6. Try logging on with the dss_manager and dss_programmer IDs, and notice how the client application reacts dynamically to their server security rights.