BI Cloud - Utilizando SQL lógico para otimizar o resultado de filtros

Por Carina Mendes Oracle ACE
Postado em Janeiro 2017

Pequenos detalhes durante o desenvolvimento, podem tornar o resultado final do BI muito mais eficientes e assertivos. Neste artigo demonstrarei como é possível utilizar uma querie lógica de SQL dentro dos prompts do BI Cloud Service, com o objetivo de melhorar a usabilidade dos filtros e dessa forma aumentar a praticidade e assim, melhorando a experiência do usuário final.

Ativar o SQL nos Prompts:
Criei um prompt para utilizarmos de exemplo, onde irei demostrar em um único prompt algumas hipóteses de SQL que podem ser aplicadas aos filtros:

Vamos criar um prompt (filtro):

É necessário cria um filtro de coluna. Selecionei uma coluna de “Data” da dimensão “Tempo” e observe a opção que deve ser escolhida para que a querie SQL possa ser customizada:

O resultado final é transparente:



Compreendendo a estrutura do SQL lógico:
A querie retornada pelo filtro no prompt, é um pouco diferente com o SQL e utilizamos no banco de dados. Chamamos de querie lógica pois sua estrutura representa o modelo lógico de negócios existente no BI Cloud. Observe:

Testar resultados do SQL lógico:
Na interface do Analytics, existe o menu “Console” e na opção “Executar Instrução SQL” você pode realizar consultas baseadas nas queries que utilizará nos campos de filtros do prompt:

Observe que utilizei a mesma querie lógica que habilitei no filtro do prompt para testar os resultados. A partir dela podemos desenvolver novas customizações e implementar de volta nos prompts:

Trabalhando com SQL lógico para campos de data:
Agora que temos a autonomia de realizar o SQL lógico nos prompts e também sabemos como testar nosso SQL, podemos customizar as queries para atender as solicitações dos usuários e melhorar o resultado final dos filtros.

Preparei alguns exemplos que mais utilizo no desenvolvimento de filtros de painéis:

  1. Alterar o Data Type das colunas de filtros–Antes os dados de data estavam sendo exibidos no formato de “Date Time” e iremos convertê-lo para “Date”. Para isso utilizei a seguinte querie:
  2. SELECT CAST("Dim Tempo"."Data" AS DATE) FROM "PC1"
    

  3. Organizar em ordem decrescente as colunas de filtros – Antes a lista de data era desordenada, a próxima querie fará com que as datas mais recentes da base apareçam primeiro como opção de filtro:
  4. SELECT CAST("Dim Tempo"."Data" AS DATE) FROM "PC1" ORDER BY 1 DESC
    

  5. Apresentar somente as datas pertencente ao ano atual – Esta opção é interessante pois ajuda a aplicação carregar somente os dados do ano em que se irá utilizar, evitando que o BI Cloud leia todos os dados da dimensão toda vez que executar um prompt:
  6. SELECT CAST("Dim Tempo"."Data" AS DATE) FROM "PC1" 
    WHERE YEAR(CAST("Dim Tempo"."Data" AS DATE)) = YEAR(CURRENT_DATE) ORDER BY 1 DESC
    

  7. Realizar cálculos de DIAS em filtros datas – Com esta querie é possível projetar por exemplo a exibição de 15 dias a mais ou a menos da data existente no filtro.
  8. SELECT (timestampadd(SQL_TSI_day,+14,(CAST("Dim Tempo"."Data" AS DATE)))) 
    FROM "PC1" ORDER BY 1 DESC
    

  9. Realizar cálculos de MESES em filtros datas – Neste exemplo, calculamos a exibição de seis meses a menos da data existente no filtro. Estes cálculos são muito utilizados para filtrar relatórios através de uma data final e uma data inicial:
  10. SELECT (TIMESTAMPADD(SQL_TSI_MONTH, -6, (CAST("Dim Tempo"."Data" AS DATE))))
    FROM "PC1" ORDER BY 1 DESC
    

  11. Realizar cálculos de ANOS em filtros datas – Neste exemplo, calculamos a exibição uma ano a menos da data existente no filtro.
  12. SELECT (TIMESTAMPADD(SQL_TSI_YEAR, -1, (CAST("Dim Tempo"."Data" AS DATE))))
    FROM "PC1" ORDER BY 1 DESC
    

  13. Concatenar campos de dois ou mais filtros – Neste exemplo, calculamos a exibição de seis meses a menos da data existente no filtro. Estes cálculos são muito utilizados para filtrar relatórios através de uma data final e uma data inicial:
  14. SELECT (CAST("Dim Produto"."Cód Categoria" AS CHAR)||' - '||"Dim Produto"."Categoria")
    FROM "PC1" ORDER BY 
    

Funções mais utilizadas no SQL dos prompts:
A seguir estou compartilhando uma lista de funções mais utilizadas nas sintaxes SQL dos filtros, observe que todas elas não são funções de agregação, pois não é recomendado que se execute cálculos de me métricas nos filtros por questões de boas práticas de modelagem. Veja alista:

Calendários: Exemplos: Descrição

Current_Date

Current_Date

Retorna a data atual.

Current_Time

Current_Time(3)

Retorna a hora atual para o número especificado de dígitos de precisão, por exemplo: HH: MM: SS.SSS

Current_TimeStamp

Current_TimeStamp(3)

Retorna o atual data / timestamp para o número especificado de dígitos de precisão.

DayName

DayName(Order_Date)

Retorna o nome do dia da semana para uma expressão de data especificada.

DayOfMonth

DayOfMonth(Order_Date)

Retorna o número correspondente ao dia do mês para uma expressão de data especificada.

DayOfWeek

DayOfWeek(Order_Date)

Retorna um número entre 1 e 7 correspondente ao dia da semana para uma expressão de data especificada. Por exemplo, 1 corresponde sempre a domingo, 2 corresponde à segunda-feira, e assim por diante até a sábado que retorna 7.

DayOfYear

DayOfYear(Order_Date)

Retorna o número (entre 1 e 366) correspondente ao dia do ano para uma expressão de data especificada.

Day_Of_Quarter

Day_Of_Quarter(Order_Date)

Retorna um número (entre 1 e 92) correspondente ao dia do trimestre para a expressão data especificada.

Hour

Hour(Order_Time)

Retorna um número (entre 0 e 23) que corresponde à hora para uma expressão de tempo especificado. Por exemplo, 0 corresponde a 0:00 e 23 corresponde a 23:00

Minute

Minute(Order_Time)

Retorna um número (entre 0 e 59), correspondente à expressão minutos para um tempo especificado.

Month

Month(Order_Time)

Retorna o número (entre 1 e 12) que corresponde ao mês para uma expressão de data especificada.

MonthName

MonthName(Order_Time)

Retorna o nome do mês para uma expressão de data especificada.

Month_Of_Quarter

Month_Of_Quarter(Order_Date)

Retorna o número (entre 1 e 3) correspondente ao mês no trimestre para uma expressão de data especificada.

Now

Now()

Retorna a data atual. A Nowfunção é equivalente à Current_Timestampfunção.

Quater_Of_Year

Quarter_Of_Year(Order_Date)

Retorna o número (entre 1 e 4) correspondente ao trimestre do ano para uma expressão de data especificada.

Second

Second(Order_Time)

Retorna o número (entre 0 e 59) que corresponde aos segundos para uma expressão de tempo especificado.

TimeStampAdd

TimeStampAdd(SQL_TSI_MONTH, 12,Time."Order Date")

Adiciona um número especificado de intervalos a um timestamp e retorna um único timestamp.

TimeStampDiff

TimeStampDiff(SQL_TSI_MONTH, Time."Order Date",CURRENT_DATE)

Retorna o número total de intervalos específicos entre duas marcas de tempo.

Week_Of_Quarter

Week_Of_Quarter(Order_Date)

Retorna um número (entre 1 e 13) correspondente à semana do trimestre para a expressão data especificada.

Week_Of_Year

Week_Of_Year(Order_Date)

Retorna um número (entre 1 e 53) correspondente à semana do ano para a expressão data especificada.

Year

Year(Order_Date)

Retorna o ano para a expressão data especificada.

Conversão:

Exemplos:

Descrição

Cast

Cast(hiredate AS CHAR(40)) FROM employee

Altera o tipo de dados de uma expressão ou um valor nulo literal para outro tipo de dados. Por exemplo, você pode lançar uma customer_name (um tipo de dados Char ou Varchar) ou data de nascimento (a data e hora literal).

IfNull

IfNull(Sales, 0)

Testa se uma expressão avaliada como um valor nulo, e se isso acontecer, atribui o valor especificado para a expressão.

To_DateTime

SELECT To_DateTime ('2009-03-0301:01:00', 'yyyy-mm-dd hh:mi:ss') FROM sales

Converte strings literais de formato datetime para um tipo de dados DateTime.

String Functions

Exemplos:

Descrição

Bit_Length

Bit_Length('abcdef')

Retorna o tamanho, em bits, uma cadeia especificada. Cada caractere Unicode é de 2 bytes de comprimento (o equivalente a 16 bits).

Char

Char(35)

Converte um valor numérico entre 0 e 255 para o valor do caractere correspondente ao código ASCII.

Char_Length

Char_Length(Customer_Name)

Retorna o tamanho, em número de caracteres, de uma cadeia especificada. Brancos iniciais e finais não são contados no comprimento da corda.

Concat

SELECT DISTINCT Concat ('abc', 'def') FROM employee

Concatena duas cadeias de caracteres.

Insert

SELECT Insert('123456', 2, 3, 'abcd') FROM table

Insere uma cadeia de caracteres especificada em um local especificado em outra cadeia de caracteres.

Left

SELECT Left('123456', 3) FROM table

Retorna um número especificado de caracteres a partir da esquerda de uma string.

Length

Length(Customer_Name)

Retorna o tamanho, em número de caracteres, de uma cadeia especificada. O comprimento é retornado excluindo quaisquer caracteres em branco à direita.

Locate

Locate('d' 'abcdef')

Retorna a posição numérica de uma cadeia de caracteres em outra seqüência de caracteres. Se a cadeia de caracteres não for encontrada na cadeia que está sendo pesquisado, a função retorna um valor de 0.

LocateN

Locate('d' 'abcdef', 3)

Como Localize, retorna a posição numérica de uma cadeia de caracteres em outra seqüência de caracteres. LocateN inclui um argumento inteiro que permite especificar uma posição de partida para iniciar a busca.

Lower

Lower(Customer_Name)

Converte uma cadeia de caracteres para minúsculas.

Octet_Length

Octet_Length('abcdef')

Retorna o número de bytes de uma cadeia especificada.

Position

Position('d', 'abcdef')

Retorna a posição numérica strExpr1em uma expressão de caracteres. Se strExpr1não for encontrado, a função retorna 0.

Repeat

Repeat('abc', 4)

Repete-se uma expressão especificada nvezes.

Replace

Replace('abcd1234', '123', 'zz')

Substitui um ou mais caracteres de uma expressão de caracteres especificada com um ou mais outros personagens.

Right

SELECT Right('123456', 3) FROM table

Retorna um número especificado de caracteres da direita de uma string.

Space

Space(2)

Insere espaços em branco.

Substring

Substring('abcdef' FROM 2)

Cria uma nova cadeia a partir de um número fixo de caracteres no string original.

SubstringN

Substring('abcdef' FROM 2 FOR 3)

Como Substring, cria uma nova cadeia a partir de um número fixo de caracteres no string original.

TrimBoth

Trim(BOTH '_' FROM '_abcdef_')

Tiras especificado esquerda e à direita caracteres de uma cadeia de caracteres.

TrimLeading

Trim(LEADING '_' FROM '_abcdef')

Tiras especificado personagens principais de uma cadeia de caracteres.

TrimTrailing

Trim(TRAILING '_' FROM 'abcdef_')

Tiras especificado arrastando caracteres de uma cadeia de caracteres.

Upper

Upper(Customer_Name)

Converte uma cadeia de caracteres em maiúsculas.

* Funções de Calendário manipula somente dados no dormato "Date" ou "Date Time".



Carina Mendes - Business Intelligence Consultant.
Formação Acadêmica em Gestão Financeira e Estatística, Atuação técnica no desenvolvimento de projetos em Oracle BIEE 10g/11g e Oracle Endeca Information Discovery 3.1. Linguagens e ferramentas: Oracle SQL, ODI, AdminTool, Shell, Html e CSS.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.