Dimension design question
Hello,
I have a question of dimension design. There are two taxes ISS (tax on
services provided by company or individual) and IPTU tax on property.
From a Dimension Person (containing company and individual) there is a need
to identify the property (IPTU) and each member of a company that paid ISS.
The question is how to create the shared Dimension Person in Analysis
Service.
Here the tables with some data:
/****************************
TABLE DimPerson
*****************************/
create table DimPerson(
DimPersonID int not null primary key, NamePerson varchar(20) not null,
CompanyNumber char(14) null, IndividualNumber char(14) null,TypePerson
varchar(10))
-- TypePerson 'Company' or 'Individual'
alter table DimPerson add constraint ck_DimPerson_TypePerson check
(TypePerson in ('Company','Individual'))
go
insert DimPerson values (1,'Fluminense SA','23230200012',null,'Company')
insert DimPerson values (2,'Flamengo SA','23654200012',null,'Company')
insert DimPerson values (3,'Paul Richard',null,'23342200012','Individual')
insert DimPerson values (4,'Mary Scott',null,'232345600012','Individual')
insert DimPerson values (5,'Lucy Adams',null,'56730200012','Individual')
go
/****************************
TABLE DimCompanyMember
*****************************/
create table DimCompanyMember(
CompanyID int not null, MemberID int not null, Cote decimal(5,2),primary key
(CompanyID,MemberID))
alter table DimCompanyMember add constraint fk_DimCompanyMember_CompanyID
foreign key (CompanyID)
references DimPerson(DimPersonID)
alter table DimCompanyMember add constraint fk_DimCompanyMember_MemberID
foreign key (MemberID)
references DimPerson(DimPersonID)
go
insert DimCompanyMember values (1,4,40.00)
insert DimCompanyMember values (1,5,60.00)
insert DimCompanyMember values (2,1,30.00)
insert DimCompanyMember values (2,3,30.00)
insert DimCompanyMember values (2,4,40.00)
go
/*
select Com.DimPersonID CompanyID,Com.NamePerson
CompanyName,Com.CompanyNumber,Mem.NamePerson MemberName,Cote
from DimPerson Com left join DimCompanyMember s on Com.DimPersonID =
s.CompanyID
left join DimPerson Mem on Mem.DimPersonID = s.MemberID
*/
/****************************
TABLE DimTime
*****************************/
create table DimTime (
DimTimeID int not null primary key, Date datetime not null, [Year] char(4)
not null, [Month] varchar(10) not null,MonthNum int not null)
insert DimTime values (20070108,'20070108','2007','January',200701)
insert DimTime values (20070208,'20070208','2007','February',200702)
insert DimTime values (20070210,'20070210','2007','February',200702)
insert DimTime values (20070215,'20070215','2007','February',200702)
insert DimTime values (20070220,'20070220','2007','February',200702)
insert DimTime values (20070308,'20070308','2007','March',200703)
insert DimTime values (20070408,'20070408','2007','April',200704)
insert DimTime values (20080210,'20080210','2008','February',200802)
insert DimTime values (20080215,'20080215','2008','February',200802)
insert DimTime values (20080220,'20080220','2008','February',200802)
/****************************
TABLE FactISS
*****************************/
create table FactISS (
DimPersonID int not null, DimTimeID int not null, Val decimal(10,2))
go
alter table FactISS add constraint fk_FactISS_DimPerson foreign key
(DimPersonID) references DimPerson(DimPersonID)
alter table FactISS add constraint fk_FactISS_DimTime foreign key
(DimTimeID) references DimTime(DimTimeID)
insert FactISS values (1,20070108,12500.00)
insert FactISS values (1,20070208,10500.00)
insert FactISS values (1,20070308,9000.00)
insert FactISS values (1,20070408,17500.00)
insert FactISS values (2,20070208,35500.00)
insert FactISS values (2,20070308,90000.00)
insert FactISS values (2,20070408,87500.00)
go
/****************************
TABLE DimProperty
*****************************/
create table DimProperty (
DimPropertyID int not null primary key,Number char(10) not null, location
varchar(30) null)
go
insert DimProperty values (1,'2341','Aires Saldanha Street 123')
insert DimProperty values (2,'1452','Aires Saldanha Street 124')
insert DimProperty values (3,'3425','Honorio Barros Street 102')
insert DimProperty values (4,'6341','Honorio Barros Street 104')
insert DimProperty values (5,'7332','Farme Amoedo Street 403')
go
/****************************
TABLE FactIPTU
*****************************/
create table FactIPTU (
DimPropertyID int not null,DimPersonID int not null, DimTimeID int not
null,Val decimal(10,2) null)
go
alter table FactIPTU add constraint fk_FactIPTU_DimProperty foreign key
(DimPropertyID) references DimProperty(DimPropertyID)
alter table FactIPTU add constraint fk_FactIPTU_DimPerson foreign key
(DimPersonID) references DimPerson(DimPersonID)
alter table FactIPTU add constraint fk_FactIPTU_DimTime foreign key
(DimTimeID) references DimTime(DimTimeID)
go
insert FactIPTU values (1,1,20070210,2500.00)
insert FactIPTU values (2,3,20070215,3400.00)
insert FactIPTU values (3,4,20070215,1200.00)
insert FactIPTU values (4,3,20070210,900.00)
insert FactIPTU values (5,5,20070220,840.00)
go
insert FactIPTU values (1,1,20080210,2700.00)
insert FactIPTU values (2,3,20080215,3600.00)
insert FactIPTU values (3,4,20080215,1100.00)
insert FactIPTU values (4,3,20080210,930.00)
insert FactIPTU values (5,5,20080220,860.00)
go
date: Wed, 25 Jun 2008 07:58:00 -0700
author: Salles
