CommunityAssist Data Warehouse

Create database CommunityAssistDw

use CommunityAssistDW

Create table DimClient
(
    DimClientKey int identity(1,1) primary key,
 PersonKey int,
 ClientLastName nvarchar(255),
 ClientFirstName nvarchar(255),
 ClientEmail nvarchar(25),
 ClientZipcode nchar(10)
)

Create table DimEmployee
(
 DimEmployeeKey int identity(1,1) primary key,
 PersonKey int,
 EmployeeKey int,
 EmployeeHireDate dateTime,
 EmployeeStatus nchar(2)
)

Create table DimService
(
 DimServiceKey int identity(1,1) primary key,
 ServiceName nvarchar(255),
 ServiceKey int
)

Create table DimGrant
(
 DimGrantKey int identity(1,1) primary key,
 Grantkey int,
 GrantApprovalStatus nvarchar(10)
)

Create Table DimDate
(
 DimDateKey int identity (1,1) primary key,
 GrantDate dateTime,
 GrantReviewDate dateTime,
 GrantYear int,
 GrantMonth int,
 ReviewYear int,
 ReviewMonth int
)


Create table FactGrant
(
 DimClientKey int not null,
 DimServiceKey int not Null,
 DimGrantKey int not null,
 DimDateKey int not null,
 GrantAmount money,
 GrantAllocation money
)

Alter table FactGrant
Add Constraint PK_factGrant primary key 
(DimclientKey, dimServiceKey,  DimDateKey, DimGrantKey)

Alter Table FactGrant
Add Constraint FK_DimClient foreign key(DimClientKey)
references DimClient(DimClientKey)

Alter Table FactGrant
Add Constraint FK_DimSevice foreign key(DimServiceKey)
references DimService(DimServiceKey)

Alter Table FactGrant
Add Constraint FK_DimDate foreign key(DimDateKey)
references DimDate(DimDateKey)

Alter Table FactGrant
Add Constraint FK_DimGrant foreign key(DimGrantKey)
references DimGrant(DimGrantKey)

Insert into DimClient(PersonKey, ClientLastName, ClientFirstName, ClientEmail, ClientZipcode)
Select Distinct sg.PersonKey, PersonLastName, PersonFirstName,
  PersonUserName, Zip
 From CommunityAssist.dbo.Person p
 inner Join CommunityAssist.dbo.PersonAddress pa
 on p.PersonKey=pa.PersonKey
 inner Join CommunityAssist.dbo.ServiceGrant sg
 on p.PersonKey=sg.PersonKey


insert into DimService(ServiceName, ServiceKey)
Select ServiceName, ServiceKey from CommunityAssist.dbo.CommunityService

Insert into DimGrant(Grantkey, GrantApprovalStatus)
Select GrantKey, GrantApprovalStatus from CommunityAssist.dbo.ServiceGrant

Insert into dimDate(GrantDate, GrantReviewDate, GrantYear, GrantMonth, ReviewYear, ReviewMonth)
Select GrantDate, GrantReviewDate, Year(GrantDate), Month(GrantDate), 
      Year(GrantReviewDate), Month(GrantReviewDate)
   From CommunityAssist.dbo.ServiceGrant

Insert into FactGrant(DimClientKey, DimServiceKey, DimGrantKey, DimDateKey, GrantAmount, GrantAllocation)
Select DimClientKey, DimServiceKey, DimGrantKey, DimDateKey, GrantAmount, GrantAllocation
From DimClient dc
inner join CommunityAssist.dbo.serviceGrant sg
on dc.PersonKey=sg.PersonKey
inner join DimService ds
on ds.ServiceKey =sg.ServiceKey
inner join DimGrant dg
on dg.Grantkey=sg.GrantKey
inner join DimDate dd
on dd.GrantDate=sg.GrantDate

Data Warehouse script

I figured out what the problem was from class. The elements in the fact table were not in the same granularity. Particularly. employees and Customers were a problem. We could incorporate the customers if we went a step up to the general service table, rather than service details. We could add another fact table to handle this, but since most of the calculable values are at the detail level, I focused the fact table on that.

Here is the ERD

Here is the script

use master
Go
/************************************************
* Check to see if the database exists.
* If it does drop it and then recreate it.
************************************************/
if exists
 (Select name from sys.Databases 
   where name='AutomartDataWarehouse')
begin
 Drop database AutomartDataWarehouse
end
go
Create database AutomartDataWareHouse
Go
Use AutomartDataWareHouse
/*******************************************
* Create the Dimension tables.
********************************************/
Go


Go
Create table DimVehicle
(
   DimVehicleKey int identity(1,1) primary Key,
   VehicleID int,
   personKey int,
   LicenseNumber nvarchar(10),
   VehicleMake nvarchar(255),
   VehicleYEar nchar(4)
)
Go
Create table DimLocation
(
  DimLocationKey int identity(1,1) primary key,
  LocationID int,
  locationName nvarchar(255),
  LocationAddress nvarchar(255),
  LocationCity nvarchar(255),
  LocationState nchar(2),
  LocationZip nchar(10),
  LocationPhone nchar(13)
)

Go
Create Table DimService
(
 DimServiceKey int identity(1,1) primary Key,
 serviceId int,
 ServiceName nvarchar(255)
)
go
Create table DimDate
(
  DimDateKey int identity(1,1) primary key,
  ServiceDate Date,
  ServiceYear int,
  ServiceMonth int
)

/***********************************************
* Create the fact tables. The failure of the previous
* fact table was that it was of two different
* granularities that couldn't be resolved. To solve this
* I Left out the customer and employee dims. A different
* fact table might include them.
*************************************************/
Create table FactService
(
   FactServiceKey int identity(1,1),
      DimVehicleKey int Foreign Key references DimVehicle(DimVehicleKey),
   DimLocationKey int Foreign Key references DimLocation(DimLocationKey),
   DimDateKey int Foreign Key references DimDate(DimDateKey),
   DimServiceKey int Foreign Key references DimService(DimServiceKey),
  
   Constraint PK_FactService primary key(FactServiceKey,DimVehicleKey, DimLocationKey, DimDateKey, dimServiceKey),
   ServicePrice money,
   DiscountPercent decimal(3,2),
   TaxPercent decimal(3,2)
)

Go


/*****************************************************
* Populate the dim tables
******************************************************/


Go
Insert into DimVehicle(VehicleID,
   LicenseNumber,
   VehicleMake,
   VehicleYEar,
   personKey)
   Select VehicleID,
   LicenseNumber,
   VehicleMake,
   VehicleYEar, personKey
   From Automart.Customer.Vehicle
 
 Go
 Insert into DimLocation (
  LocationID,
  locationName,
  LocationAddress,
  LocationCity,
  LocationState,
  LocationZip,
  LocationPhone
 )
 Select 
 LocationID,
  locationName,
  LocationAddress,
  LocationCity,
  LocationState,
  LocationZip,
  LocationPhone
  From Automart.Customer.Location

  Go
  Insert into DimService(
  serviceId,
   ServiceName 
  )
  Select AutoServiceID, ServiceName
  from Automart.customer.AutoService

Go

Insert into DimDate(
 ServiceDate,
  ServiceYear,
  ServiceMonth)
 Select 
 ServiceDate,
  Year(ServiceDate),
  Month(ServiceDate)
  From Automart.Employee.VehicleService
  Go

  /***************************************
  * Insert into the fact table
  ****************************************/

  Insert into FactService(DimLocationKey, DimDateKey, DimServiceKey, DimVehicleKey, ServicePrice, DiscountPercent, TaxPercent)
  Select DimLocationKey, DimDateKey, DimServiceKey, DimVehicleKey, ServicePrice, DiscountPercent, TaxPercent
  from DimLocation dl
  inner Join Automart.Customer.Location  loc
  on dl.LocationID=loc.LocationID
  inner join Automart.Employee.VehicleService vs
  on loc.LocationID=dl.LocationID
  inner Join DimVehicle dv
  on vs.VehicleID=dv.VehicleID
  inner Join DimDate dd
  on dd.ServiceDate=vs.ServiceDate
  inner join Automart.Employee.VehicleServiceDetail vsd
  on vs.VehicleServiceID=vsd.VehicleServiceID
  inner Join DimService ds
  on ds.serviceId=vsd.VehicleServiceID
  inner join Automart.Customer.AutoService a
  on vsd.AutoServiceID=a.AutoServiceID

We will follow by creating a SSIS script to import data on a regular basis and creating a cube

Basic Security Script

Use Automart
go
--create schema for managers
Create Schema manager
--create an object that belongs to the schema Manager
Go
Create view manager.vw_LocationSummary
 As
 Select LocationName, count(distinct vs.VehicleServiceId) as [Count],
 sum(dbo.fx_GetTotalDue(ServicePrice, DiscountPercent)) as Total
 From Customer.AutoService a
 inner join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID
 inner Join Employee.VehicleService vs
 on vsd.VehicleServiceID=vs.VehicleServiceID
 inner Join Customer.Location loc
 on loc.LocationID=vs.LocationID
 Group by LocationName
 go
 --Create a role for Managers
 create role MangagerRole
 Go
 --provide permission for manager role
 Grant select, update on Schema::manager to ManagerRole

--create a login for managers
Create Login ManagerLogin with password='P@ssw0rd1'

--create a user in automart that is mapped to that login
Create user ManagerUser for Login ManagerLogin

--add the user to the role
exec sys.sp_addrolemember 'managerRole', 'ManagerUser'

--now login you should only see the objects that belong to the schema Manager
--and only have the permissions assigned to the role

Populating the Dim tables

Insert into DimCustomer(RegisteredCustomerKey, PersonKey, LastName, FirstName, Email)
Select RegisteredCustomerID, p.PersonKey, LastName, FirstName, Email
From Automart.dbo.Person p
inner join Automart.Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey

Insert into DimEmployee(EmployeeID, PersonKey, HireDate, LocationID, SupervisorID)
Select EmployeeID, PersonKey, HireDate, LocationID, SupervisorID
From Automart.dbo.Employee

Insert into DimLocation(LocationID, LocationName, LocationAddress, LocationCity, LocationState, LocationZip, LocationPhone)
Select LocationID, LocationName, LocationAddress, LocationCity, LocationState, LocationZip, LocationPhone
From Automart.Customer.Location

Insert into DimVehicle (VehicleID, VehicleMake, VehicleYear)
Select VehicleID, VehicleMake, VehicleYear
From Automart.Customer.Vehicle

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 loc
on loc.LocationID=vs.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

Alter table FactSales Drop constraint [PK_FactSales]

Second Version of DataWarehouse

Create database AutomartDW2
Go
use AutomartDW2
go
Create table DimVehicle
(
 VehicleID int primary key,
 VehicleMake nvarchar(255),
 VehicleYear nvarchar(4)
)
go
Create table DimCustomer
(
 RegisteredCustomerKey int primary key,
 PersonKey int,
 LastName nvarchar(255),
 FirstName nvarchar(255),
 Email nvarchar(255),
 
)

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

)
Go
Create Table DimLocation
(
 LocationID int primary key,
 LocationName nvarchar(255), 
 LocationAddress nvarchar(255), 
 LocationCity nvarchar(255), 
 LocationState nchar(2), 
 LocationZip nchar(10), 
 LocationPhone nchar(13)
)

go

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

Create Table DimEmployee
(
 EmployeeID int primary Key,
 PersonKey int, 
 HireDate Date, 
 LocationID int, 
 SupervisorID 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),
 RegisteredCustomerKey int foreign key references DimCustomer(RegisteredCustomerKey),
 EmployeeID int foreign key references DimEmployee (EmployeeID),
 LocationID int Foreign key references DimLocation (LocationID),
 ServicePRice money,
 DiscountPercent decimal(3,2),
 TaxPercent decimal(3,2),
 Constraint PK_FactSales primary key (VehicleID, DateKey, VehicleServiceKey, RegisteredCustomerKey, EmployeeID, locationID)
)

Loading images dynamically

The complete code for this, and the images I used are available at https://github.com/spconger/DynamicallyShowImages

To load images I ended up using these imports

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;

import javax.imageio.ImageIO;
import javax.swing.ImageIcon;

I sat the image path as a constant. You will have to change the path to make the sample work. To get an easier more flexible path you might try using .getAbsolutePath(), .getPath(), or .getCanonicalPath().

private final String IMG_PATH = "C:\\Users\\stevec\\Workspace\\ImageTest\\Images\\";
private final int IMAGE_WIDTH=315;
private final int IMAGE_HEIGHT=670;

The image is stored in a label using an icon property.(I put the image names in an array).

 BufferedImage img=null;

img = ImageIO.read(new File(IMG_PATH + pictures[i]));
ImageIcon icon = new ImageIcon(img);
JLabel label = new JLabel(icon);

The program starts by letting the user enter how many pictures they want to display. In this case the only valid numbers are 1 to 5. I did not validation. It will crash if you enter any number greater than 5. When they click the button, the action listener, loops for as many times as the user requested, adding labels and pictures to a picturePanel. Then the panel is added to a JPanel that uses a border layout. The frame is given a new size based on the number of images and the image width. Then the frame is revalidated and repainted. These two methods are necessary to redraw the fram with the new picture panel and pictures. Here is the code for the ActionListener.

private class PictureListener implements ActionListener{

@Override
public void actionPerformed(ActionEvent arg0) {
 number = Integer.parseInt(numberField.getText());
 String[] pictures = new String[] {"One.png", "Two.png",
                     "Three.png", "four.png", "Five.png"};
 picturePanel=new JPanel();
 picturePanel.setLayout(new FlowLayout(FlowLayout.LEFT));
 BufferedImage img=null;
 try {
      for (int i=0; i<number;i++){
  img = ImageIO.read(new File(IMG_PATH + pictures[i]));
  ImageIcon icon = new ImageIcon(img);
         JLabel label = new JLabel(icon);
         picturePanel.add(label,BorderLayout.WEST);
           
     }
            panel.add(picturePanel, BorderLayout.CENTER);
     frame.setBounds(200,200,number *(IMAGE_WIDTH),IMAGE_HEIGHT);
     
     frame.revalidate();
     frame.repaint();
 } catch (IOException e) {
     
  e.printStackTrace();
     
    }
     }
  
}

The try catch is required because whenever you try to get a file it might cause an IOException--i.e. the file might not be found.

Here are two pictures of the program running

SQL Injection again

Here is the minimal form

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <p>Enter your old email address
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    </p>
        <p>
            Enter your new email address
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        </p>
        <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
        <p>
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </p>
    </div>
    </form>
</body>
</html>

Here is the code behind the form

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection connect = new SqlConnection
            ("Data source=localhost;initial catalog=Automart;integrated security=true");
        string sql = "Update Customer.RegisteredCustomerb "
            + "Set email= '" + TextBox2.Text + "' Where email ='" + TextBox1.Text +"'";
        SqlCommand cmd = new SqlCommand(sql, connect);
        connect.Open();
        cmd.ExecuteNonQuery();
        connect.Close();


    }
}

One mistake here is to concatenate the text boxes directly into the SQL Statement. Another big mistake is to connect with Admin Permissions. (The integrated security has admin permissions if the current windows user has admin permissions.) The malicious user can enter what they want as a value in the update statement and use -- to comment out any criteria or SQL that follows. in this case the user enters GotYou@hack.com ' -- to cancel out the criteria and set all the email addresses to GotYou@hack.com. The single quote before the dashes is necessary to complete the set statemnent

Here is picture of it running:

Here is an image of the results in SQL Server:

Automart Data Warehouse SQL Script

Use Master
if exists 
 (Select name from sys.DataBases where name = 'AutomartDW')
 Begin
 Drop Database AutomartDW
 end
Go
Create database AutomartDW

--possible dimension tables
--Employee
--Customer
--Vehicle
--Services
--Location
Go
use AutomartDW
Go
Create table DimEmployee
(
  EmployeeDWKey int identity(1,1) primary key,
  EmployeeKey int,
  HireDate Date,
  EmployeeFirstname nvarchar(255),
  EmployeeLastname nvarchar(255),
  SupervisorID int,
  LocationID int

)
Go
Create table DimCustomer
(
      CustomerDwKey int identity (1,1) primary key,
   Email nvarchar(255),
   VehicleID int,
   CustomerLastName nvarchar(255),
   CustomerFirstName nvarchar(255)
)
Go
Create Table DimVehicle
(
 VehicleDWKey int identity (1,1) Primary key,
 LicenseNumber nvarchar(10),
 VehicleMake nvarchar(255),
 VehicleYear nchar(4)
)
go
Create table DimLocation
(
 LocationDWKey int identity(1,1) Primary Key,
 LocationName nvarchar(255),
 LocationAddress nvarchar(255),
 LocationCity nvarchar(255),
 locationState nchar(2),
 LocationZip nchar(10),
 LocationPhone nchar(13)
)
go
Create Table DimService
(
 ServiceDWKey int identity(1,1) primary key,
 ServiceName nvarchar(255)

)

Go

Create Table DimTime
(
 DimTimeKey int identity(1,1) primary key,
 ServiceDate Date,
 ServiceTime Time(7),
 ServiceMonth int,
 ServiceYear int
)
 Go
 Create table ServiceFact
 (
 EmployeeDWkey int not null,
 CustomerDWkey int not null,
 LocationDWKey int not null,
 VehicleDWKey int not null,
 ServiceDWKey int not null,
 DimTimeKey int not null,
 AutoServiceID int,
 VSReferenceID int,
 ServicePrice Money,
 DiscountPercent decimal(3,2),
 TaxPercent decimal(3,2)
 )
 go
 alter table ServiceFact
 Add constraint PK_ServiceFact Primary Key (EmployeeDWkey,
 CustomerDWkey,
 LocationDWKey,
 VehicleDWKey,
 ServiceDWKey,
 DimTimeKey)

 alter table ServiceFact
 Add Constraint Fk_EmployeeDim Foreign Key(EmployeeDwKey)
  References DimEmployee(EmployeeDwKey)

 alter table ServiceFact
 Add Constraint Fk_CustomerDim Foreign Key(CustomerDwKey)
  References DimCustomer(CustomerDwKey)

 alter table ServiceFact
 Add Constraint Fk_LocationDim Foreign Key(LocationDwKey)
  References DimLocation(LocationDwKey)

 alter table ServiceFact
 Add Constraint Fk_VehicleDim Foreign Key(VehicleDwKey)
  References DimVehicle(VehicleDwKey)

 alter table ServiceFact
 Add Constraint Fk_ServiceDim Foreign Key(ServiceDwKey)
  References DimService(ServiceDwKey)

 alter table ServiceFact
 Add Constraint Fk_timeDim Foreign Key(DimTimeKey)
  References Dimtime(DimTimeKey)