Missing Msvcr100.dll

When you first try to launch SQL developer on your PC you might run into “Msvcr100.dll is missing” error message. When you encounter above error massage, you will not be able to launch SQL developer.  However; there is a very simple step to fix this issue.

First thing you need to do is find MSvcr100.dll on your machine.  Normally it is available on “\Program Files\Java\jsdk1.7.0_04\bin” folder. Simply copy it and paste it to “\sqldeveloper\sqldeveloper\bin” folder.  This should fix the issue.

Date Functions

I always find date functions difficult to code as they can be confusing. Here are some of the date functions that can come handy while working with dates in Business Objects.

 

Last day of month =LastDayOfMonth(< Month>) 

First day of month =DayNumberOfWeek(< Month>) 

Days in a month  =DaysBetween(< Month> ,<last day of month >)+1 

Weekends =(Truncate((DayNumberOfWeek(< Month>)+<Days in a month >)/7 ,0)) 

Working Days =Floor(<Days in a month>-(<Weekends>)) 

6 working Days =If <first day of month > = 7  Then (< Working Days >+1) Else < Working Days > 

How to prompt for Quarters (Q1, Q2, Q3, and Q4) in BusinessObjects

How to prompt for Quarters (Q1, Q2, Q3, and Q4) in Business objects using designer?

Simple Method…
You can use @ Variable function to prompt for Quarter. Here is one example.

TO_CHAR (CALENDAR_DATE,'Q') =@Variable('Which Quarter?')

But Coding can be little complicated when users want to select Q1,Q2,Q3,Q4 from the list, instead.
In that case Syntax should be something as below

( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))

Now let’s assume requirement is to select year and quarter and retrieve date up to next 36 months... In this example I am going to use @variable as well as @Prompt.

TABLE.CALENDAR_DATE BETWEEN (SELECT Distinct MIN(CALENDAR_DATE) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))
AND
(SELECT Distinct MIN(LAST_DAY(ADD_MONTHS(CALENDAR_DATE,35))) from CALENDAR_DIM
WHERE TO_CHAR(CALENDAR_DATE,'YYYY') =@Variable('Which Year?')
AND ( CASE WHEN TO_CHAR(CALENDAR_DATE,'Q') ='1'
THEN 'Q1'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '2'
THEN 'Q2'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '3'
THEN 'Q3'
WHEN TO_CHAR(CALENDAR_DATE,'Q') = '4'
THEN 'Q4' END) =@Prompt('Which Quarter?','A',{'Q1','Q2','Q3','Q4'},mono,Constrained))

Fun with Xcelsius

I used my Facebook friends profile to create a simple dashboard using Xcelsius BI reporting tool. The purpose of this dashboard is just for fun and i believe friends privacy has not been compromised ! Have fun ! 

Section Access (Qlikview)

Qlkview is a Business Intelligence reporting tool and this tool is really gaining momentum in US job market. This tool has many cool features that can be leveraged as per your BI needs. On this blog i am going to share QlikView security feature that  might come handy when data cannot be compromised. 

example :

 Let's assume that we have two users; UserA, UserB. UserA is responsible for product A and UserB is responsible for product B. Our reporting requirement is to write a report for both products. However; we also want to secure data in such a way that product A is not visible to UserB and Product B is not visible to UserA.

In order to secure data, we can use a Qlikview feature called section access. Section access helps you protect data from unauthorized access as well as limit authorized users access to important data.

With the help of section access, you can restrict unauthorized access or limit authorized access  in different ways. For instance you can either use basic user access approach or basic NT security approach for access restriction. For our example purpose we will use basic user access approach.

Before writing script for section access, let's make sure “Initial Data Reduction Based on Section Access”  and "strict exclusion" is checked under Settings > document properties > Openings.  

Now let's start writing section access script.....

In order to maintain security, we will create hidden script for section access.  (File > Create hidden script. )

Section Access;  /* Start section access script */

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, PRODUCT

    ADMIN, ADMIN, ADMIN

    USER, USERA, U1, A

    USER, USERB, U2, B  

];

Section Application; /* End section access script */

PRODUCT:

LOAD * INLINE [

PRODUCT, PROFIT

A, 1000

B, 2000

];

In the above example we created row level security that means userA will be able to see profit only for his product. Similar UserB will be able to see profit only for his product.

Section access can be used not only for row level security but also for column level security. The advantage of section access as shown in our example is that it eliminated the need for creating two reports for each product. In a large organization where we have thousands of products and their related data cannot be shared among users, in that case,  section access can be very useful.

ABCD Stratification

Let's assume that we have two items. One item cost 10K and other one cost $10. Also, let's assume that both items have high coefficient of variation that means both items are critical items. In this situation it makes sense to prioritize and  investigate root cause for high volatility for expensive item rather than cheap one .

When you are managing hundreds of item then it might be difficult to identify those items, which are expensive and highly volatile and need immediate attention. On this blog i will show you how to mathematically identify items that need immediate attention. The kind of items that need immediate attention are those items that have not only high CV but also are expensive. As i have showed you earlier how to calculate CV, on this blog i will show you how to identify items that can create panic.

ABCD Stratification : ABCD Stratification method categorize items into A,B,C, and D based on weighted percentage of item cost (For our example purpose we are using items cost. Otherwise It could be anything like inventory cost, Average selling price,  or Billing Unit).

Note that Item that has high CV and fall under "A" category should be given high priority.

Here is how we calculate ABCD. Note that you should calculate ABCD for entire product lines.

1. Sort cost (Qty) in descending Order

2. Calculate Total Qty

3. Divide each cost by total Qty (Calculate Weight)

4. Do cumulative sum of  weight (You will need to do cumulative sum on the result from step 3)

5. For our example purpose, if cumulative sum is less than 80% then we will consider as A item. If cumulative sum is between .80 and .95 then we will consider as B item. If cumulative sum is between .95 and .99 then we will consider as C item and rest would be D items.

You will notice that items that fall under "A" have high Qty. If an item has high CV and falls under category A then you should give high priority to that item. 

Finding volatile items using SQL

In my last blog we i showed you how to identify volatile items with help of simple mathematical calculation. Today i am going to show you how to identify volatile items using SQL.

First we will need to create table in Oracle. This time we are not going to write sql to create and insert data into table. This time we will use oracle application builder to create new table and add data from external source (.xls).

As appose to traditional SQL method, creating a table and adding records to it is fairly simple if you use application builder. You can either copy and paste your data directly to application builder or you can import to it. Once we create table and add data from external source, we are ready to write SQL to calculate Standard deviation and average of each rooms.

 Select Distinct Room, STDEV,  AVERAGE from
(select Period_date,
Room, Qty,
Round(STDDEV(QTY) Over(Partition by Room),2) STDEV,
Round(AVG(QTY) OVER(Partition by Room),2) as AVerage
from hotel ) CV_CALC ;

ROOM                           STDEV                  AVERAGE               

------------------------------ ---------------------- ----------------------

Embassy                       27.37                  22.33                 

Honeymoon                 36.41                  48.5                  

Apartment                    3.61                   6.5                   

Classic                            0.89                   10.67                 

Presidential                   3.75                   6.42    

Since we calculate standard deviation and average, it is now time to calculate coefficient of variation. We will be using coefficient of variation to identify product volatility.

Select Room,
Round((STDEV/AVERAGE),2) as CV,
Case when  Round((STDEV/AVERAGE),2) > 1 then 'High'
When  Round((STDEV/AVERAGE),2) between .50 and 1 then 'Med'
when  Round((STDEV/AVERAGE),2) < .50 then 'Low'
end as CV_Volatility
from
(Select Distinct Room,STDEV, AVERAGE from
(select Period_date,
Room, Qty,
Round(STDDEV(QTY) Over(Partition by Room),2) STDEV,
Round(AVG(QTY) OVER(Partition by Room),2) as AVerage
from hotel ) CV_CALC ) CV ;

ROOM                           CV                     CV_VOLATILITY

------------------------------ ---------------------- -------------

Embassy                        1.23                   High         

Honeymoon                  0.75                   Med          

Apartment                     0.56                   Med          

Classic                             0.08                  Low          

Presidential                   0.58                   Med          

That is it for now. Will see you in next blogs.