Teradata Query Banding In SAP Business Objects Information Design Tool
Usually customers require tracking and auditing of users and queries they are running on DWH/Teradata. There is no one way of tracking queries run by BI application users. An application user is not necessarily a Teradata user, therefore, query band is a useful feature for tracking logons and queries generated from BO and BO users. Queries are logged in DBQL along with necessary information. For instance:
- Application (Business Objects) username
- Universe name
- Document name
- etc (further details can be found in SAP BO documentation)
This is achieved using query banding feature provided in Teradata and is exploited in SAP Business Objects using 'BEGIN_SQL' parameter for SQL Generation. An example of BEIGN_SQL string is as follows:
SET QUERY_BAND='USER='@Variable('BOUSER');Document='@Variable('DPNAME')';' for transaction;
As per SAP documentation, it should work fine and it does work fine if you are using it in Universe Design Tool. But when used in Information Design Tool, it returns an error due to problem with SQL generation. Issue is due to @Variable function which returns a value enclosed in single quotes. For some reason, this is taken care of in Universe Design Tool but since Information Design Tool is new, it causes SQL error on Teradata.
Until SAP development team fixes this issue, a workaround can be used for query banding in Information Design Tool and that is to call a Teradata Stored Procedure in BEGIN_SQL and pass necessary parameters to it as inputs. Query band can then be set within the procedure.
For further details or queries, leave comments and we will reply.
Teradata Calling A Procedure Within A Procedure
Yes! Teradata allows you to call a stored procedure within a stored procedure. One way of doing it is as follows:
CALL DBC.SYSEXECSQL('CALL PROC_A(''VALUE'',OUTPUT)');
It might be required for many reasons, for example writing a sub routine to process data or to recursively call same procedure until some criteria is met. For me, it was used in a case where WAP source system was sending data usage for specific events only and for rest of the events data usage was only included in session summary. Therefore, to process session summary information, a separate routine was written which was called to process session summary.
Business Intelligence on Mobile
Businesses around the world have been facing a challenge of making right decision throughout the course of modern history. With time, need to make accurate decisions has become more dependent on availability of processed data. Digital age has gifted us with volumes of raw data and nightmare of getting actionable information out of it. Technological evolution of Teradata has enabled the world to process these volume of data and store it in a way that can be used to derive useful information for timely decisions. With this technology corporate are using information as an asset that is leading to higher profits. Decision makers have now started to expect availability of actionable information on the fly, hence, analytics are moving from desktop to mobile devices.
Need for Mobile BI:
Most of the companies so far have not focused on extending BI access to tactical work force. Most of the DWH and BI models are deployed keeping in mind analysts and middle level management. Nevertheless, need is being realized and in many industries analytical information is provided to front-line workers. For this work-force, access on mobile maybe critical. Let us take some real-life examples to understand this:
Retail: Large super stores have many point of sales. These POSes have cash drawers where cash is pilling up. Collecting this cash on regular intervals is usually important to avoid frauds etc. In this scenario, collection agents are on the move continuously and cannot sit on the computer to check desktop reports. A mobile based BI solution is required in this situation to optimize the collection of cash process as well as allow agents to monitor and act wisely.
Telecom: Where brains of the business and operation do not interact with customer directly that often, are required to set high level organisational objectives, which then are executed by front-line workers including call center, sales point/franchises and service centers. To effectively achieve set targets, it is important to delegate operational level decision making to front-line workers. For example, in call centers agents are always focused to sell additional services to existing customers. This effort can be made more effective by providing processed data (generated through BI) regarding a customer when he calls. Call center agent will be able to understand the customer interest and can offer services which customer will be more interested in.
Similarly, sales manager can monitor city-wise and area-wise sales statistics. Manager can take appropriate actions to meet daily sales target by monitoring sales data and can avoid target-miss. It becomes a pro-active approach to meet targets. Conventionally, reason of a daily target-miss would have been analysed the other day.
Technological Advancements:
Many Business Intelligence platforms are providing applications that can deliver graphs and dashboards on mobile devices. These tools use power of teradata to execute complex queries and store pre-aggregated data which then is used to generate visualizations (graphs, dashboards). Specially after Teradata introduced AEI (Active Enterprise Intelligence), theoretical pervasive BI architecture has now become a reality.
SAP Business Objects:
From the early days of Business Objects, they had something called Business Objects Mobile. It has two parts. First one is a component that needs to be installed on the server and the second one is a Java App which is installed on mobile devices. Same application has continued in later versions of SAP BO.
Installation: Once server component is deployed, mobile app can be downloaded over the air (OTA) using WiFi or Mobile Internet by accessing a URL. OTA installer is deployed with war file for Mobile.
Supported Devices: Following devices are supported by BO Mobile App.
- Blackberry OS v4.1, v4.2, v4.3, v4.5 and v4.6
- Symbian Series-60 3rd Edition
- Windows Mobile v5.0 with .Net Compact Framework v2.0 or above
- Windows Mobile v6.0 and v6.1
When installing, client app automatically detects type of mobile device.
SAP Business Objects Mobile and Business Objects Explorer can be used to deliver dashboards and reports on almost all mobile devices including iPhone and Android.
MicroStrategy:
MSTR desktop provides very flashy dashboards but they have introduced mobile component which can be used to deliver dashboards on mobiles phone.
Considerations:
- Number of decision makers using data from Teradata
- Number of managers
- Number of business areas covered
- Monitoring requirements
- Number of operational reports and tactical decision makers
- Competition in the market
- Realization of Business Intelligence as a competitive advantage
- Requirement for real-time data insights
- Focus on process optimization (operational or business)
Extracting Domain From A URL In Teradata
When working with internet cloud traffic, domain name is a significant information for analysis. For instance, some telecom operators may offer free GPRS usage for specific websites like facebook, yahoo etc. For operators offering ADSL and other broadband services, domain name becomes more important to variety of exploratory analysis that can be used for designing new offers or bundles, optimizing cache, adjusting capacity by analyzing streaming media, download trends, popular websites etc
Following techniques can be used to extract domain name from a URL
- Creating a UDF that supports regular expressions and then using regular expressions to extract domain name
- Using simple string manipulation functions that are already available in Teradata
Later technique is discussed here for general use. It can be applied on normalized URLs and/or can be modified as per the requirements. Following SQL can be used to test domain name extraction from a URL
SELECT
SUBSTR
(
SUBSTR(?url ,
(-COALESCE(NULLIFZERO(POSITION ('/' IN ?url)),-1)*-1)+2 ) ,
0,
POSITION('/' IN SUBSTR(?url ,
(-COALESCE(NULLIFZERO(POSITION ('/' IN ?url)),-1)*-1)+2 )||'/' )
);
Use URLs in following style to check above script:
'http://www.emergingbytes.com'
Script is using simple function to locate position of first occurring '/' (forward slash) in a URL and then again search for ending '/'. If ending slash is not present in URL, it appends a '/' and extracts anything between these two '/' (forward slashes). This script can be used on most of the domains but to handle exceptions, it can be improved upon.
Teradata Date Addition/Subtraction
Today when I started designing BI layer for a session authentication system (RADIUS), I realized that there are two types of CDRs
- Start of a session CDR
- Stop of a session CDR
Interestingly, STOP CDRs have no Start date information. Therefore, I had to subtract session duration for the stop date to get start date of a session.
Problem: To subtract duration (in seconds) from a date (session stop date) to get actual start date of a session
Solution:
Although, it appears to be a simple riddle but in Teradata, this can be a little bit tricky, specially when you are not comfortable working with dates and intervals. Following code snippet explains that how it is done.
CAST(START_DATE_TIME - CAST((SESSION_DURATION/60/60/24/28/12) AS INTERVAL YEAR) - CAST((SESSION_DURATION/60/60/24/28 MOD 12) AS INTERVAL MONTH) - CAST((SESSION_DURATION/60/60/24 MOD 28) AS INTERVAL DAY) - CAST((SESSION_DURATION/60/60 MOD 24) AS INTERVAL HOUR) - CAST((SESSION_DURATION/60 MOD 60) AS INTERVAL MINUTE) - CAST((SESSION_DURATION MOD 60) AS INTERVAL SECOND) AS DATE FORMAT 'YYYY-MM-DD') AS START_DATE
Point 1:
When dealing with huge number of seconds, their is a limitation of casting it to seconds. Running the following query will result in 'Interval field overflow':
SELECT CAST(30000 AS INTERVAL SECOND(4) )
Therefore, to add/subtract huge number of seconds, conversion to hour, minute and seconds is required.
Point 2:
To successfully cast above, we may need to convert it into hours
SELECT CAST(30000/60/60 AS INTERVAL HOUR)
It will result in 8 hours.
Point 3:
Converting 8 hours back to seconds will not give 30000. Teradata INTERVAL does not maintain precision information. Hence, to keep track of exact seconds, MOD should be used and added up to get precise number of seconds. Below example explains how it can be done.
SELECT CAST(30000/60/60 AS INTERVAL HOUR) AS hrs ,CAST(((30000/60) MOD 60) AS INTERVAL MINUTE) AS mins ,CAST((30000 MOD 60) AS INTERVAL SECOND) AS secs , hrs+mins+secs