Finance DB: Query Reporting, Performance Tuning, and Design Review

This report provides a detailed analysis of finance database queries, focusing on query reporting, performance evaluation, and design considerations. Part A covers the reporting of various queries, including annual sales analysis and monthly sales performance. Part B delves into query performance, examining the impact of indexing and execution plans. It includes specific queries to demonstrate performance optimization techniques. Part C discusses query design principles, emphasizing the importance of execution plans and database statistics for efficient query execution. The report references external sources to support its findings and recommendations. Desklib provides a platform for students to access similar solved assignments and study resources.

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,