SQL tasks

Leave a reply

My strong believe is that the most important part of the job interview for a BI position is a practical SQL task. From my experience, it is a good predictor for future performance. I prefer giving a relatively complex task that can be solved within 15 minutes. To minimize the stress factor, I usually give the task in the end of the interview and ask to send the solution within 1 hour.

The database table REVENUE contains revenue amount obtained at certain month. It has the following fields: YEAR, MONTH, AMOUNT. For the current date, calculate YTD value and estimated full year value under two assumptions: 1) even distribution of the revenue, 2) seasonal distribution of the revenue.

The database table PROJECT contains the information about projects, project start and end date (inclusive) and the total budget for the project duration. It has the following fields: PROJECTID, STARTDATE, ENDDATE, BUDGET. Distribute the budget to months evenly by day. The output should contain fields PROJECTID, YEAR, MONTH, BUDGET. You can use an auxiliary table CALENDAR with the fields THEDATE, YEAR, MONTH. You can assume it covers all dates used in the table PROJECT.

(I am going to add more sample tasks here)

Copy binary as text via clipboard to a remote desktop

Sometimes you are not allowed to transfer files between machines but you can transfer text via clipboard. A little hack how to transfer a file using a standard Windows utility certutil. 

Create a text file encoding the file

certutil -encode file.zip file.txt

Open and copy text

Create a new file on the remote desktop and paste the text

Decode the file into zip

certutil -decode file.txt file.zip

 

Backup scheduled reports

recurring.txt

action = exportXML
exportBiarLocation = recurring.biar
userName = Administrator
password = 
authentication = secEnterprise
CMS = localhost
exportDependencies = false
includeSecurity = false
exportQueriesTotal = 1
exportQuery1 = select * from CI_INFOOBJECTS where SI_RECURRING=1

recurring.bat

SET BO=C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0
"%BO%\win32_x86\jre8\bin\java.exe" -jar "%BO%\java\lib\biarengine.jar" recurring.txt

“Relationship would not be a tree after update so bailing”

Sometimes a connection cannot be promoted by Promotion Management with the error:

Resolution Status=Copied, Dependency Status=All the required dependencies are included., Commit Status=Commit attempted and failed., Promotion Status=Failure : Relationship would not be a tree after update so bailing.

It may help to promote connection using BiarEngine. This can display a better message explicitly saying object’s ID causing the dependency issue, for instance:

2

Creating ODBC system DNS with command line 

%WINDIR%\System32\odbcconf.exe CONFIGSYSDSN "SQL Server" "DSN=macoprod|Description=macoprod|SERVER=10.2.151.18|Trusted_Connection=No|Database=macoprod"
%WINDIR%\SysWOW64\odbcconf.exe CONFIGSYSDSN "SQL Server" "DSN=macoprod|Description=macoprod|SERVER=10.2.151.18|Trusted_Connection=No|Database=macoprod"

Weird issue in Webi

A query is based on eFashion universe and returns two objects: measure [Promotion Cost USD] and dimension [Duration].

The variable [Cost] is defined as

=If [Duration]>0 Then [Promotion Cost USD]/[Duration]

The following formula in report’s cell returns #MULTIVALUE

=Sum([Cost])

If I just replace the variable with the expression, it will return correct value

=Sum(If [Duration]>0 Then [Promotion Cost USD]/[Duration])

The following formula also returns correct value

=Sum([Cost] ForEach([Duration]))

context issue

Example of an Indexed View in SQL Server 

CREATE VIEW C_FINANCEENTRYPV WITH SCHEMABINDING AS
  SELECT
     ACCOUNTNUMBER
    ,ENTRYDATE
    ,Sum(IsNull(FINANCEENTRY.DEBITBASE,0))  DEBITBASE
    ,Sum(IsNull(FINANCEENTRY.CREDITBASE,0)) CREDITBASE
    ,Count_Big(*) CNT
  FROM
    dbo.FINANCEENTRY FINANCEENTRY
GROUP BY 
    ACCOUNTNUMBER,
    ENTRYDATE
GO
CREATE UNIQUE CLUSTERED INDEX C_FINANCEENTRYPV_01 ON C_FINANCEENTRYPV (
  ACCOUNTNUMBER,
  ENTRYDATE
)
GO
SELECT * FROM C_FINANCEENTRYPV WITH (NOEXPAND)

How to Use Reposcan to Repair CMS and FRS

This post describes the steps to repair CMS database and Filestore using SAP BusinessObjects reposcan.exe.

Note that this may delete some items from CMS or Filestore if the dependencies are not found. Make sure that you have made necessary backups before applying reposcan.

You can run the tool without repairing option (or with -repair off). This will scan the environment and make the report of inconsistencies which will be saved to the output folder.

1. Create folder C:\Reposcan
2. Create file reposcan.ini in the folder

-dbdriver sqlanywheredatabasesubsystem 
-connect "UID=dba;PWD=1-Password;DSN=BI4_CMS_DSN"
-dbkey "[[qoNXDD3yCYuHLyTpqyj9Cw]]"
-inputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Input"
-outputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Output"
-outputdir "C:\Reposcan"
-repair on

For Oracle, the first two line should be:

-dbdriver oracledatabasesubsystem 
-connect "UID=<user>;PWD=<password>;DSN=<dsn>"

For SQL Server:

-dbdriver sqlserverdatabasesubsystem 
-connect "UID=<user>;PWD=<password>;DSN=<dsn>"

The value for dbkey can be found in Central Configuration Manager under CMS Cluster Key Configuration:
sia
Here you can also see DNS under CMS System Database Configuration.

3. Create file reposcan.bat

"C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\reposcan.exe" -optionsfile "C:\Reposcan\reposcan.ini"
pause

4. Run the file reposcan.bat
reposcan

You can get the list of all commands running the reposcan without options:

"C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\reposcan.exe"