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

Por Carina Mendes
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:

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:

SELECT CAST("Dim Tempo"."Data" AS DATE) FROM "PC1"

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:

SELECT CAST("Dim Tempo"."Data" AS DATE) FROM "PC1" ORDER BY 1 DESC

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:

SELECT CAST("Dim Tempo"."Data" AS DATE) FROM "PC1"   WHERE YEAR(CAST("Dim Tempo"."Data" AS DATE)) = YEAR(CURRENT_DATE) ORDER BY 1 DESC

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.

SELECT (timestampadd(SQL_TSI_day,+14,(CAST("Dim Tempo"."Data" AS DATE)))) FROM "PC1" ORDER BY 1 DESC

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:

SELECT (TIMESTAMPADD(SQL_TSI_MONTH, -6, (CAST("Dim Tempo"."Data" AS DATE)))) FROM "PC1" ORDER BY 1 DESC

Realizar cálculos de ANOS em filtros datas – Neste exemplo, calculamos a exibição uma ano a menos da data existente no filtro.

SELECT (TIMESTAMPADD(SQL_TSI_YEAR, -1, (CAST("Dim Tempo"."Data" AS DATE)))) FROM "PC1" ORDER BY 1 DESC

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:

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.