Jeff Pries

Business Intelligence, SQL Server, and other assorted IT miscellany

Category: T-SQL

Sample Data — An Intro to Adventure Works

AdventureWorks-Logo_blogSample Data — An Intro to Adventure Works

From time to time, it’s handy to have access to some good, solid sample data.  Now, actually coming up with a robust set of sample data that has no tie back to any proprietary business data can actually be a tricky prospect.  It’s easy to generate a small amount of good data yourself, or a large amount of really random data, but neither of these are very robust.  Fortunately, Microsoft comes to the rescue with their fictional company, Adventure Works, and the Adventure Works Databases.

Microsoft provides the following bio for the Adventure Works company over at Technet:

Adventure Works Cycles, the fictitious company on which the AdventureWorks sample databases are based, is a large, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. While its base operation is located in Bothell, Washington with 290 employees, several regional sales teams are located throughout their market base.

In 2000, Adventure Works Cycles bought a small manufacturing plant, Importadores Neptuno, located in Mexico. Importadores Neptuno manufactures several critical subcomponents for the Adventure Works Cycles product line. These subcomponents are shipped to the Bothell location for final product assembly. In 2001, Importadores Neptuno, became the sole manufacturer and distributor of the touring bicycle product group.

Coming off a successful fiscal year, Adventure Works Cycles is looking to broaden its market share by targeting their sales to their best customers, extending their product availability through an external Web site, and reducing their cost of sales through lower production costs.

In addition to providing a realistic scenario for the Adventure Works company and a database full of realistic sample data, Microsoft continually updates the Adventure Works database for new versions of SQL Server and makes numerous variations of the databases available to highlight different technologies.

Continue reading

T-SQL Date Dimensions with ConnectWise

T-SQL Date Dimensions with ConnectWise PSA

ConnectWise PSA is a Microsoft SQL Server OLTP-based application commonly used by IT Managed Services Providers for managing their businesses.  ConnectWise provides a mechanism for entering and storing service tickets, time worked by technicians on service tickets, sales items, client billing, and other finance information.  For any Managed Services Provider, it tends to be the heart of the business.

A Date Dimension is a concept typically used in a Data Warehouse for implementing Time Intelligence which allows for querying on say “year to date” or for the quarter.  A Date Dimension is created through the creation of a Date Table.  A Date Table is a table which contains every date for a time period (with no gaps within) and numerous properties for each date.  Some of the most common properties are:

  • The Date, as a DATE or DATETIME
  • The DateKey, which is an INT that shows the date in YYYYMMDD form
  • Whether or not the date is a work day or not
  • Numerous date parts of the date, such as the month number, month name, day of the week, week number, quarter, year, and many more.
While the exact fields which make up the date table will vary based on the needs of the business, the above items tend to be some of the most common.

Continue reading

© 2018 Jeff Pries

Theme by Anders NorenUp ↑