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

97 Things Every Software Architect Should Know – Notes

Notes from the book "97 Things Every Software Architect Should Know"

Read more…

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"

Disable automatic start of Server Manager

Server Manager > Manage > Server Manager Properties > Do not start Server Manager automatically at logon

server_manager_disable

Disable Password Complexity for Local Accounts

Local Security Policy > Account Policies > Password Policy > Password must meet complexity requirements -> Disabled

passwordcomplexity

How to Allow Remote TCP/IP Connections for SQL Server

  1. SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS > TCP/IP
    TCP/IP Enabled
    IPAll > TCP Dynamic Ports is blank
    IPAll > TCP Port = 1433
  2. SQL Server Management Studio > Server Properties > Security
    Server authentication = SQL Server and Windows Authentication mode
  3. Services > SQL Server Browser
    Enable and start
  4. Windows Firewall with Advanced Security > Inbound Rules > New Rule… > Port
    Allow the connection for the port 1433

How to Delete Files Older Than 30 Days from Windows Command Line

forfiles -p F:\Logs -s -m *.log /D -30 /C "cmd /c del @path"

Tool for updating non-Administrator group

BO 4.x has a possibility to customize the user interface of Web Intelligence for specific groups of users. For instance, you may want to hide "Design Mode" interface elements for report viewers who are not supposed to edit reports.

others-group-03

This can be done in Central Configuration Management in Groups:

others-group-01

others-group

The problem is that the change will be applied for all users who belong to the group. If a report designer belongs to the group, it will not be able to see the disabled interface elements even if the user belongs Administrators group.

A solution is to create a group of non administrators group (say Others) and apply user interface customization for this group.

However it is a tedious work to maintain two groups Administrators and Others. I have created a tool which allows to maintain the Others group. If a user is not Administrators group (e.g. new user) or if it is removed from Administrators group, the tool will add the user to Others group. If a user is added to Administrators group, it will be deleted from Others groups.

Here is the link to archive: othersgroup-1.0.zip

The archive includes executable and source code:
others-group-02

The batch file has the following parameters you may need to change.

The location of BusienssObjects:

set BO=C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0

BO Administrator credentials:

set BOADMIN=Administrator
set BOPASS=
set BOSERV=localhost
set BOAUTH=secEnterprise

The groups names:

set EVERYONE=Everyone
set ADMIN=Administrators
set OTHERS=Others

The connection driver "xxx" is unknown. (SLS 16000)

com.sap.sl.sdk.authoring.internal.AuthoringException: The connection driver "SAP NetWeaver BW 7.x/SAP BICS Client" is unknown. (SLS 16000)

The documentation says to use "SAP NetWeaver BW 7.x", but you have to use "SAP BW 7.x" instead. I.e.

OlapConnection connection = connectionFactory.createOlapConnection(
  connectionName, "SAP BW 7.x", "SAP BICS Client");

Other OLAP drivers just in case:

Microsoft Analysis Services 2005 ODBO
Microsoft Analysis Services 2008 ODBO
Microsoft Analysis Services 2012 ODBO
Microsoft Analysis Services 2005 XMLA
Microsoft Analysis Services 2008 XMLA
Microsoft Analysis Services 2012 XMLA
Financial Consolidation 10 for Microsoft Platform XMLA
Planning and Consolidation 7.5 for Microsoft Platform XMLA
Planning and Consolidation 10 for Microsoft Platform XMLA
Profitability and Cost Management 10 XMLA
Strategy Management 10 XMLA
Oracle OLAP XMLA
Hyperion Essbase 11.x XMLA
Hyperion Essbase 11.x Essbase API for Analysis
Teradata XMLA
SAP BW 7.x SAP BICS Client
SAP HANA SAP Hana Client