Thursday, May 24, 2018

Data densification in Oracle

Missing data in time series can be handled in webi using the TimeDim function. But Oracle also has options to do this using partitioned outer joins


SQL:
with data as
( select 'Banana' AS "FRUIT", 201702 AS "MONTH", 12 AS "SALES" from dual 
  union 
  select 'Banana' AS "FRUIT", 201710 AS "MONTH", 12 AS "SALES" from dual ),
months as
( select 201701 as "MONTH" from dual union select 201702 from dual
  union select 201703 from dual union select 201704 from dual
  union select 201705 from dual union select 201706 from dual
  union select 201707 from dual union select 201708 from dual
  union select 201709 from dual union select 201710 from dual
  union select 201711 from dual union select 201712 from dual )
select FRUIT, data.MONTH, SALES
from data
partition by ( FRUIT )
right outer join months
on ( months.MONTH = data.MONTH )




Wednesday, December 27, 2017

String to integer in Oracle

Converting a string object to numeric may result in query errors (not parse errors). Use this code to check if the string contains non numeric characters:

case when translate(FIELD1,'_0123456789 ','_') is null  then to_number(FIELD1) end


Monday, November 27, 2017

Colored projects on a planning report

What if you want to create a planning report where the projects have different colors like this:
 


And not like this:


First you need to remove the right cell border:
 
 
Then create a conditional formatting rule to add a cell border:
 

 

Wednesday, October 11, 2017

Concatenating multiple rows

 

Concatenating multiple rows in webi is not an easy tasks to do. But concatenation multiple rows in Oracle is easy. Creating an object in the universe with the LISTAGG analytical funtion will do the trick.



 
 
On SQL server use FOR XML PATH in combination with STUFF.

Saturday, January 14, 2017

The BO security model: object names

In this blog series I will share insights I’ve gathered over the years on how to setup an effective security model: simple, structured, maintainable, flexible, expandable and easy to use.
In this third part of the series I will focus on the names of objects. As stated the goal is to create a security model that is simple and structured. The object names play a big part in this.

·         This blog series is aimed at experienced BO administrators, which means there will be no how-to screenshots
·         This blog series can be used as a guideline, it cannot be used as a manual
·         This blog series only covers the internal BO stuff. No windows AD or SAP roles and no IAM software

User types

Let’s assume there are four types of users on your systems:
  • Endusers
  • Analysts
  • Reporters
  • Designers
Where each type is an extension of the previous one, so there is some kind of structure in these types.

Per type there will be a user group and a CAL.

      

When I assign these user groups to folders it looks like this:


Although they are sorted alphabetically I don’t like that very much, So I make a little change to the user type user groups, to make use of the alphabetic sort:


After this little name change it’s much easier on the eye. And it’s making the model a bit more structured.

Organisation user groups

Lets add a department to the system, Sales. I create folders which represent the structure of the sales department and corresponding user groups. Theses folders and user groups are used to define access. Both have the same structure but the don't have the same names.


Doing this makes them appear neatly ordered when they are assigned to the folders:









Total user security on folder Sales \ EMAE \ NL now looks like this:




Tuesday, December 27, 2016

The BO security model: high level breakdown


In this blog series I will share insights I’ve gathered over the years on how to setup an effective security model: simple, structured, maintainable, flexible, expandable and easy to use. In the previous blog I concluded that a security model consists of CALs, user groups and folders.
In this second blog I will focus on the structure of the user groups from a high level.

·         This blog series is aimed at experienced BO administrators, which means there will be no how-to screenshots
·         This blog series can be used as a guideline, it cannot be used as a manual
·         This blog series only covers the internal BO stuff. No windows AD or SAP roles and no IAM software

I will divide the user groups into two parts:

  • The assignment part
    Underlying groups are only used to assign imported windows AD or SAP roles to.
  • The configuration part
    Underlying groups are used to config the system.

Configuration user groups

The configuration part holds all user groups used to configure access throughout the system. It is divided (for now) into these two:



  • The organisational part: who can see what
    This part consists of user groups which are modelled according to the organisational structure. Which folders a principal can see is determined here,
    it’s about access.


  • The user types part: who can do what
    This part covers the different user types. What a user
    type can do with the content is determined here,
    it’s about functionality.

Assignment user groups

The assignment part is not divided (for now). This part holds all the user groups that are used to assign business users to. You should create a group for every combination of the organisation and the user types that will be used:


The assignment groups all are members of the configuration groups. The group "HR - Attrition - Endusers" is a member of "HR - Attrition", which defines the folders and reports that can be used. And it is a member of "1 - Endusers"which defines what can be done with the reports.




The model now looks like this: