+1(978)310-4246 credencewriters@gmail.com
  

This assignment relates to the following Course Learning Requirements:
CLR 1 – Plan, Prepare, Install, Configure, and Use a market-leading Database
Management System, Data Modeling Engineering Tools, and Open
Source Software.
CLR 2 – Develop Advanced Database Design and Normalization
CLR 7 – Explain basic data warehousing concepts. Understand basic concepts of
data warehousing
Background
Final Close is a 3 year old company that developed an e-store platform. Their platform
allows their brick-and-mortar clients do e-commerce through e-stores and virtual
storefronts. What the company developed is more than just a brick-to-click solution. Their
framework has automation in place that responds immediate to changes in each client’s
partnerships and offerings. To achieve this, Final Close builds integrations between their
platform and their clients’ business management systems. This allows their platform’s data
be synchronization to their clients’ every 24 hours.
Instructions
Complete all the steps. Paste in the document only the work that’s requested. When
submitting your images make sure you show the complete information along with your
name and student number.
E-Store Database Design
Step
Task
1
System Design
1
System Designs are designs that give a high level view of the components involved. They
are the road maps for your corporate IT infrastructure. They also help you look for
performance bottlenecks, scaleability issues, and opportunities to use vendors and opensource solutions. System designs include components such as user groups, applications,
data/process management systems, and the groups of data being managed.
User Groups. These are the categories of users that use systems. High level user
groups can include customers/clients, employees, management, and executives.
You can provide more detailed user groups by their titles and demographic
information.
Applications. These are programs that help users interface with other system
components. They can include data entry applications, reporting systems,
performance tools, business intelligence, and data mining tools.
Management Systems. These are programs that manage computer processes
and/or data. They can be used to manage data, services, quality and
performance.
Databases. These are files and repositories that store data. They cannot do
anything on there own. They are accessed and manipulated by users,
applications, and management systems.
System Diagrams are visual diagrams of your system designs. They give a high level
overview of systems and show which components have which responsibilities. They also
include how components talk to each other through arrows. The tail of the arrow is on
component requesting/sending the data.
To facilitate understanding system diagrams are designed to be read from left to right,
top to bottom like a book.
In the diagram above Component 1 is sending a message / data / request to Component
2.
You can use boxes to group components together into systems. You can use arrows that
describe how a component from one system interacts with a component from another
system.
2
Database Systems
A system diagram helps designers see at a glance the differences of system behaviour
from a high level. This is useful for visually showing the differences between database
systems.
Operational Systems. Or OPS Systems, are used to support daily operations of the
company. Users view data using reporting applications and view/manipulate data though
data entry applications. These applications send/receive data from the OPS DBMS. The
OPS DBMS sends/retrieves data from the Operational Database or OPS DB.
2
Data Warehouse Systems. Or D/W Systems, are used to load and aggregate data on a
daily, weekly, or monthly basis. This loaded data is read only. Users request applications
to tell the D/W DBMS to load the data into the D/W DB by notifying the OPS DBMS. The
Business Intelligence system retrieves the data by requesting the D/W DBMS to send the
data over to it.
The D/W DB is designed for heavy computer processing. Because of this data warehouses
typically doesn’t prepare data for operational reporting. Operational reporting is done
strictly by the Operational System
Business Intelligence Systems. Or BI Systems, are used to help users see patterns in the
data through comparisons, inclusions, and omissions. The user requests the data from the
application which tells the BI DBMS to retrieve the data from the BI D/W and send that
data to the application or reporting system the user is using. On a daily, weekly, or
monthly schedule the BI DB is loaded with new data from the D/W System.
The user would retrieve the data and then use filtering and pivots to do analysis. The user
shouldn’t need to know how the data is stored in the OPS DB nor the D/W DB.
System Diagram
3
i.
ii.
iii.
Create a new document using a drafting or visual modeling tool of your choice
Draw the operational system based on diagram examples in the previous steps
Add the arrows between your operational system components that best describe
3
the system’s behaviour. Label each arrow with at most three words.
Repeat steps (ii) and (iii) for the data warehouse system.
Repeat steps (ii) and (iii) for the business intelligence system.
Add the arrows between the operational, data warehouse, and business intelligence
system components that best explains their interactions. Label each arrow with at
most three words.
vii. Add a separate component called Client to represent customers’ systems. Add
arrows to and from Client describing how the three systems could interact with that
component. Use what you know about Final Close as a guide.
viii. Add your name and student number at the bottom left corner of your diagram.
iv.
v.
vi.
Submit System Diagram
4
Paste the screen shot of your System Diagram after the red text below.
Crop your image to exclude tool bars, menu options, and object browsers.
Your image must have your name and student number.
Resize your diagram if it doesn’t fit the page.
Data Warehouses versus Business Intelligence
Because of marketers, self-proclaimed experts, and Google there is a lot of muddiness on
what actually is the difference between data warehouses and business intelligence.
5
Data Warehouses are for computers. Data warehouses aggregate data from the
operational database and put in all possible aggregates into its D/W database.
For instance, if sales data was stored in the data warehouse, there would be a
row for how much was sold each and every day the company was operating.
There would be rows for the total sold for every week. Rows for every month.
Rows for every quarter. Rows for every year. And rows for every 5 years.
Business Intelligence are for people. Business Intelligence takes data from the
data warehouse and structures it into data sets for easy analysis and
manipulation. To optimize system performance BI database are often separate
from the data warehouse. The data sets are stored in the business intelligence
database. This data is often presented to the user as visualizations, reports,
dashboards, and extracts.
In your System Diagram you modeled the data warehouse and business intelligence
systems as separate systems. Don’t assume the data warehouse and the business
intelligence are running on the same DBMS and DB. That is a design decision often made
by an expert based on talent, cost, and time lines.
Data Warehouse
6
The data warehouse has all possible combinations of aggregates a user could possible ask
for and more. Aggregates may include SUM, COUNT, AVERAGE, MIN, and MAX.
Aggregates are done only on measures.
A measure is something that changes over time. It can be — frequency of an event (COUNT) such as checking into a hotel
– frequency of something being active/inactive (COUNT) on a specific day or days
4
like an insurance policy
– an amount received/paid (SUM) such as a purchase from a customer
These measures are stored in a table called a fact table. The fact table is then sliced and
diced using dimensions. Dimensions are things that don’t change over time. Dimensions
are used to determine how the data is aggregated in the fact table.
Dimensions are stored in dimension tables. Dimension tables have an ID along with
characteristics (fields) that describe the dimension. Dimensions can be for time, location,
and categories. Characteristics are static and do not change over time.
Fact tables are joined only to dimension tables. This structure is know as a star schema.
Here is a small star for aggregating the sale amount by SUM for each and every possible
combination for time and customer category.
Not all characteristics will be filled in for each row in the dimension table. If all
characteristics are blank except for the YearNum being 2010 then that means
that row in the dimensional table is for just the year 2010. If the YearNum is 2010
and the MonthNum is 12 then that row is for December, 2010.
If multiple fact tables share the same dimension tables then the structure is known as a
constellation.
It’s a good practice that fact tables are not joined to other fact tables; and dimension
tables are not joined to other dimension tables.
Data Warehouse Database Design
7
i.
ii.
Create a new Relational Model in Oracle Data Modeler as your new data warehouse
database design
Examine the E-Store database design. Know what fields there are and how they are
related to the other fields in the diagram.
Fact Tables
8
i.
ii.
Add a fact table using the fields from the PARTNERSHIP table to create your
measures. You will add a count for each time the partnership is Activated, Active,
and Terminated. Post-Fix the word “Count” at the end of each these three measures’
names.
Add a fact table using the fields from the PURCHASE table to create your measures.
5
iii.
You will count each time a purchase is made and sum each purchase amount. PostFix “Count” and “Sum” to these two measures accordingly.
Add a time dimension table and join it to both fact tables. Add the new
characteristics WeekNum, TwoYearNum and TenYearNum to the time dimension
table.
WeekNum is week 1, 2, 3 or 4 of a month
TwoYearNum, FiveYearNum, and TenYearNum are for combining totals
together from several years. If the FiveYearNum is 2020 then this row will be for
the years 2016 to 2020. If the FiveYearNum is 2020 and the YearNum is 2016
then the row is only for 2016. However, this also gives the user which five year
range that 2016 belongs to so they can easily compare data to another data set.
iv.
Add a customer dimension table and join it to both fact tables. Add the new
demographic characteristics AgeRange (varchar), Sex, Ethnicity, Gender, and
Is_Indigenous.
AgeRange is a text characteristic like “40 – 55 years”. You would use text rather
than numeric because age ranges aren’t broken down equally by number of
years. It’s much harder to analyze for patterns if that is done.
v.
vi.
Examine your customer dimension and see how it derived from the CUSTOMER and
ADDRESS tables in the E-store design.
Examine the ENTITY ADDRESS tables in the E-store design. Add an entity dimension
with the appropriate measures and join it to your fact tables. Add the new
characteristic PartnerTierLetter. Remember you don’t store individual names and
information in a dimension.
PartnerTierLetter using a ranking system to determine its priority or worth.
Letters often used are A, B, C, D. This ranking can also be used for products,
customers/clients, and marketing opportunities.
vii. Examine the PRODUCT and E_STORE tables in the E-store design. Add a product and
e-store dimension with the appropriate characteristics. Join these to your fact tables.
viii. Add your name and student number at the bottom left corner of your diagram.
Submit Data Warehouse Database Design
9
Paste the screen shot of your Data Warehouse Database Design Diagram after the red
text below.
Crop your image to exclude tool bars, menu options, and object browsers.
Your image must have your name and student number.
Resize your diagram if it doesn’t fit the page.
Business Intelligence
10
Business intelligence provides data to users in a form that makes it easy for them to see
patterns and trends. Users do this by comparing data to other data. The easier you make
it for them to do these comparisons, the more effective the business intelligence will be.
Many advocate that business intelligence is designed to help users answer questions; that
6
you should focus your design around those key questions that a company has about their
business. Key questions are a short list of formal questions usually ten or less that’s the
most important to ask for a specific topic.
(If there are more than ten questions that list is called a checklist.)
However, the bigger problem is that users do not know which questions are the right
questions to ask. So the emphasis of effective business intelligence should be helping the
user uncover what questions they need to be asking. Supporting the activity of doing
comparisons helps users discover these better questions.
A direct way to support their comparison activities is to give users the most likely data
they will ever need. Then give them the choice to filter that data down to what they
exactly need. To do this, you would use data sets. A data sets is an aggregate of data from
several tables merged together around a specific topic. A data set could consist of
denormalized data.
A data set for sales data could look like the following.
This data set is stored as a table in the business intelligence database. Rather than
accessing the operational database or data warehouse databases for data, the user would
access just the data sets in the BI DB.
Business Intelligence Database Design
i.
ii.
11
iii.
iv.
v.
vi.
ix.
Create a new Relational Model in Oracle Data Modeler as your new business
intelligence database design
Examine the two star schemas in your data warehouse database design. Each fact
table is the center of each star.
Add a partnership data set table
Add all characteristics of each dimension for the partnership fact table as fields in
the data set table.
Add all measures from the partnership fact table to the data set table.
Repeat steps (iii) to (v) for creating the purchase data set table.
Add your name and student number at the bottom left corner of your diagram.
Submit Business Intelligence Database Design
12
Paste the screen shot of your Business Intelligence Database Design Diagram after the
red text below.
Crop your image to exclude tool bars, menu options, and object browsers.
Your image must have your name and student number.
Resize your diagram if it doesn’t fit the page.
7

Purchase answer to see full
attachment

  
error: Content is protected !!