November 14, 2016 at 4:09 pm
Please see attached. I am trying to do a tutorial in SSAS and I cant find any of the tables that start with the word "DIM"
Can someone provide me ADVENTURWORKS database file that has all these tables or tell me where to get them.
The missing tables are
DimCustomer
DimDate
DimGeography
DimProduct
FactIntertnetSales
November 14, 2016 at 4:56 pm
I found the tables script on some internet site. I am going to paste this hoping it may help someone
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimProductSubcategory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DimProductSubcategory](
[ProductSubcategoryKey] [int] IDENTITY(1,1) NOT NULL,
[ProductSubcategoryAlternateKey] [int] NULL,
[EnglishProductSubcategoryName] [nvarchar](50) NOT NULL,
[SpanishProductSubcategoryName] [nvarchar](50) NOT NULL,
[FrenchProductSubcategoryName] [nvarchar](50) NOT NULL,
[ProductCategoryKey] [int] NULL,
CONSTRAINT [PK_DimProductSubcategory_ProductSubcategoryKey] PRIMARY KEY CLUSTERED
(
[ProductSubcategoryKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [AK_DimProductSubcategory_ProductSubcategoryAlternateKey] UNIQUE NONCLUSTERED
(
[ProductSubcategoryAlternateKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimProduct]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DimProduct](
[ProductKey] [int] IDENTITY(1,1) NOT NULL,
[ProductAlternateKey] [nvarchar](25) NULL,
[ProductSubcategoryKey] [int] NULL,
[WeightUnitMeasureCode] [nchar](3) NULL,
[SizeUnitMeasureCode] [nchar](3) NULL,
[EnglishProductName] [nvarchar](50) NOT NULL,
[SpanishProductName] [nvarchar](50) NOT NULL,
[FrenchProductName] [nvarchar](50) NOT NULL,
[StandardCost] [money] NULL,
[FinishedGoodsFlag] [bit] NOT NULL,
[Color] [nvarchar](15) NOT NULL,
[SafetyStockLevel] [smallint] NULL,
[ReorderPoint] [smallint] NULL,
[ListPrice] [money] NULL,
[Size] [nvarchar](50) NULL,
[SizeRange] [nvarchar](50) NULL,
[Weight] [float] NULL,
[DaysToManufacture] [int] NULL,
[ProductLine] [nchar](2) NULL,
[DealerPrice] [money] NULL,
[Class] [nchar](2) NULL,
[Style] [nchar](2) NULL,
[ModelName] [nvarchar](50) NULL,
[LargePhoto] [varbinary](max) NULL,
[EnglishDescription] [nvarchar](400) NULL,
[FrenchDescription] [nvarchar](400) NULL,
[ChineseDescription] [nvarchar](400) NULL,
[ArabicDescription] [nvarchar](400) NULL,
[HebrewDescription] [nvarchar](400) NULL,
[ThaiDescription] [nvarchar](400) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Status] [nvarchar](7) NULL,
CONSTRAINT [PK_DimProduct_ProductKey] PRIMARY KEY CLUSTERED
(
[ProductKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [AK_DimProduct_ProductAlternateKey_StartDate] UNIQUE NONCLUSTERED
(
[ProductAlternateKey] ASC,
[StartDate] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactInternetSalesReason]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FactInternetSalesReason](
[SalesOrderNumber] [nvarchar](20) NOT NULL,
[SalesOrderLineNumber] [tinyint] NOT NULL,
[SalesReasonKey] [int] NOT NULL,
CONSTRAINT [AK_FactInternetSalesReason_SalesOrderNumber_SalesOrderLineNumber_SalesReasonKey] UNIQUE NONCLUSTERED
(
[SalesOrderNumber] ASC,
[SalesOrderLineNumber] ASC,
[SalesReasonKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimEmployee]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DimEmployee](
[EmployeeKey] [int] IDENTITY(1,1) NOT NULL,
[ParentEmployeeKey] [int] NULL,
[EmployeeNationalIDAlternateKey] [nvarchar](15) NULL,
[ParentEmployeeNationalIDAlternateKey] [nvarchar](15) NULL,
[SalesTerritoryKey] [int] NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[NameStyle] [bit] NOT NULL,
[Title] [nvarchar](50) NULL,
[HireDate] [datetime] NULL,
[BirthDate] [datetime] NULL,
[LoginID] [nvarchar](256) NULL,
[EmailAddress] [nvarchar](50) NULL,
[Phone] [nvarchar](25) NULL,
[MaritalStatus] [nchar](1) NULL,
[EmergencyContactName] [nvarchar](50) NULL,
[EmergencyContactPhone] [nvarchar](25) NULL,
[SalariedFlag] [bit] NULL,
[Gender] [nchar](1) NULL,
[PayFrequency] [tinyint] NULL,
[BaseRate] [money] NULL,
[VacationHours] [smallint] NULL,
[SickLeaveHours] [smallint] NULL,
[CurrentFlag] [bit] NOT NULL,
[SalesPersonFlag] [bit] NOT NULL,
[DepartmentName] [nvarchar](50) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Status] [nvarchar](50) NULL,
CONSTRAINT [PK_DimEmployee_EmployeeKey] PRIMARY KEY CLUSTERED
(
[EmployeeKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimGeography]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DimGeography](
[GeographyKey] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](30) NULL,
[StateProvinceCode] [nvarchar](3) NULL,
[StateProvinceName] [nvarchar](50) NULL,
[CountryRegionCode] [nvarchar](3) NULL,
[EnglishCountryRegionName] [nvarchar](50) NULL,
[SpanishCountryRegionName] [nvarchar](50) NULL,
[FrenchCountryRegionName] [nvarchar](50) NULL,
[PostalCode] [nvarchar](15) NULL,
[SalesTerritoryKey] [int] NULL,
CONSTRAINT [PK_DimGeography_GeographyKey] PRIMARY KEY CLUSTERED
(
[GeographyKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
November 14, 2016 at 4:59 pm
However there are a lot more tables that start with the word "Dim" .
If anyone has the full database with the data ( mdb file ) let me know.
November 14, 2016 at 5:00 pm
I found this script to populate the DimDate table
BEGIN TRAN
declare @startdate date = '2005-01-01',
@enddate date = '2014-12-31'
IF @startdate IS NULL
BEGIN
Select Top 1 @startdate = FulldateAlternateKey
From DimDate
Order By DateKey ASC
END
Declare @datelist table (FullDate date)
while @startdate <= @enddate
Begin
Insert into @datelist (FullDate)
Select @startdate
Set @startdate = dateadd(dd,1,@startdate)
end
Insert into dbo.DimDate
(DateKey,
FullDateAlternateKey,
DayNumberOfWeek,
EnglishDayNameOfWeek,
SpanishDayNameOfWeek,
FrenchDayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
SpanishMonthName,
FrenchMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarYear,
CalendarSemester,
FiscalQuarter,
FiscalYear,
FiscalSemester)
select convert(int,convert(varchar,dl.FullDate,112)) as DateKey,
dl.FullDate,
datepart(dw,dl.FullDate) as DayNumberOfWeek,
datename(weekday,dl.FullDate) as EnglishDayNameOfWeek,
(Select top 1 SpanishDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as SpanishDayNameOfWeek,
(Select top 1 FrenchDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as FrenchDayNameOfWeek,
datepart(d,dl.FullDate) as DayNumberOfMonth,
datepart(dy,dl.FullDate) as DayNumberOfYear,
datepart(wk, dl.FUllDate) as WeekNumberOfYear,
datename(MONTH,dl.FullDate) as EnglishMonthName,
(Select top 1 SpanishMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as SpanishMonthName,
(Select top 1 FrenchMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as FrenchMonthName,
Month(dl.FullDate) as MonthNumberOfYear,
datepart(qq, dl.FullDate) as CalendarQuarter,
year(dl.FullDate) as CalendarYear,
case datepart(qq, dl.FullDate)
when 1 then 1
when 2 then 1
when 3 then 2
when 4 then 2
end as CalendarSemester,
case datepart(qq, dl.FullDate)
when 1 then 3
when 2 then 4
when 3 then 1
when 4 then 2
end as FiscalQuarter,
case datepart(qq, dl.FullDate)
when 1 then year(dl.FullDate)
when 2 then year(dl.FullDate)
when 3 then year(dl.FullDate) + 1
when 4 then year(dl.FullDate) + 1
end as FiscalYear,
case datepart(qq, dl.FullDate)
when 1 then 2
when 2 then 2
when 3 then 1
when 4 then 1
end as FiscalSemester
from @datelist dl left join
DimDate dd
on dl.FullDate = dd.FullDateAlternateKey
Where dd.FullDateAlternateKey is null
COMMIT TRAN
November 15, 2016 at 12:36 am
I suspect you downloaded the incorrect DB.
AdventureWorks2014 doesn't have any DIM named tables in it, as it's an OLTP demo database. The AdventureWorksDW database is the sample data warehouse and has the DIM named tables in it
https://msftdbprodsamples.codeplex.com/releases/view/125550
You want the download labeled "Adventure Works DW 2014 Full Database Backup.zip"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2016 at 6:51 am
GM
I did unzip the file and now I have this file.
AdventureWorks2014.bak
May I ask you how do I install the DB from here. I am sure this can be done via T-SQL.
November 15, 2016 at 7:11 am
Did a RightClick on Database and Selected [highlight="#F5CC33"] Restore Database[/highlight]
I followed the steps on http://www.techjunkieblog.com/2014/08/installing-adventureworks-sample.html
However the "OK" button is greyed out.
See the attached picture .... Somehow when I selected the BAK File it did not recognize that the DB name was AdventureWorks2014 and did not populate the rest of the fields
November 15, 2016 at 7:20 am
Sorry, didn't notice you had SQL Server 2012
Go to this link rather https://msftdbprodsamples.codeplex.com/releases/view/55330, and download the second item there "AdventureWorksDW2012 Data File " and follow the instructions further down that page.
The 2014 DB will not restore on SQL 2012.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2016 at 7:28 am
GM
I am assuming that this Db will have those tables that start with the "DM" prefix ( Ex: DmDate )
November 15, 2016 at 7:33 am
mw112009 (11/15/2016)
GMI am assuming that this Db will have those tables that start with the "DM" prefix ( Ex: DmDate )
Yes, AdventureWorksDW (with whatever version number) is the data warehouse database which has Dim(ension) and Fact tables.
You initially downloaded the transactional database.
November 15, 2016 at 7:49 am
Thanks GM and LUIS..
The following command did work
CREATE DATABASE AdventureWorks2012_Data
ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012_Data.mdf')
FOR ATTACH_REBUILD_LOG
Go
November 15, 2016 at 8:06 am
Surprise!
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply