CODEWITHSUNDEEP
sqlpowerbipowerquery
Warric Ritchie
Warric Ritchie

Reputation: 141

Viewing a programs database query and duplicating

We have a server with a database on full of our financial records where information is captured through a property management program called MDA. MDA has reporting features.

Is it possible to see what specifically the program is querying the database so I can duplicate that specific report table in excel etc.

MDA help page does give the sql code for some reports but not all and some code provided does not give the same information. So is it possible perhaps from the servers side to see exactly what is being done?

Edit:

Below is a captured query:

Update tmp  Set 
tmp.TenantReceipts                  = a.TenantReceipts, 
tmp.OtherReceipts                   = a.OtherReceipts,      
tmp.Disbursments                    = a.Disbursments, 
tmp.AgentFeesCollectionCommission   = a.AgentFeesCollectionCommission, 
tmp.AgentFeesManagementFee          = a.AgentFeesManagementFee, 
tmp.AgentFeesBankCharges            = a.AgentFeesBankCharges,   
tmp.AgentOnlyCashMovements          = a.AgentOnlyCashMovements,  
tmp.PaidToOwners                    = a.PaidToOwners 

From #tmpResultSet tmp
Inner Join 
(Select PropertyID, IsNull(CashBookID, 0) as CashBookID,    

--Tenant Receipts   Sum of tenant receipts (type = 2) and tenant payments (type = 3), excluding agent only transactions, for either financial or owner statement period. 
Sum (Case When TransactionTypeID in (2,3) and ExcludeFromOwnerReports = 0 then InclusiveAmount else 0 end)   as TenantReceipts, 

--Other Receipts    Sum of suppier receipts (type = 12 not used) and property receipts (type = 22), excluding agent only transactions, for either financial or owner statement period.
Sum (Case When TransactionTypeID in (12,22) and ExcludeFromOwnerReports = 0 then InclusiveAmount else 0 end)   as OtherReceipts,    

--Disbursements Sum of suppier payments (type = 13) and property payments (type = 23), excluding agent only transactions, for either financial or owner statement period.
--Excludes transactions designated as Agent Fees in Global Options i.e Collection Commission, Management Fee, Bank Charge son Cash, Bank Charges on Cheques (includes all payments)  and Bank Charges on Cash, as well as amounts Paid to Owners. 
Sum (Case When TransactionTypeID in (13,23) 
                --and IsNull(afgo.TransactionCodeID, 0 ) = 0 
                and tx.TransactionCodeID not in (@CollectionCommissionID)
                and tx.TransactionCodeID not in (@ManagementFeeID) 
                and tx.TransactionCodeID not in (@BankChargesOnCashID)
                and tx.TransactionCodeID not in (@BankChargesOnChequesID)
                and tx.TransactionCodeID not in (@OwnerPaymentsID)  
                and ExcludeFromOwnerReports = 0 then InclusiveAmount else 0 end)   as Disbursments, 

--Agent Fees    For Amounts Paid:
--If any of the Agent Fee components are unchecked on the parameter screen, they will be excluded from the calculation.
--Sum of supplier payments (type = 13) and property payments (type =23), excluding agent only transactions, for either financial or owner statement period. 
--Further restricted to transactions designated in Global Options as Collection Commission, Management Fee and Bank Charges, and if ticked on the parameter form.

--Agent Fees    Collection Commission
Sum (Case When TransactionTypeID in (13, 23) and ExcludeFromOwnerReports = 0 and @AmountsAccrued = 0 and @ShowCollectionCommission = 1
                and tx.TransactionCodeID in (@CollectionCommissionID) then InclusiveAmount else 0 end)   as AgentFeesCollectionCommission,  

--Agent Fees    Management Fee  
Sum (Case When TransactionTypeID in (13, 23) and ExcludeFromOwnerReports = 0 and @AmountsAccrued = 0 and @ShowManagementFee = 1
                and tx.TransactionCodeID in (@ManagementFeeID) then InclusiveAmount else 0 end)   as AgentFeesManagementFee,    

--Agent Fees    Bank Charges    
Sum (Case When TransactionTypeID in (13, 23) and ExcludeFromOwnerReports = 0 and @AmountsAccrued = 0 and @ShowBankCharges = 1
                and (tx.TransactionCodeID in (@BankChargesOnCashID) or tx.TransactionCodeID in (@BankChargesOnChequesID)) 
                then InclusiveAmount else 0 end)   as AgentFeesBankCharges, 

--Agent Only (Cash) Movements   Sum of cash transactions (types = 2, 3, 12, 13, 22 and 23) and where such transactions are designated as ‘agent only, for either financial or owner statement period.
Sum (Case When TransactionTypeID in (2, 3, 12, 13, 22, 23) and ExcludeFromOwnerReports = 1 then InclusiveAmount else 0 end)   as AgentOnlyCashMovements,    

--Paid to Owners    Sum of property payments (type = 23) allocated to the Owner Payment transaction code in Global Options, excluding agent only transactions, for either financial or owner statement period.
Sum (Case When TransactionTypeID in (13, 23)    --RB 05/08/2009 Added Transaction Type 13 where expense accruals are paid to owner payment codes
    and ExcludeFromOwnerReports = 0
    and tx.TransactionCodeID = @OwnerPaymentsID
    then InclusiveAmount else 0 end)  as PaidToOwners   

From Transactions tx  (NoLock)  
Where ( (@UseFinPeriod = 1 and tx.Period =  @Period)     or (@UseFinPeriod = 0 and tx.OwnerStatPeriod = @Period) )
  and ((@IncludeRegularTransactions = 1 and @IncludeAgentOnlyTransactions = 1) 
          or (tx.ExcludeFromOwnerReports = 0 and @IncludeRegularTransactions = 1)
          or (tx.ExcludeFromOwnerReports = 1 and @IncludeAgentOnlyTransactions = 1)
          )
  and (@GenerateOwnerPayments = 0 or @PropertyDefaultCashBookID = tx.CashBookID)
  and tx.PropertyID in (Select PropertyID from #tmpProperties)

Group By tx.PropertyID, tx.CashBookID
--Order by tx.PropertyID
) a     on a.PropertyID = tmp.PropertyID and a.CashBookID = tmp.CashBookID

How would one go about using the above query through Powerbi or PowerQuery? As it can't just be added since I assume the "tmp" from above adds temporary tables and from the query side the database can't be modified. And all the @ have to be given values?

Upvotes: 0

Views: 53

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

I assume that underlying data engine is SQL Server. In this case consider to use SQL Profiler, since it will allow to capture all incoming queries and provide execution statistics, like duration, IO and CPU costs etc. Example

Update:

The query you provided contains temporary tables and variables. Therefore, creation of that temporary table is also has to be traced via profiler. When it comes variable values, they can be found by tracing event "Showplan XML Statistics Profile"

Example:

<ParameterList>
  <ColumnReference Column="@P3" ParameterCompiledValue="'2012-03-04 05:06:07.080'" ParameterRuntimeValue="'2012-03-04 05:06:07.080'" />
  <ColumnReference Column="@P2" ParameterCompiledValue="N'StrVal1'" ParameterRuntimeValue="N'StrVal1'" />
  <ColumnReference Column="@P1" ParameterCompiledValue="(17)" ParameterRuntimeValue="(17)" />
</ParameterList>

Next to this, if your data comes via On-Premises Data Gateway, consider this:

Question: How can I see what queries are being sent to the on-premises data source?

Answer: You can enable query tracing. This will include the queries being sent. Remember to change it back to the original value when done troubleshooting. Having query tracing enabled will cause the logs to be larger.

Reference

Upvotes: 1

Related Questions