OLAP and Cubes: New Dimensional Model and Queries

This report discusses the new dimensional model for retail stores, including tables descriptions and OLAP queries to analyze customer behavior and sales data. It also covers recommendations for improving the data warehouse. The subject is OLAP and Cubes, and the course code and college/university are not mentioned.

Assignment 02: OLAP and Cubes

Table of Contents
New dimensional model that is improved...................................................................................................2
Tables Description.......................................................................................................................................3
Create cubes and OLAP queries...................................................................................................................8
Recommendation......................................................................................................................................12
References.................................................................................................................................................15

New dimensional model that is improved
Relationship for supermarket
The above figure shows the improved version dimensional model of the old one consisting of ten
relations which are all related with the retails dimensional model to get all transaction details.

Description of the relations
Customer details
Feedback relation

Mailshot Campaigns relation
Product_Promotion relation

Product_In_Warehouses relation
Ref_Calendar relation

Ref_Invoice_Status_Codes relation
Ref_Payment_Methods relation

Retail_Dimensional_Model relation
Store_Details

Creating cubes and queries
The underneath OLAP inquiry is utilized to discover the which client purchased more number of
items from store so we can give the a few offers to who got more number of products and also
we can analysis the more sales at which store. Based on our database store ID S_100 has more
sales and customer C1000 got 300 products. Likewise we can analysis for the rest of the stores
who sold very less number of products such as store _id 105. We can also analysis total number
of products sold in all store by sum of all products in all stores is 950.
This query is used to analysis the product price of the product. Here we got price of the product
100 rs from warehouse 1 and 3 which is selling milk and battery.

This query is used to analysis the product price of the product. Here we got price of the product
200 rs from warehouse 1 which is selling story books.
The below query is used to check what are all the stores are available in US which has store1 and
4.

The below query is used to check what are all the stores are available in UK which has store1,3
and 4.
Analysis the feedback of the customer by using below query here which product has highest
price , got good feedback and values are 5.

The below query result states that customer C1000 only purchase details and total cost of the
product is 155000.

New Improvements on the dimensional model
Transaction Fact Tables
A fact table is the focal table in a star construction of an information distribution center. A fact
table stores quantitative data for examination and is regularly renormalized (ADMA
(Conference), Zhou, Zhang, & Karypis, 2012).
A transitional fact table that is joined to the measurement table. A middle of the road fact table is
joined, thusly, to a halfway measurement table to which the fact table is joined. The numerous
to-numerous connections between the middle of the road fact table and both the measurement
tables in the relationship and the halfway measurement makes the numerous to-numerous
connections between individuals from the essential measurement and measures in the measure
assemble that is determined by the relationship. So as to characterize a numerous to-numerous
relationship between a measurement and a measure bunch through a middle of the road measure
amass, the halfway measure bunch must impart one or more measurements to the first measure
bunch (Baker, Fletcher, Garvey, & Sweazy, 2015).
A fact table works with measurement tables. A fact table holds the information to be
investigated, and a measurement table stores information about the routes in which the
information in the fact table can be broke down. Hence, the fact table comprises of two sorts of
segments. The remote keys section permits joins with measurement tables, and the measures
segments contain the information that is being investigated. Assume that an organization offers
items to clients. Each deal is a fact that happens, and the fact table is utilized to record these facts
(Han, Kamber, & Pei, 2012a).

A line in an exchange fact table compares to an estimation occasion at a point in space and time.
Nuclear exchange grain fact tables are the most dimensional and expressive fact tables; this
powerful dimensionality empowers the greatest cutting and dicing of exchange information.
Exchange fact tables might be thick or meager on the grounds that columns exist just if
estimations occur. These fact tables dependably contain a remote key for each related dimension,
and alternatively contain exact time stamps and decline dimension keys. The deliberate numeric
facts must be steady with the exchange grain (Han, Kamber, & Pei, 2012b).
Snapshot Fact Tables that have accumulated
At the point when building fact tables, there are physical and information limits. A definitive size
of the item and in addition access ways ought to be considered. Including files can help with
both. In any case, from a legitimate outline viewpoint, there ought to be no confinements. Tables
ought to be assembled in light of present and future prerequisites, guaranteeing that there is
however much adaptability as could reasonably be expected incorporated with the configuration
to take into account future upgrades without rebuilding the information.
Despite the fact that comparative phrasing is utilized for intelligent table and physical table
articles, for example, the idea of keys, sensible tables and participates in the Business Model and
Mapping layer have their own arrangement of tenets that vary from those of social models. For
instance, sensible fact tables are not required to have keys, and legitimate joins can speak to
numerous conceivable physical joins (Lovejoy & Langley Research Center, 2013).
Sensible tables, joins, mappings, and different articles in the Business Model and Mapping layer
are ordinarily made consequently when you move and customize objects from the Physical layer
to a specific plan of action. After these articles have been made, you can perform undertakings

like making extra consistent joins, performing computations and changes on sections, and
including and expelling keys from measurement and fact tables.
A line in an amassing preview fact table outlines the estimation occasions happening at
predictable strides between the start and the finish of a procedure. Pipeline or work process
forms, for example, arrange satisfaction or case handling, that have a characterized begin point,
standard middle of the road steps, and characterized end point can be modeled with this sort of
fact table. There is a date outside key in the fact table for each basic turning point simultaneously
(Rajesh, Narisimha, & Rupa, 2012). An individual column in a collecting depiction fact table,
comparing for example to a line on a request, is at first embedded when the request line is made.
As pipeline advance happens, the gathering fact table line is returned to and refreshed. This
reliable refreshing of aggregating preview fact columns is interesting among the three kinds of
fact tables. Notwithstanding the date remote keys related with each basic procedure step,
amassing depiction fact tables contain outside keys for different dimensions and alternatively
contain deteriorate dimensions. They frequently incorporate numeric slack estimations
predictable with the grain, alongside point of reference fulfillment counters.
Dimension Surrogate Keys
A dimension table is composed with one segment filling in as a one of a kind primary key. This
primary key can't be the operational framework's normal key in light of the fact that there will be
various dimension columns for that characteristic key when changes are followed after some
time. Also, characteristic keys for a dimension might be made by in excess of one source
framework, and these normal keys might be incongruent or ineffectively directed. The DW/BI
framework needs to guarantee control of the primary keys of all dimensions; instead of utilizing

express normal keys or regular keys with attached dates, you ought to make unknown whole
number primary keys for each dimension (Sampson, 2015). These dimension surrogate keys are
basic whole numbers, allocated in arrangement, beginning with the esteem 1, each time another
key is required. The date dimension is absolved from the surrogate key govern; this exceedingly
predictable and stable dimension can utilize a more significant primary key.
In numerous associations, old or inert generation codes are reused. For instance, a bank may
reuse a record number on the off chance that it has been dormant for a drawn out stretch of time
or that record has been shut by the client. Also out of date item codes could be reused by a
supermarket. Such changes don't influence the operational frameworks, as operational
frameworks don't keep recorded information. An information stockroom then again holds
information for quite a long time and reuse of codes can cause a contention. Utilization of
surrogate keys causes us make another record in the measurement table that will have distinctive
qualities in the vast majority of the traits (Sampson, 2015).
Recommendations
This report talked about the retails store information distribution center and made blocks and
OLAP questions. It additionally contains enhanced current dimensional model and distribution
center to catch all significant data that applies to clients, for example, socioeconomics. The new
dimensional model used to better comprehends the client base by distinguishing client practices
and along these lines potential deals and showcasing openings. The new information stockroom
will bolster client from low-esteem to high-esteem and the potential open doors uncovered in the
buyer flow and furthermore backings to investigation the strategically pitch, up-offer and down-
offer or score up points of interest.

References
ADMA (Conference), Zhou, S., Zhang, S., & Karypis, G. (2012). Advanced data mining and
applications: 8th International Conference, ADMA 2012, Nanjing, China, December 15-18,
2012 : proceedings. Berlin: Springer.
Baker, P., Fletcher, L., Garvey, S., & Sweazy, L. (2015). Data divination: Big data strategies.
Han, J., Kamber, M., & Pei, J. (2012). Data mining: Concepts and techniques. Amsterdam:
Elsevier/Morgan Kaufmann.
Han, J., Kamber, M., & Pei, J. (2012). Data Cube Technology. Data Mining, 187-242.
doi:10.1016/b978-0-12-381479-1.00005-8
Lovejoy, A. E., & Langley Research Center. (2013). PRSEUS pressure cube test data and
response.
Rajesh, P., Narisimha, G., & Rupa, C. (2012). Fuzzy based privacy preserving classification of
data streams. Proceedings of the CUBE International Information Technology Conference on -
CUBE '12. doi:10.1145/2381716.2381865
Sampson, A. (2015a). Microsoft SQL Server 2014: Viewing Cube Data and Metadata in Cube
Designer.
Sampson, A. (2015b). Microsoft SQL Server 2014: Checking Cube Data Through the Browser.