1. Our Systems are Microsoft Dynamics OLTP, we move data from the Source Systems through Staging to a Data Warehouse. Our report primarily run off the Data Warehouse.

Why do we use a data warehouse?________________________________________________________

2. Our data warehouse is star schema based with minimal snow flaking and large conformed dimensions.

How does the Star Schema differ from a transactional relational schema and why is this good for reporting?

______________________________________________________________________________

3. A client sends in a ticket for a Balance Sheet report in which he says Other Assets = 30,000 when it should be 100,000. She states she is running the report with the Parameters Fund I and As Of Date = 12/31/2014.

a. You are shown how to go to production (a SharePoint integrated SSRS report url) and run the report. When you run it the Other Assets line is 30,000 for those parameters.

b. You are given a code copy of the report in TFS.

c. You are given a copy of the production data warehouse on a development server. You are told you may mess up the dev copy however you like.

Balance Sheet

Fund I

As Of 12/31/2014

Assets

Investments

$12,000,000

Other Assets

30,000

Total

12,030,000

Liabilities

Expenses

100,000

Partners Capital

General Partner

2,000,000

Limited Partners

10,000,000

Total

12,000,000

Total Liabilites and Partners' Capital

$ 12,100,000

How do you troubleshoot the clients request?

_____________________________________________________________________________________

4. The query the report uses looks something like this:

Alter Stored Procedure spv_Report

(@Fund varchar(100), @AsOfDate datetime)

As

Begin

Create Table #Results

( Reportline varchar(100),

Amount decimal(28,12))

Insert into #Results

Select ReportLine, Sum(Amount)

From FactGL As GL

Join AuxReportLineMatrix ARLM

on GL.AccountKey = ARLM.AccountKey

Where Fund = @FundKey and PostingDate

Group by ReportLine

Insert into #Results

Select ReportLine, Sum(Amount)

From FactCommitment As CMT

Where Fund = @FundKey and PostingDate

Group by ReportLine

Insert into #Results

Select ReportLine, Sum(Amount)

From FactMemo As Mem

Join AuxReportLineMatrix ARLM

on MEM.AccountKey = ARLM.AccountKey

Where Fund = @FundKey and PostingDate

Group by ReportLine

Update #Results set Amount = Amount *.12 From #Results R

Join AuxReportLineMatrixFx ARLM

on R.ReportLine = ARLM.ReportLine

Where ARLM.CurrencyCode = 'disc'

Delete from #Results where Amount = 0

Select * from #Results

End

How do you find out where the Other Assets is comes from in the database tables?

_____________________________________________________________________________________

5. If you found that this query is the only part of the procedure that returns or changes the Other Assets line.

Insert into #Results

Select ReportLine, Sum(Amount)

From FactGL As GL

Join AuxReportLineMatrix ARLM

on GL.AccountKey = ARLM.AccountKey

Where Fund = @FundKey and PostingDate

Group by ReportLine

How do you get more detail on what makes up the query?

_____________________________________________________________________________________

6. If in looking at more detail you find these values, and you suspect that you should eliminate reversals how would you do it?

Report Line

Amount

Reversed

Entry No_

Other Assets

100,000

0

1

Other Assets

-70,000

1

2

Other Assets

70,000

1

3

Other Assets

-70,000

1

4

Insert into #Results

Select ReportLine, Sum(Amount)

From FactGL As GL

Join AuxReportLineMatrix ARLM

on GL.AccountKey = ARLM.AccountKey

Where Fund = @FundKey and PostingDate

Group by ReportLine

____________________________________________________________________________________

7. Software Development Lifecycle:

A. What are the steps of the SDLC?

B. In the scenario we just discussed what phases of the SDLC did you just do?

C. What steps in the SDLC do you need to complete in order to complete this request and explain how you would do it?

D. How would a support role follow all the SDLC steps to change a clients report?

1. Our Systems are Microsoft Dynamics OLTP, we move data from the Source Systems through Staging to a Data Warehouse. Our report primarily run off the Data Warehouse Microsoft NAV CRM ste Data Warehouse Why do we use a data warehouse? 2. Our data warehouse is star schema based with minimal snowflaking and large conformed dimensions How does the Star Schema differ from a transactional relational schema and why is this good for reporting? 3. A client sends in a ticket for a Balance Sheet report in which he says Other Assets = 30,000 when it should be 100,000. She states she is running the report with the Parameters Fund I and As Of Date=12/31/2014 a. You are shown how to go to production (a SharePoint integrated SSRS report url) and run the report. When you run it the Other Assets line is 30,000 for those parameters b. You are given a code copy of the report in TFS c. You are given a copy of the production data warehouse on a development server. You are told you may mess up the dev copy however you like Hence Sheet Full ASO A S12 Der Aste 13 BERLIN IT IN 3. A client sends in a ticket for a Balance Sheet report in which he says Other Assets - 30,000 when it should be 100,000. She states she is running the report with the Parameters Fund I and As or Date - 12/31/2014 a. You are shown how to go to production (a SharePoint integrated SSRS report url) and run the report. When you run it the Other Assets line is 30,000 for those parameters. b. You are given a code copy of the report in TFS c. You are given a copy of the production data warehouse on a development server. You are told you may mess up the dev copy however you like. Ble Sheet PIT ASORT2012014 A ther 13 1 Expo Tap Kerala Limited Leal 30. 12. W Tutal Liabetes and Parin Capital 21 How do you troubleshoot the client's request? 4. The query the report uses looks something like this: Alte Sted crew Repen un arch100% Omdat A. Thegi Tevens Latine anche Ames.1211 Lecce Sclect Repeelise. Sami Amet PAROL Jose Auskepellatrix AREM SEGLARE ARE MANY Where Fund Funkey and LA Kabyli Sclet Rage SA Factory ACME Whey und Pou AOFD by pl. Select Rep. SAM From Fact Me ARMARIM MEMAHARIMA Where Fondadey and Posting 4. The query the report uses looks something like this Als Sted Prescrespo Report Find 100% Ada) AN Regis Report Am12 re Select SAE) PFLANGE AMARIM GLASARIM When Pandan key and Post- JAFT by Report Scler Report. Sex Free Femmes CMT Where they doc RAF King Select optime Summer From PACEMAR Rein AutReport Matris ALLM MEMAKARLMARK Where Fan Funkey and TEAM Kung by Lapore Update AA 12 San Anton MARIM RRARI M Raine Whex ARINO Deler besser Seletes How do you find out where the Other Assets is comes from in the database tables? 5. If you found that this query is the only part of the procedure that returns or changes the Other Assets line lettera Beras Scler Retin, San Fru FC AGL AMARIM GL Accountry- AREMACY Where and key ending Flute hep by Reportline How do you get more detail on what makes up the query? 6. If in looking at more detail you find these values, and you suspect that you should eliminate reversals how would you do it? Repuine Amery Oh. ARNARLM Report DARI Report Who ARE Cake TA Select from Rolle How do you find out where the Other Assets is comes from in the database tables? 5. If you found that this query is the only part of the procedure that retums or changes the Other Assets line. Intereses Select Rapolin SA The FIGLA GL NA RAMIR ARIM GLACK - ARILMA.com When they and Da OFD Reply Repeline How do you get more detail on what makes up the query? 6. If in looking at more detail you find these values, and you suspect that you should eliminate reversals how would you do it? Hep Acne ye Other Acte 100.000 Ohe 2000 onlin Select a SA From PAGE Perpenter AREM Cewek ARM .kay Where und Funkey and Pong Kity Report 7. Software Development Lifecycle: A. What are the steps of the SDLC? B. In the scenario we just discussed what phases of the SDLC did you just do? C. What steps in the SDLC do you need to complete in order to complete this request and explain how you would do it D. How would a support role follow all the SDLC steps to change a client's report? 1. Our Systems are Microsoft Dynamics OLTP, we move data from the Source Systems through Staging to a Data Warehouse. Our report primarily run off the Data Warehouse. Microsoft Microsoft CRM NAV B (stg Data Warehouse Why do we use a data warehouse? 2. Our data warehouse is star schema based with minimal snow flaking and large conformed dimensions. Dim Fund Dim Date Fact General Ledger Fact Commitment Dim Investor Dim Investment How does the Star Schema differ from a transactional relational schema and why is this good for reporting? 3. A client sends in a ticket for a Balance Sheet report in which he says Other Assets = 30,000 when it should be 100,000. She states she is running the report with the Parameters Fund I and As Of Date = 12/31/2014. a. You are shown how to go to production (a SharePoint integrated SSRS report url) and run the report. When you run it the Other Assets line is 30,000 for those parameters. b. You are given a code copy of the report in TFS. c. You are given a copy of the production data warehouse on a development server. You are told you may mess up the dev copy however you like. Balance Sheet Fund I As of 12/31/2014 Assets Investments Other Assets Total Liabilities S12.000.000 30,000 12.030.000 Expenses 100,000 Partners Capital General Partner Limited Partners Total 2,000,000 10,000,000 12,000,000 Total Liabilites and Partners' Capitals 12,100,000 How do you troubleshoot the client's request