Identification of dimension tables are based on the dimensions that are the companion of the facts for a warehouse. These attributes present in the dimension table is used later for making the queries and data retrieval. The dimension tables identified here are DimClient (stores client details), DimProduction (stores production details of the plays), DimTheatre (stores theatre details), DimCalendar (This dimension table has been created to store each day, week, month, date month name, etc. in a year that act as a whole calendar), DimShowtime (stores show times and names of the performance).
Identification of the fact tables has been done on the basis of the measurements, facts and amount details of the business. The table used for facts has been named FactPerformance here. It stores the foreign key of all the dimension tables along with the amounts, methods, and dates. It is used for analytical evaluation of the business.
Figure 1: Star Schema of Data Mart
Source: created by author
Figure 2: Logical relations between the entities in Data Mart
Source: created by author
DimTheatre
CREATE TABLE DimTheatre (
TheatreID VARCHAR(10) NOT NULL,
Name VARCHAR(45) NULL,
Address VARCHAR(45) NULL,
PRIMARY KEY (TheatreID));
DimProduction
CREATE TABLE DimProduction (
Pid VARCHAR(10) NOT NULL,
Title VARCHAR(45) NULL,
PDirector VARCHAR(45) NULL,
Playauthor VARCHAR(45) NULL,
PRIMARY KEY (Pid));
DimShowtime
CREATE TABLE DimShowTime (
Name VARCHAR(45) NULL,
Hour number(2) NULL,
Minute number(2) NULL,
PRIMARY KEY (hour,minute));
DimClient
CREATE TABLE DimClient (
ClientID VARCHAR(10) NOT NULL,
Title VARCHAR(45) NULL,
Name VARCHAR(45) NULL,
street varchar(45) not null,
town varchar(45) not null,
country varchar(45) not null,
email VARCHAR(45) NULL,
PRIMARY KEY (ClientID));
DimCalendar
CREATE TABLE DimCalendar AS
WITH base_calendar AS
(SELECT CurrDate AS Day_ID,
1 AS Day_Time_Span,
CurrDate AS Day_End_Date,
TO_CHAR(CurrDate,’Day’) AS Week_Day_Full,
TO_CHAR(CurrDate,’DY’) AS Week_Day_Short,
TO_NUMBER(TRIM(leading ‘0’
FROM TO_CHAR(CurrDate,’D’))) AS Day_Num_of_Week,
TO_NUMBER(TRIM(leading ‘0’
FROM TO_CHAR(CurrDate,’DD’))) AS Day_Num_of_Month,
TO_NUMBER(TRIM(leading ‘0’
FROM TO_CHAR(CurrDate,’DDD’))) AS Day_Num_of_Year,
UPPER(TO_CHAR(CurrDate,’Mon’)
|| ‘-‘
|| TO_CHAR(CurrDate,’YYYY’)) AS Month_ID,
TO_CHAR(CurrDate,’Mon’)
|| ‘ ‘
|| TO_CHAR(CurrDate,’YYYY’) AS Month_Short_Desc,
RTRIM(TO_CHAR(CurrDate,’Month’))
|| ‘ ‘
|| TO_CHAR(CurrDate,’YYYY’) AS Month_Long_Desc,
TO_CHAR(CurrDate,’Mon’) AS Month_Short,
TO_CHAR(CurrDate,’Month’) AS Month_Long,
TO_NUMBER(TRIM(leading ‘0’
FROM TO_CHAR(CurrDate,’MM’))) AS Month_Num_of_Year,
‘Q’
|| UPPER(TO_CHAR(CurrDate,’Q’)
|| ‘-‘
|| TO_CHAR(CurrDate,’YYYY’)) AS Quarter_ID,
TO_NUMBER(TO_CHAR(CurrDate,’Q’)) AS Quarter_Num_of_Year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,’Q’)) <= 2
THEN 1
ELSE 2
END AS half_num_of_year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,’Q’)) <= 2
THEN ‘H’
|| 1
|| ‘-‘
|| TO_CHAR(CurrDate,’YYYY’)
ELSE ‘H’
|| 2
|| ‘-‘
|| TO_CHAR(CurrDate,’YYYY’)
END AS half_of_year_id,
TO_CHAR(CurrDate,’YYYY’) AS Year_ID
FROM
(SELECT level n,
— Calendar starts at the day after this date.
TO_DATE(’31/12/2017′,’DD/MM/YYYY’) + NUMTODSINTERVAL(level,’DAY’) CurrDate
FROM dual
— Change for the number of days to be added to the table.
CONNECT BY level <= 365
)
)
SELECT day_id,
day_time_span,
day_end_date,
week_day_full,
week_day_short,
day_num_of_week,
day_num_of_month,
day_num_of_year,
month_id,
COUNT(*) OVER (PARTITION BY month_id) AS Month_Time_Span,
MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,
month_short_desc,
month_long_desc,
month_short,
month_long,
month_num_of_year,
quarter_id,
COUNT(*) OVER (PARTITION BY quarter_id) AS Quarter_Time_Span,
MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,
quarter_num_of_year,
half_num_of_year,
half_of_year_id,
COUNT(*) OVER (PARTITION BY half_of_year_id) AS Half_Year_Time_Span,
MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,
year_id,
COUNT(*) OVER (PARTITION BY year_id) AS Year_Time_Span,
MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date
FROM base_calendar
ORDER BY day_id;
alter table dimcalendar
add primary key (Day_id);
FactPerformance
CREATE TABLE FactPerformance (
Ticktno VARCHAR(45) NOT NULL,
TheatreID VARCHAR(10) NOT NULL,
Pid VARCHAR(10) NOT NULL,
showhour int NOT NULL,
showminute int not null,
ClientID VARCHAR(10) NOT NULL,
amount DECIMAL(10,2) NULL,
Deliverymethod varchar(25) NOT NULL,
PaymentMethod varchar(25) NOT NULL,
PerformanceDate Date,
PRIMARY KEY (Ticktno),
CONSTRAINT fk_Performance_Theatre
FOREIGN KEY (TheatreID)
REFERENCES DimTheatre (TheatreID),
CONSTRAINT fk_Performance_date
FOREIGN KEY (performancedate)
REFERENCES dimcalendar (day_ID),
CONSTRAINT fk_Performance_Production1
FOREIGN KEY (Pid)
REFERENCES DimProduction (Pid),
CONSTRAINT fk_Performance_Showtime1
FOREIGN KEY (showhour,showminute)
REFERENCES DimShowtime (hour,minute),
CONSTRAINT fk_FactPerformance_DimClient1
FOREIGN KEY (ClientID)
REFERENCES DimClient (ClientID));
DimTheatre
Figure 3: Data sources mapped for Dimension table theatre
Source: created by author
DimClient
Figure 4: Data sources mapped for Dimension table client
Source: created by author
DimShowtime
Figure 5: Data sources mapped for Dimension table showtime
Source: created by author
DimProduction
Figure 6: Data sources mapped for Dimension table production
Source: created by author
FactPerformance
Figure 7: Data sources mapped for Fact table FactPerformance
Source: created by author
DimClient
insert into dimclient
select * from client;
Dimshowtime
insert into dimshowtime
select distinct comments,phour, pminute from performance;
DimTheatre
insert into dimtheatre
select * from theatre;
DimProduction
insert into dimproduction
select * from production;
FactPerformance
insert into factperformance
select t.purchase, p.theatreid,p.p,
p.phour,p.pminute,t.clientid,t.totalamount,
t.deliverymethod,t.paymentmethod,p.pdate
from
performance p
inner join
ticketpurchase t
on t.per=p.per;
Query1
select sum(fp.amount) as “Total Sales”,
extract(year from fp.performancedate) as “Year”,
dt.name from factperformance fp
inner join dimtheatre dt on
dt.theatreid=fp.theatreid group by (dt.name,extract(year from fp.performancedate));
Figure 8: Output of Query 1
Source: created by author
Query2
select c.name from dimclient c,
factperformance f where f.clientid=c.clientid group by c.name
having count(extract (month from f.performancedate)) >4;
Figure 9: Output of Query 1
Source: created by author
Query3
select sum(fp.amount) as “Total sales”,
p.title, p.pdirector,
p.playauthor from factperformance fp
inner join dimproduction p on
p.pid=fp.pid group by (p.title,p.pdirector,p.playauthor)
order by sum(fp.amount) desc;
Figure 10: Output of Query 3
Source: created by author
A Data Mart is basically represented as subset of the relational database as the data in the Data Mart is extracted from the relational databases. Although some of the data is generated from the other sources. For example, for better date reference a Calendar table is created and populated here with the help of procedures and formulas. The date key here is referenced to the date column in the fact table. Also, it does not need to populate dates while the system is in use as it will automatically can be referred to the calendar table as it holds all the dates and same dates are not needed to be input in the fact table more than once. Hence, the size of the Data Mart is comparatively less than the Relational databases. Apart from that, the transaction reports are clear as the each particular transaction is stored here as a separate row. Also the relational models requires a lot of joining tables for the query. Whereas the Data Mart has stored all the factual data in the one table. If dimension attributes are needed in the query then also it requires minimum joining conditions.
Anand, N. and Kumar, M., 2013, July. Modeling and optimization of extraction-transformation-loading (ETL) processes in data warehouse: An overview. In 2013 Fourth International Conference on Computing, Communications and Networking Technologies (ICCCNT) (pp. 1-5). IEEE.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and data warehouses. Prospect Press.
Mansmann, S., Rehman, N.U., Weiler, A. and Scholl, M.H., 2014. Discovering OLAP dimensions in semi-structured data. Information Systems, 44, pp.120-133.
Sadoghi, M., Bhattacherjee, S., Bhattacharjee, B. and Canim, M., 2016. L-Store: A real-time OLTP and OLAP system. arXiv preprint arXiv:1601.04084.
Selene Xia, B. and Gong, P., 2014. Review of business intelligence through data analysis. Benchmarking: An International Journal, 21(2), pp.300-311.
Sharma, S. and Jain, R., 2014, February. Modeling ETL process for data warehouse: an exploratory study. In 2014 Fourth International Conference on Advanced Computing & Communication Technologies (pp. 271-276). IEEE.
Sidi, E., El Merouani, M., Amin, E. and Abdelouarit, A., 2016. Star Schema Advantages on Data Warehouse: Using Bitmap Index and Partitioned Fact Tables. International Journal of Computer Applications, 975, p.8887.
Sohail, A. and Dominic, P.D.D., 2015. From ER model to star model: a systematic transformation approach. International Journal of Business Information Systems, 18(3), pp.249-267.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download
