question

PhilipODonnell-3996 avatar image
0 Votes"
PhilipODonnell-3996 asked RicardoPerezGonalves-5891 commented

AdventureWorksDW2020 DB is missing a table

Hi,

I'm trying to go through the PowerBI lab at this url:
https://docs.microsoft.com/en-gb/learn/modules/get-data/lab-prepare

I downloaded the AdventureWorksDW2020 from GitHub and loaded into SQL server management studio. 6 tables are mentioned in the lab:
DimEmployee
DimEmployeeSalesTerritory
DimProduct
DimReseller
DimSalesTerritory
FactResellerSales

But DimEmployeeSalesTerritory is not there. Note, AdventureWorksDW2017 was also mentioned but that table is not in that DB either.

Is there an updated version of AdventureWorksDW2020 that I can get that includes all the mentioned tables?

Thanks

Philip

sql-server-reporting-services
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There has never been a table named DimEmployeeSalesTerritory in the AdvWorks DW database. There is a DimSalesTerritory table in all versions I know of. There is a foreign key from the DimEmployee table to the DimSalesTerritory table. Maybe the Power BI teaching imported the DimSalesTerritory and renamed to DimEmployeeSalesTerritory because the DimSalesTerritory table is used in multiple relationships in the AdvWorks DW database.

Or, perhaps someone has created a view called DimEmployeeSalesTerritory like:

CREATE VIEW [dbo].DimEmployeeSalesTerritory
AS
SELECT *
FROM dbo.DimSalesTerritory
GO

1 Vote 1 ·

ThomBeaux

Thanks for that.

Also, thanks to Zoehui-MSFT.

I'll accept the answer, i.e. close it.

Regards

Philip

1 Vote 1 ·

Really, I see too, The documentation it's diferent of the database AdventureWorksDW2020, but the solution's ThomBeaux solve the problem until here.

0 Votes 0 ·

Very good, Thanks so much!

0 Votes 0 ·
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @PhilipODonnell-3996,

The newest AdventureWorksDW is 2019, you may see in the documentation.

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms

The AdventureWorksDW2020 mentioned in the tutorial is based on the AdventureWorksDW2017 and has been modified to support the learning objectives of the course labs.So it is a bit different with the AdventureWorksDW2017 and you couldn't find the DimEmployeeSalesTerritory.

In this tutorial, you may need to launch the lab and then complete the exercises with the provided AdventureWorksDW2020.

33156-screenshot-2020-10-19-103718.jpg

I tried to log in to the lab environment and connect to the AdventureWorksDW2020, we could see the DimEmployeeSalesTerritory.
33164-screenshot-2020-10-19-104027.jpg
Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PhilipODonnell-3996 avatar image
0 Votes"
PhilipODonnell-3996 answered LuisJMoranCuenca-5200 commented

Hi @Zoehui-MSFT,

Thanks for the reply. Much appreciated.

I downloaded and restored the AdventureWorksDW2019 file (because I can't see a ...2020 version) but there is no DimEmployeeSalesTerritory table there either. I'll attach an image.

One of your images shows adventureworksdw2020. Where did you get that file?

Regards

Philip33314-adventureworksdw2019.png







· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PhilipODonnell-3996,

In this https://docs.microsoft.com/en-gb/learn/modules/get-data/lab-prepare, you may see an opinion named "launch tab"

Click it there will be a new pop window, you could have a online exercise and in this exercise, you may use the AdventureWorksDW2020 provided by the tutorial.

And then you could use the table "DimEmployeeSalesTerritory" which is shown in my picture.

In short, if you want to use the AdventureWorksDW2020, you should launch tab and do the exercise online.

I think you could Unaccept your answer and mark my reply below as "Accept Answer" and then we may close the case.

Millions thanks!
33380-screenshot-2020-10-20-093551.jpg


0 Votes 0 ·

Thank you, it runs

0 Votes 0 ·
PhilipODonnell-3996 avatar image
0 Votes"
PhilipODonnell-3996 answered

@Zoehui-MSFT

I appreciate the clarification. I was following along on my desktop PowerBI, but it is much better to follow the lab from the PowerBi within the lab - which did indeed have the DimEmployeeSalesTerritory table.

I've unaccepted the answer that was accepted - which was too bad as the idea of creating a view to get through the lab was a good one.

I'm a newbie to posting such queries, so did not see a way to accept your latest post, but maybe it will be available when I post this.

Regards

Philip

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MaartenvanSchaik-1236 avatar image
0 Votes"
MaartenvanSchaik-1236 answered MaartenvanSchaik-1236 edited

The table you look for is included in the AdventureWorksDW2020.bak file which is used in the DA-100 labs which are normally run inside the VM environment of the module. However, you can obtain it from a Microsoft Learning GitHub page which is apparently used to host all lab files. You can find it here: https://github.com/MicrosoftLearning/DA-100-Analyzing-Data-with-Power-BI/tree/master/Allfiles/DA-100-Allfiles/DatabaseBackup

You can download the .bak file from there, restore it to a localhost and then import it in a PowerBI that you run locally on your own computer (it's faster and less annoying than having to do it in the virtual lab)

[edit] Currently as I'm writing this, in the very first DA-100 lab (Lab - Prepare data in Power BI Desktop) you also need two .csv files (ResellerSalesTargets.csv and ColorFormats.csv), you will likely want these if you plan to do the labs on your local PowerBI installation rather than in the VM. You can find those here:
https://github.com/MicrosoftLearning/DA-100KO-Analyzing-Data-with-Power-BI/tree/master/Allfiles/DA-100-Allfiles/Resources

note: this is NOT the same as the backup files that Zoehui-MSFT links to, that is a 'cleaned up' version of the database, so you don't get to practise the material of the first labs.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PhilipODonnell-3996 avatar image
0 Votes"
PhilipODonnell-3996 answered MaartenvanSchaik-1236 edited

@MaartenvanSchaik-1236

Thanks for the update. This was many months ago, and that course is long finished for me - so there is not much point in my commenting on it any more.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I realized that, but I thought it would probably be helpful for anyone else facing the same problem. I know I was, and searching for "AdventureworksDW2020" on the current most popular internet search engine returns this thread as the first result :-)

0 Votes 0 ·