AdventureWorks

Microsoft provides a common dataset called AdventureWorks when learning how to use SQL Server. Using SquaredUp, the AdventureWorks dataset can easily be visualized in any organization.

Shawn Williams, SquaredUp

How do I get it?

Challenge

For each release of SQL Server, Microsoft also provides a sample database called AdventureWorks. Is it possible to visualize data from the AdventureWorks data warehouse database using SquaredUp?

Solution

Microsoft provides a sample database for various workload types like OLTP, Data Warehouse, and Lightweight workloads for each release of SQL Server. Since each release of SQL Server results in a new version of AdventureWorks, the user may be required to make a few alterations to the data for visualization purposes. For example, the database doesn’t have any current data in some cases. The initial target for this dashboard was the AdventureWorks 2017 dataset. In several instances, the data for the tile was duplicated so that the graph appears continuous for visualization purposes.

Dashboard walk-through

Each tile on the dashboard is a different SquaredUp visualization. Here is how it breaks down:

Tile 1 (Bar) – Simple SQL query using a Group By to count customers by region.
Tile 2 (Graph) – A more complex query to duplicate records to create a continuous graph (since the last record in the dataset is from 2017). Page Timeframe is also used to demonstrate a different approach to browsing the dataset.
Tile 3 (Scalar) – A simple query to show which Page Timeframe was applied for the dashboard.
Tiles 4, 5, and 6 (Scalar) – A scalar tile that shows health information based on criteria within the query.
Tiles 7 (Status Icon) – Summarized results are assigned a health state and then placed on an image.
Tiles 8 (Status Blocks) – Similar to the Scalar tiles, but demonstrates how to use a complex Case statement to assign health state to a result set.
Tiles 9 (Donut) – A simple summary query.
Tile 10 (Sparkline) – The same query used in Tile 2, but the results are shown in a Sparkline instead of a single graph.

How do I import and configure this dashboard?

Download pack for SCOM Dashboard Server

Ensure you're using SquaredUp DS v5.4 or above.
Already a SquaredUp customer?
Get the latest version of SquaredUp DS for SCOM
New to SquaredUp?
Try it for free
Import the Microsoft AdventureWorks datasets from: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms
Download and import this dashboard pack.
Download pack for SCOM Dashboard Server
1. Download the dashboard pack zip file for the dashboard pack you wish to import.
  There may be additional steps before or after the import to get the dashboard working in your environment.
2. In DS for SCOM go to the top right hand menu ☰ > Import Dashboard Packs and drag the zip file into the Manual Import box.
3. The dashboard pack is imported and if the pack contains top-level dashboards, these will automatically appear in the navigation bar, usually in a folder called 'Community Dashboards' for dashboard packs from the SquaredUp Community. If the dashboard pack also contains perspectives (see Working with perspectives), then you'll see these when you drilldown to the relevant object.
4. Carry out any additional steps required to make the dashboard work in your environment. These steps will be described on the download page for each dashboard. For example, you may need to create the correctly named Web API provider, create a PowerShell profile, or edit tile collections.
5. Edit the imported dashboard as required.
Replace the connection string in each of the ten SQL tiles with the connection string to your own OperationsManagerDW database.
1. Edit the tiles by clicking the Edit button.
2. Open the connection panel.
3. Enter the connection string global:dw.
Publish the dashboard.
A newly created dashboard will not be visible to others until it is published.
Only admins can publish dashboards, unless you have been given author permissions to a Team Folder see Team Folders
If you made changes to an existing dashboard, the changes will only be visible to others after you published the dashboard again.
You can identify a dashboard that has not been published yet or has unpublished changes by the unpublished button at the top:
When you click on the unpublished button, you'll have two options:
- Publish will make the dashboard or changes visible to others.
  Note: A newly created dashboard will appear on the menu where you created it. To move the dashboard to a different place on the navigation bar see How to edit the Navigation Bar.
- Discard will delete your draft dashboard if it has never been published or, if you made changes to an already published dashboard, discard the changes.
Publishing dashboards to different audiences
Find out how to publish dashboards to a subset of users using Team Folders or visible to anyone even unlicensed users with Open Access (Sharing Dashboards with anyone - Open Access).

GET STARTED TODAY

Zero to dashboard hero in 60 seconds

Start now with hundreds of customizable dashboards.