Dat701 Finance Database Answers Assessment Answers

Answer:

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]

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

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],

[Dim Date].[ Calendar Date].CurrentMember (

KpiValue ( "Product Gross Profit Margin" ),

ParallelPeriod (

[Dim Date].[ Calendar Date].[ Calendar Year],

[Dim Date].[ Calendar Date].CurrentMembe ) <=.02

Then 0

When KpiValue( "Product Gross Profit Margin" ) - (

KpiValue ( "Product Gross Profit Margin" ), ParallelPerio

[Dim Date].[ Calendar Date].[ Calendar Year],

[Dim Date].[ Calendar Date].CurrentMember

KpiValue ( "Product Gross Profit Margin" ),

ParallelPeriod

[Dim Date].[Calendar Date].[Calendar Year],

[Dim Date].[Calendar Date].CurrentMember >.02

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]

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

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).

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..

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

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;

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]

In SQL, the database records engagesan 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 oflists 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.

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 consequentlythe 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

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’ssetting. 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:

• For each table determined in the declaration, getthe methodology.
• Asking for the tables which are referenced using declaration.
• Data exercises like channel, sort, or accumulation
• 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:

1. Partitioning

Example:Course of action of got to distributions.

1. Optimization

Example: Cost and cardinality of each assignment.

1. Parallel execution

Example:Allocation system for join inputs.

References

Create and use an index to improve performance. (2018). 

Zait, M. (2018). How do I display and read the execution plans for a SQL statement.