AdventureWorksDW20xx / Which version to use?

AdventureWorks is one of the standard demo models from Microsoft for SQL Server. There is a data warehouse version (DW), an OLTP and a light OLTP (LT) version. They are versioned according to the SQL Server (on premises) version schema, which is basically the year (there used to be a 2008R2, but this naming schema wasn’t used any more). So the latest version of the datawarehouse (and I’m not interested in OLTP) is AdventureWorksDW2019.

Now the confusion starts.

With the creation of learning content for DAX and Power BI, Microsoft created a somewhat simplified version and named it AdventureWorksDW2020. It is the basis for the labs of the Power BI certification PL-300 “Power BI Data Analyst Associate”.

The new certification DP-500 “Azure Enterprise Data Analyst Associate” uses another version: AdventureWorksDW2022.
It contains tables (e.g. FactSalesQuota) that were removed in AdventureWorksDW2020, so it is rather a branch from AdventureWorksDW2019 than from AdventureWorksDW2020.

See the versioning information within the databases:

Database	DBVersion	VersionDate
[AdventureWorksDW2019]	14.0.1000.169	08/22/2017
[AdventureWorksDW2020-PL300]	12.0.1800	02/20/2014
[AdventureWorksDW2020-PL300]	2020-PowerBI	03/15/2020
[AdventureWorksDW2022-DP500]	14.0.1000.169	08/22/2017
[AdventureWorksDW2022-DP500]	2020-DAX-Docs-v1	06/05/2020
[AdventureWorksDW2022-DP500]	AdventureWorksDW2022-DP500	03/29/2022

However, AdventureWorksDW2022 is still a simplified version if AdventureWorksDW2019, so it is unclear whether there still will be an additional branch for newer versions of SQL Server like SQL Server 2022. Hmm, the name AdventureWorksDW2022 is already taken…

So will it be the chaotic way forward:
AdventureWorksDW2019 -> AdventureWorksDW2020 -> continued for PL-300
AdventureWorksDW2019 -> AdventureWorksDW2022 -> continued for DP-500
AdventureWorksDW2019 -> another AdventureWorksDW2022 for SQL 2022 ??? -> ???

Or am I just seeing chaos where no chaos is and the story is simply:
AdventureWorksDW2019 -> AdventureWorksDW2020 -> AdventureWorksDW2022

In this current situation, the differences in the schema (existing tables) are as follows. Numbers in the tables are number of records. If there is no number, the table doesn’t exist (so you see that both DW2020 and DW2022 are simplified). The tables that are not listed (of course still the majority), are existing in all three databases:

Table Name	DW2019	DW2020	DW2022
dbo.DatabaseLog	96	–	–
dbo.DimEmployeeSalesTerritory	–	39	–
dbo.FactAdditionalInternationalProductDescription	15,168	–	–
dbo.FactCallCenter	120	–	120
dbo.FactSalesQuota	163	–	163
dbo.FactSurveyResponse	2,727	–	–
dbo.NewFactCurrencyRate	50	–	–
dbo.ProspectiveBuyer	2,059	–	–

Compatibility of DW2022 with the Analysis Services Tabular demo model

In contrast to the multidimensional model, the tabular demo model seems to be a little bit outdated. The compatibility layer is SQL 2016 (1200) and the readme talks about AdventureWorksDW2014. In a first step, for curiosity, I pointed the datasource to DW2019 (I don’t even have an older one) and rebuilt. No problem. Then the switch to DW2022 caused some issues due to changes in the schema of table FactInternetSales.

Three fields are not longer existing:

OrderDate
DueDate
ShipDate

The reason seems clear to me: there were already and currently there are foreign key fields (OrderDateKey, DueDateKey and ShipDateKey), so the deleted ones were redundant. In the model, the deletion of the fields was not a problem. The only use was in the partition definitions. There are yearly partitions defined, by using the order date.

For example: WHERE (([OrderDate] >= N’2013-01-01 00:00:00′) AND ([OrderDate] < N’2014-01-01 00:00:00′))

Changing the queries would not have been a big thing, but I just got rid of the partitions to avoid this work.

Additionally two fields were renamed:

Freight to FreightAmount
TaxAmt to TaxAmount

Also this makes sense to follow naming conventions of other fields. In the model there is another naming convention to uses spaces in between, so the resulting fields in the model should be Freight Amount and Tax Amount.

Compatibility of DW2022 with the Analysis Services Multidimensional demo model

The multidimensional demo model is surprisingly more current than the tabular one. The datasource is named AdventureWorksDW2019, so it is clear that it is using the respective database.

Trying to switch to DW2022 shows the same problems like the tabular one, plus additional errors. While the tabular model is only based on Internet Sales, the multidimensional one is much more comprehensive, so that more database changes apply. I just deleted some measures and attributes because the model is not so important for me. If it is important you rather stick with AdventureWorksDW2019.

Compatibility of DW2022 with the labs of PL-300

The table DimEmployeeSalesTerritory is only available in the DW2020 version. It is used to demonstrate advanced relationships, like shown in the following screenshot. Attention, it is renamed to SalespersonRegion:

I thought about copying the table from DW2020 to DW2022. But then I looked at potential further changes that were not so obvious. And indeed, there are much more, like it can be seen in the following screenshot:

Comparison AdventureWorksDW2020 to AdventureWorksDW2022

With this insight, I stopped my activities. I’m using PL-300 lab-materials purely for teaching. The benefit of getting rid of one database would not justify the effort to go through all the materials and check what the consequences of the changes are. And what would I do when there are changes by Microsoft on the PL-300 materials?

Result

I spent an afternoon watching Tour de France and doing this stuff. I got insights into the development of AventureWorks and have my standard models on the latest database version AventureWorksDW2022. They are not very important for me, but it is done now. For PL-300 demos I still use the AdventureWorksDW2020. I hope that I also helped others with this by avoiding confusion.