Business Intelligence

I am going to set up the Automart database as a Data warehouse. (I am using Automart because at home CommunityAssist is in a process of transformation and is too different from the version on your servers to be useful.) I have tried to reorganize the database into a "Star" structure consisting of a fact table surrounded by Dim or Dimension tables. Here is the diagram:

First we create the new database

Create Database AutomartDW
Go
use AutomartDW
Go

Then we create the tables

Create table DimVehicle
(
 VehicleID int primary key,
 VehicleMake nvarchar(255),
 VehicleYear nvarchar(4)
)
go
Create table DimCustomer
(
 CustomerKey int identity(1,1) primary key,
 LastName nvarchar(255),
 FirstName nvarchar(255),
 Email nvarchar(255),
 VehicleID int Foreign key references DimVehicle(VehicleID)
)

Create table DimVehicleService
(
 VehicleServiceKey  int identity(1,1) primary key,
 VehicleServiceID int,
 VehicleID int,
 LocationName nvarchar(255),
 ServiceName nvarchar(255),

)

go

Create Table DimDate
(
 DateKey int identity (1,1) primary key,
 ServiceDate Date,
 ServiceTime Time,
 sDay int,
 sMonth int,
 sYear int
)
go

Create table FactSales
(
 VehicleID int foreign Key references DimVehicle(VehicleID),
 DateKey int foreign key references DimDate(DateKey),
 VehicleServiceKey int foreign Key references DimVehicleService(VehicleServiceKey),
 CustomerKey int foreign key references DimCustomer(customerKey),
 ServicePRice money,
 DiscountPercent decimal(3,2),
 TaxPercent decimal(3,2)


)

Next we populate the tables. I am pretty sure of the Dim table inserts, but must confess a bit of uncertainty about how I populated the Fact Table

--start inserts into tables

Insert into DimVehicle(VehicleID, VehicleMake, VehicleYear)
Select vehicleID, VehicleMake, VehicleYEar from Automart.Customer.Vehicle

Insert into DimCustomer (LastName, FirstName, Email, VehicleID)
Select LastName, Firstname, Email, VehicleID
From Automart.dbo.Person p
Inner join Automart.Customer.Vehicle v
on p.Personkey=v.PersonKey
inner join Automart.Customer.RegisteredCustomer c
on c.PersonKey=p.Personkey

Insert into DimVehicleService(VehicleServiceID, VehicleID, LocationName, ServiceName)
Select vs.VehicleServiceID, VehicleID, LocationName, ServiceName
From Automart.Employee.VehicleService vs
Inner join Automart.Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID=vsd.VehicleServiceID
inner join Automart.Customer.Location l
on vs.LocationID=l.LocationID
inner join Automart.Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID

INsert into dimDate(ServiceDate, ServiceTime, sDay, sMonth, sYear)
Select ServiceDate, ServiceTime, Day(ServiceDate), Month(ServiceDate), Year(serviceDate) 
From Automart.Employee.VehicleService

Insert into FactSales(VehicleID, DateKey, VehicleServiceKey, CustomerKey, ServicePRice, DiscountPercent, TaxPercent)
Select dv.VehicleID, DateKey, VehicleServiceKey, CustomerKey, ServicePRice, DiscountPercent, TaxPErcent
From DimVehicle dv
inner Join DimCustomer dc
on dv.VehicleID = dc.VehicleID
inner join Automart.Employee.VehicleService vs
on dv.VehicleID=vs.VehicleID
inner join Automart.Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID=vsd.VehicleServiceID
inner join DimVehicleService dvs
on vs.VehicleServiceID=dvs.VehicleServiceID
inner join Automart.customer.AutoService a
on vsd.AutoServiceID=a.AutoServiceID
inner join DimDate dd
on vs.ServiceDate=dd.ServiceDate 

Next we need to make sure we add the service user to the AutomartDW database

First open the SQL Server Configuration Manager and select SQL Server Analysis Services. Right Click on the properties. You will probably see something like this.

We want to change it to local services.

If it already looks like this, great. other wise change it. Oking this will cause a server restart. Say OK to the warning. Then return to Sql Server Management studio. Go to the Server Security folder and right click logins. Select New Login. In the login dialog click on search,and then advanced. Select NT Authority/Local Service as a login.

Then click OK, OK until you are back at the main dialog window. Click on the page User Mapping. Then check the box by AutomartDW and Check the roles DataReader, DataWriter

Click OK. Now we need to open Visual Studio Data tools. and Start a new Data Analysis Multidimensional cube project. Name it "AutomartAnalysisProject."

In the Solution Explorer right click on Data Sources and add a new Data source

Click OK and Next and then choose Use Service Account

Go Next and Finish. Now right click Data Source Views in the Solution Explored and Select New. Use the data source we just created.

On the next dialog of the wizard, add all the tables

Click Next and finish. You will see the designer with the tables.

Now right click on the Cubes folder and add a new cube

Use Existing tables

Click Next and then choose all the tables except sysDiagrams

click Next, Next and Finish. It wouldn't hurt at this time to build the solution. Then right click on the cube and select Process.

Click Run. You should see something like this when it is done.

Click Close, Close. Then click the Browser tab. Here you can drag in dimensions and calculations

Not very exciting, but Automart doesn't have much data. Also, I am not absolutely confident of my dimesion setup. I seemed to have missed the location. It should have been a dim table. Also I am not sure about how I populated the SaleFact table. The numbers displaying are correct, but some of the other measures produce wrong results. But this blog does give you an overview of the process.

You can still look at other measures and try to add KPIs and other aspects. Maybe sometime in the not too distant future I will do a blog where I take it from the cube and show how to apply statistics and KPIs to the results.