Data Modeling

This article provides reporting, performance and design queries for finance database. It includes queries for summarizing columns, annual sales, total sales performance, and more.

Data Modeling

Table of Contents
Part A: Reporting of Queries..................................................................................................2
Query – 1...............................................................................................................................2
Query – 2...............................................................................................................................4
Query – 3...............................................................................................................................9
Query – 4.............................................................................................................................10
Part B: Performance of Query..............................................................................................11
Question - B1......................................................................................................................11
Question - B2......................................................................................................................15
Part C: Design of the Query..................................................................................................17
CA........................................................................................................................................17
CB........................................................................................................................................17
CC........................................................................................................................................19
References...............................................................................................................................21

Part A: Reporting of Queries
Here, we are going to prepare the reporting queries for provided data base that is finance
database. So, user needs to open the Finance DB and it is illustrated as below ("Create and
use an index to improve performance", 2018).
Query: 1
EVALUATE
SUMMARIZECOLUMNS('City'[Sales Territory], "% of Grand Total", 'Order'[% of Grand
Total])
ORDER BY
[% of Grand Total] DESC, 'City'[Sales Territory]

Query: 2
A. Annual Sales
CREATE TABLE SalesPerYear
(Id INT PRIMARY KEY IDENTITY(1,1),
SalesYear INT,
Amount Float)
INSERT INTO SalesPerYear
VALUES ('2014','77'),('2015','66'),('2016','50') ,('2017','60'),('2018','73')
SELECT * ,
CASE
WHEN KpiTarget > Amount THEN -1
WHEN KpiTarget < Amount THEN 1
ELSE
0
END AS Sts
FROM
(
SELECT * , (SELECT AVG(Sl.Amount)*1.1 FROM SalesPerYear Sl) as KpiTarget
FROM SalesPerYear
) as tmp_tbl
order by SalesYear ASC
B.
Case
When IsEmpty
(ParallelPeriod
([Dim Date].[Calendar Date].[Calendar Year],1,
[Dim Date].[Calendar Date].CurrentMember))
Then 0
When VBA!Abs

(
KpiValue( "Product Gross Profit Margin" ) -
(
KpiValue ( "Product Gross Profit Margin" ),
ParallelPeriod
(
[Dim Date].[ Calendar Date].[ Calendar Year],
1,
[Dim Date].[ Calendar Date].CurrentMember
)
) /
(
KpiValue ( "Product Gross Profit Margin" ),
ParallelPeriod
(
[Dim Date].[ Calendar Date].[ Calendar Year],
1,
[Dim Date].[ Calendar Date].CurrentMember
)
)
) <=.02
Then 0
When KpiValue( "Product Gross Profit Margin" ) -
(
KpiValue ( "Product Gross Profit Margin" ),
ParallelPeriod
(
[Dim Date].[ Calendar Date].[ Calendar Year],
1,
[Dim Date].[ Calendar Date].CurrentMember
)
) /
(
KpiValue ( "Product Gross Profit Margin" ),

ParallelPeriod
(
[Dim Date].[Calendar Date].[Calendar Year],
1,
[Dim Date].[Calendar Date].CurrentMember
)
) >.02
Then 1
Else -1
End
SELECT {KPIValue("Product Gross Profit Margin"),
KPIGoal("Product Gross Profit Margin"),

KPIStatus("Product Gross Profit Margin"),
KPITrend("Product Gross Profit Margin")} on 0
FROM [Adventure Works DW]

C.
Query: 3
A.

SELECT
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec',
SUM(total) AS total_yearly
FROM (
SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM cart
WHERE date <= NOW() and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y")) as sub
B.
The representation presented helps to represent each month’s Total Sales Performance,
based on a chart representation. Request of period classification is likewise correct, due to a
direct result of the setting called as, Sort by Column (Zait, 2018).
Query: 4
A.
Evaluate the focal point of a businessperson's execution by following not simply the general
arrangements and edge focuses against spending plan, yet the specific quantifiable targets
perceived previously. Starting now and into the foreseeable future, balance these results with
similar arrangements gatherings, both to the extent size and customer mix. How might they
rate? Take a gander at against the association's exhibited benchmark. What is their rank? The
answer to this gives the business delegate a review of the business execution by giving a

situating of sorting when it appears uniquely in association with the benchmark of the
irganization.
B.
select salesname, orderamount
from (
select
rank() over(partition by s.SalesName order by o.OrderAmount desc) as rnk
,s.SalesName
,o.OrderAmount
From Orders o
join SalesPerson s on o.SalesID = s.SalesID) t
C.
WITH total_sales
AS (SELECT TOP 10 FROM Sales),
(SELECT region, person, count(*) as thousands
FROM sales
GROUP BY region, person
ORDER BY region, count(*) desc
)
, ranked_sales
AS (SELECT region, person, thousands,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY thousands
DESC, person) AS region_rank
FROM total_sales
)
SELECT region, person, thousands
FROM ranked_sales
order by rank;

Part B: Performance of Query
Question: B1
select
year(so.SalesOrderDate) as SalesYear,
c.CountryName,
s.SegmentName,
sp.FirstName,
sp.LastName,
p.ProductName,
count(*) as TotalProductSales,
sum(case when sli.PromotionID = 0 then 0 else 1 end) as TotalPromotionalSales
from SalesOrderLineItem sli
inner join Product p on p.ProductID = sli.ProductID
inner join SalesOrder so on so.SalesOrderID = sli.SalesOrderID
inner join SalesRegion sr on sr.SalesRegionID = so.SalesRegionID
inner join SalesPerson sp on sp.SalesPersonID = sr.SalesPersonID
inner join Region r on r.RegionID = sr.RegionID
inner join Segment s on s.SegmentID = r.SegmentID
inner join Country c on c.CountryID = r.CountryID
where year(so.SalesOrderDate) > 2012
group by
year(so.SalesOrderDate),
c.CountryName,
s.SegmentName,

sp.FirstName,
sp.LastName,
p.ProductName
;
B1A: The above query is used to present the product ID, sales order, sales region, sales
person, region, segments and country depends on sales order date.
B1B: It generally aren't the sort of thing you'd have to discover in a request plan. A record
channel infers that all the leaf-level of the document was hoped to find the information
for the inquiry: When the rundown is a bundled list, this is the same as looking at the
entire table. With only a few exclusions, this isn't incredible; we need to endeavour to
change inspects into searches for, which suggests recuperating the data by just using
the record tree.
B1C
CREATE CLUSTERED INDEX [ix_Customer_ID] ON [dbo].[Customers]
(
[Customer_ID] ASC
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
)

B1D

B1E
In SQL, the database records engages an individual to instanly complete the execution
of SELECT inquiry.The record doesn’t empower much, for small number of tables. In any
case, in case you have tables with a ton of data, records can essentially upgrade execution.
B1F
The records are optional structures related with tables and gatherings that empower
SQL clarifications to execute simply more quickly against a table. Also as the record in this
manual supports you discover information faster than if there were no rundown, an Oracle
Database list gives a snappier access approach to table data. You can use records without
overhauling any request. Your results are the same, anyway you see them simply more
quickly.
B1G
It is possible to make use of lists to empower the Access for finding and sorting out the
records instantly. A document stores the territory of records in light of the field or fields that
you record. Once the access gets the zone from the record, it would then have the capacity to
recuperate the data by moving direct to the correct territory. Henceforth, using a rundown can
be fundamentally snappier than investigating most of the records to find the data.

Question: B2
select
sales_info.SalesMonth,
c.CountryName,
s.SegmentName,
sales_info.PromotionRate,
sales_info.TotalMonthlySales
from Region r
inner join Country c on c.CountryID = r.CountryID
inner join Segment s on s.SegmentID = r.SegmentID
inner join SalesRegion sr on sr.RegionID = r.RegionID
left join (
select
so.SalesRegionID,
so.SalesMonth,
sum(case when sli.PromotionID = 0 then 0.0 else 1.0 end) /
count(*) as PromotionRate,
sum(SalePrice) as TotalMonthlySales
from SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID =
so.SalesOrderID
group by
so.SalesRegionID,
so.SalesMonth
) sales_info on sales_info.SalesRegionID = sr.SalesRegionID
)
select *
from monthly_sales_info
where SalesMonth >= '2016-01-01';

B2A
The overhead query is used to show the region based on county, segment and sales
region and also count the promotion rate to sum the sales price that is total monthly sales
from the sales order.
B2B
Create Index
create index idx_promotions on SalesOrderLineItem (PromotionID, SalesOrderID);
Execution plans discloses how an inquiry might be executed, or how a request was
executed. This is consequently the basic strategies of DBA to research the performing
requests which are an insufficient. Rather than hypothesize why a given inquiry is playing out
countless, putting your I/O through the housetop, you can use the execution plan to recognize
the right piece of SQL code that is causing the issue. For example, it may channel an entire
table-worth of data when, with the most ideal record, it could only backpack out only the
lines you require. The execution plan represents all of this.
B2C
select
b.Name,
SUM(ti.Pages * ti.Rate) as TotalSales,
SUM(ti.Pages) as TotalPages,
bt.Amount,
bt.Month,
bt.Year
from Sales.BranchTarget bt
left join Sales.[Transaction] t
on bt.BranchId = t.BranchId
AND MONTH(t.Date) = bt.Month
AND YEAR(t.Date) = bt.Year

left join Sales.TransactionItem ti
on ti.TransactionId = t.Id
left join Sales.Branch b
on b.Id = bt.BranchId
group by bt.Month, bt.Year, bt.Amount, b.Name
order by bt.Month, bt.Year, b.Name
Part C: Design of the Query
CA
The execution plan refers to the visual description of all the exercises that are
conducted by the database engine, using the true objective for re-establishing the necessary
data based on your inquiry. Now and again, you will be astonished by what they reveal,
despite for the most safe looking request. More number of requests could be sensibly
understood inside the execution plan’s setting. The execution plan will immediately represent
any kind of problem due to the inquiry.
CB
select
basic_metrics.SalesOrderDate,
basic_metrics.SalesOrderNumber,
basic_metrics.SalesPersonID,
margin_calculation.SalesOrderID,
basic_metrics.TotalSalesPrice,
basic_metrics.TotalCost,
basic_metrics.TotalRRP,
basic_metrics.UniqueItems,

basic_metrics.TotalItems,
round(margin_calculation.Margin, 2) as Margin,
round(discount_calculation.PercentageDiscount, 2) as PercentageDiscount
from (
-- Calculate Discount
select
so.SalesOrderID,
sum((pc.RRP * sli.UnitsSold) - SalePrice) / sum(pc.RRP * sli.UnitsSold) as
PercentageDiscount
from SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID
)
discount_calculation
inner join (
-- Calculate Margin
select
so.SalesOrderID,
case
when sum(SalePrice) = 0 then 0
else sum(SalePrice - (pc.ManufacturingPrice * sli.UnitsSold)) /
sum(SalePrice)
end as Margin
from SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID =
so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID
)

margin_calculation on margin_calculation.SalesOrderID =
discount_calculation.SalesOrderID
inner join (
-- basic metrics
select
so.SalesOrderID,
so.SalesOrderNumber,
so.SalesOrderDate,
so.SalesPersonID,
so.SalesMonth,
sum(sli.SalePrice) as TotalSalesPrice,
sum(pc.ManufacturingPrice * sli.UnitsSold) as TotalCost,
sum(pc.RRP * sli.UnitsSold) as TotalRRP,
count(distinct sli.ProductID) as UniqueItems,
sum(UnitsSold) as TotalItems
from SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID,
so.SalesOrderNumber,
so.SalesOrderDate,
so.SalesPersonID,
so.SalesMonth
basic_metrics on basic_metrics.SalesOrderID = margin_calculation.SalesOrderID
where SalesOrderDate > '2016-01-01'
)
CC
1.
Initially, there is a common confusion that in case of absence of new bits of
knowledge, the execution plans has to reliably proceed as previously, i.e., without collecting

the bits of knowledge, somehow can ensure and accreditation the database will basically
perform comparably and create a comparative execution plans. This is on an exceptionally
essential level not veritable. To be sure, an amazing reverse can be legitimate. One may need
to accumulate new estimations to guarantee crucial execution plans don't change. It's the
exhibit of not empowering estimations that can on the grounds that execution needs to out of
the blue change. The next important point refers to the following- While one encounters each
something that may have changed in the database, two fundamental perspectives are
consistently neglected.
3 and 4.
EXPLAIN PLAN’s clarification indicates the design of execution that is selected by the
Oracle streamlining operator to do the following operation, SELECT, INSERT, UPDATE
and DELETE. A declaration's execution plan is the course of action of assignments Oracle
works to run the declaration. The section source tree is the focal point of the execution plan.
The following data is demonstrated:
1) For each table determined in the declaration, get the methodology.
2) Asking for the tables which are referenced using declaration.
3) Data exercises like channel, sort, or accumulation
4) The tables impacted by join undertakings in the declaration, use the join
methodology.
Despite the segment source tree, the course of action table contains information about
the going with:
a) Partitioning
Example: Course of action of got to distributions.
b) Optimization
Example: Cost and cardinality of each assignment.
c) Parallel execution
Example: Allocation system for join inputs.

References
Create and use an index to improve performance. (2018). Retrieved from
https://support.office.com/en-us/article/create-and-use-an-index-to-improve-
performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce
Zait, M. (2018). How do I display and read the execution plans for a SQL statement.
Retrieved from https://blogs.oracle.com/optimizer/how-do-i-display-and-read-the-
execution-plans-for-a-sql-statement