SQL Basics in 30 Minutes - Part 3

Topic: SQL Basics in 30 Minutes - Part 3: Views and Stored Procedures. 




Views, just like the word word itself, is a part of a whole. Look at the image on the left. You can see a partial 'view' of the earth. You don't see the rest of the earth, though it exists. Just like that, a View can have columns that are not seen, i.e. hidden from users. For example, you may have a table called "Credit_Card" and that table may contain the following columns: First_Name, Last_Name, Credit_Card_Number, Last_4_Digit, Address and so on. For security purposes, you (or your company) may only allow developers to create a View called "Credit_Card_Public" that may only have the following columns: Last_Name, Address and Last_4_Digit (of the card). 

Stored Procedures, again, just like the name suggests, are procedures or SQL commands that are stored in a single object. Look at the rabbit pic at the left. Think of the rabbit as a Stored Proc. As soon as you signal the rabbit, it snaps the twig and boom; all those bundles of cash start a domino effect leading up to the last bundle. Similarly, when a Stored Procedure is executed, series of SQL commands get run, leading up to the the 'end' of the stored proc. 

SQL Basics in 30 Minutes - Part 2

Topic: SQL Basics in 30 Minutes - Part 2: We'll dive into CREATE command. We'll 'Create' Tables, Temp Tables, Table Variables and Variables. We are going to 'Insert' data into this table (INSERT INTO) and we'll occasionally 'DROP' the tables.

Advertisement:

Buy SQL Books from Amazon.com

SQL Basics in 30 Minutes - Part 1

Topic: SQL Basics in ~30 Minutes - Part 1: Visual demonstration of Select, From and Where clauses in SQL Server 2005. I've created a little table in Excel so beginners can visual these SQL commands. 

Business Intelligence and SQL go hand to hand. You cannot be an expert BI Consultant without knowing SQL proficiently. Sure, in a large enterprise scenario, BO Admins and Architects for example may never need to type a single SQL query, but you are not likely going to be a confident consultant. In the world of BI, SQL is your best friend!

This part 1 is for absolute beginners. I didn't wan't to jump into creating tables and stuff, and thought maybe it'd be helpful for the beginners to kind have most of the important commands in one short video series. I am going to cover few more important SQL commands in the next 2 videos including Create (Tables), Insert Into, Views and more.



Advertisement:

Buy SQL Books from Amazon.com

Stored Procedure in WebI Reports Part 3

Image walkthrough: Stored Procedure with Parameters / Prompts in WebI Reports

[1] We are starting with this WebI report which was created with a simple stored procedure, in Part 2: That stored procedure just returned 2 columns. Now, we are going to work with a stored procedure with 2 input parameters, aka 'prompts' (in WebI). Very useful.

[2] Our 2nd stored procedure returns 2 columns, based on users' response. The parameters are 'StateID' and 'StateAbbrev' - with datatype of number and string / varchar, respectively. And since we know where these 2 fields are from, a quick way to test this procedure would be to find and enter 2 valid parameters when executing the procedure, in this case, we'll pick '3' for state 'AR'.

Let's run the procedure:

Usage format:

EXEC Stored_Procedure StateID, StateAbbrev

EXEC Stored_Procedure 3, AR

> see results of this procedure at the bottom of the following pic

[3] So, back to WebI > SQL (View SQL) > Use Custom SQL:

We'll apply our store procedure: EXEC Stored_Procedure 3, AR

make sure, to use "SET NOCOUNT ON" and comment out (/* blah blah */) the codes generated by WebI - do NOT delete the codes.

Validation successful.

[4] Run it and the report runs. So, we got it to work. Don't worry about the column headers, you can rename them to suit your needs.

Now, let's create prompts.

[5] We'll use both types to be Alphanumeric

This your basic @Prompt syntax:

@Prompt(‘message’,‘type’,[lov],[MONO|MULTI],[FREE|CONSTRAINED])

So we have

-- EXEC Stored_Procedure 3, AR (Original format)

EXEC Stored_Procedure

@Prompt('Enter STATE ID' , 'A' , MONO , FREE , PERSISTENT) ,

@Prompt('Enter STATE ABBREVIATION' , 'A', MONO , FREE , PERSISTENT)

... And validation successful. Great.

[6] As we save and run the above modified WebI query, we are prompted - perfect! So, we'll once again enter '3' and 'AR' to match with the results we got when we ran this procedure on SQL M our result in SQL Server Management Studio.

[7] So, now let's run this stored procedure with prompts on WebI - bingo, Success!

[8] NOTE:

If you go back to the code, you'll see that the @Prompts are gone in custom SQL query. Don't worry, simply "close" the code and the report will still prompt you. BUT, if you hit "save", then it will only retain the value you see - in this case '3' and 'AR'.

Stored Procedure in WebI Reports Part 2

WALKTHROUGH: Using Stored Procedure in WebI reports on Business Objects EN XI R2 (SP3)

This is a follow up of Part 1 where we went through the prerequisites of enabling stored procedure queries on WebI.

We have a simple Stored Procedure called "uspGetStates" - which is intuitive and does exactly what the title says - it gets names of states.

Stored Procedure: dbo.uspGetStates

Running the procedure: EXEC dbo.uspGetStates will give you results like this:

(partial result)

AA 53

AE 52

AK 1

..

..

so on (see pic)

This store procedure returns 2 columns - one with 'string' datatype and the other with 'number'.

If you can recall that your WebI query panel must have the same number of columns - i.e. Dimensions and same number of corresponding data types as your stored procedure does. Else your procedure won't validate, and thus won't work on WebI.

[1] So, we run the procedure and got some result

[2] We have to create a WebI Report with the same structure as our procedure returns

[3] Use the codes:

a) "SET NOCOUNT ON" on top of the code generated by WebI

b) Comment out - but, DON'T delete your WebI code

c) At the bottom of the comment, use your store procedure - in this case we'll use: "EXEC dbo.uspGetStates"

[4] Uh oh ... Error ! Type mismatch. Let's reslove

[5] We used:

"Company" which has "string" datatype - good

"Saleareanum" which also has "string" datatype - no good

So, we'll use:

"Saleareaid" which has "number" datatype

[6] Having corrected the Dimensions' datatype and matching them with those of the stored procedure, we are ready to validate.

[7] Validation successfull !

[8] We run our WebI report and see that our report returns the same data as it return running the stored procedure on SQL Server Management Studio.. (null row is displayed on WebI as the last row).

Stay tuned for Stored Procedure with Prompts in Part 3 ...

Stored Procedure in WebI Reports on BOE Part 1

How to use Stored Procedure on Web Intelligence (WebI) reports on Business Objects XI R2 ?

I found the following info in Bob forum and elsewhere on the net.

STEPS:

Applicable to universes that are built with ODBC or OLEDB connections.

[1] Locate the ODBC.SBO or OLEDB.SBO files

[2] Usual Location:

DRIVE:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\

DRIVE = your installation drive - be it C:\ , D:\ or something else.

/*

So, if BOE is installed in your D:\ drive then, the ODBC and OLEBD SBO files will be located in the following location:

D:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc

D:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\oledb

*/

[3] Change parameter Force SQLExecute in "ODBC.SBO" to "Always"

[4] Add parameter Force SQLExecute = Always into OLEDB.SBO

> You can do the same on on the OLEDB.SBO file if need be

[5] Create query in WebI with the same structure your SP returns

> Same structure = same data types and equal number of columns

[6] Change SQL to following

Change your code from the WebI Query Panel > SQL > Custom:

SET NOCOUNT ON -- add exactly like this

/*SELECT any_field FROM any_table*/ -- this is the code generated by WebI

EXCE Your_Stored_Procedure @prompt('enter value','A',)

-- this is your stored procedure

That looks simple. Now let's have a quick walkthrough. Stay tuned for Part 2 ...

On the fly Case Statements in Web Intelligence Reports

Problem: Due to having a little flaw in the data (in database) duplicate rows are being produced in (WebI) reports.

(1) Solution: Use CASE statements


> 122 Unique WorkItem subtypes

One of the the WorkItem SubTypes has a duplicate

'Problematic Payment - Overage '
'Problematic Payment - Overage'

One with and an extra space.

In the WebI Report - it is the following Dimension: (click view SQL)

WORKITEM.WORKITEMSUBTYPE, (here WORKITEMSUBTYPE is the Dimension - i.e. Column)

RESOLUTION: Go to

CASE WHEN WORKITEM.WORKITEMSUBTYPE NOT IN ('Problematic Payment - Overage ' ,'Problematic Payment - Overage')
> anything other than the two types above

THEN WORKITEM.WORKITEMSUBTYPE
> use the original value from database

ELSE 'Problematic Payment - Overage'
> otherwise, use 'Problematic Payment - Overage' - meaning, combine/sum the corresponding values of those two cells into one cell

END WORKITEMSUBTYPE,
> finish this (Case statement) logic and name this column as WORKITEMTYPE

-- WORKITEM.WORKITEMSUBTYPE
> I commented out the existing Dimension

This way, my column name is Identical to the Dimension name and thus the report runs fine and groups those two types into a single cell and eliminates the duplicate row.

Note: when you hit 'validate' - make sure, it prompts you with new LOVs (lis of values); else, it doesn't gets rid of the prompts. I've seen the exact same behavior in Crystal Reports too.

2nd solution: though this is a temporary fix, the same exact Case needs to be applied to our Universe so that it the duplicate problem will be eliminated in all WebI reports.

3rd / best solution: request DBAs so they permanently cleanse this data

On the fly Case Statements in Web Intelligence Report: Image Walkthrough

1) Duplicate row located



2) Go to Edit Query > SQL (view SQL) : Locate the corresponding dimension / column



3) Insert your Case statement and comment out the dimension



4) Validate




5) Make sure, it prompts you with new LOVs (list of values); else, it gets rid of the prompts (and runs off of cached LOVs, I think). I've seen the exact same behavior in Crystal Reports too.




6) Verify validation



7) Duplicate row is summed to original row


8) Before and after shots

Nested SQL, Derived Table in Business Objects Universe, Multiple joins between tables

Though most of the time we deal with simple 1 to 1 join between tables, be it, inner or outer, sometimes relationships between two tables can be based on multiple columns due to the nature of business. I've faced many situations like this in my work.  

For example: A sales area is determined by a BrokerID (has it's own table), Company (has it's own table) and StateID (has it's own table). Now, these 3 fields/columns need to connect to a 4th table to pull proper information about sales area (i.e. Sales Area Number, Sales Reps, Sales Region, Sales regional manager and so on)--and this 4th table also has BrokerID, Company and StateID. In other words, I have to connect 3 columns from 3 different table to this 4th table. It's easy, so don't be scared. :)

In a Business Objects Universe, you could simply drag and join the relationship between tables and break the loops by creating aliases and/or contexts. But, if you ever come across nasty situation like this and want to avoid the never ending aliases and contexts, simply use a derived table. Though this code is for a Crystal Reports, it applies to universe scenario too. 

So, let's see the SQL now:

Select *

From

(

Select 

B.CommonColumn1 , 

B.CommonColumn2 ,

B.Column3 ,

B.Column4 ,

C.CommonColumn5 ,

C.Column6 ,

From TableB as B

Left Join TableC as C (nolock)

On B.Column4 = C.Column6 ,

) A -- This nested query, or query within a query is named as 'A'

-- This table 'A' could also be called a Derived table

-- Double Joins --

Left Join TableD As D (nolock)

On A.CommonColumn1 = D.CommonColumn7

And D.Column8 = 'Y' -- this string 'Y' is just an example

-- basically an additional filter

--- Tripple Joins ---

Left Join TableE as E (nolock)

On D.CommonColumn9 = E.CommonColumn10

And A.CommonColumn2 = E.CommonColumn11

And A.CommonColumn5 = E.CommonColumn12

/*

>>>> A Real Life example could be like the following: >>>>>>

*/

Select       * -- everything

From

-- dbo.CO_Contract AS C  -- Need to connect to 2 (depended) columns from this table 

( 

Select

C2.PropertyAccountID, 

C2.Company,

C2.StateID,

A.StateID,    

  

-- Column 1 is native (C2.Company) – which becomes C.Company

-- and Column 2 is from CO_Address table - A.StateID

-- A.StateID becomes C.StateID, because this entire nested query is named ‘C’ 

From dbo.CO_Contract AS C2

Left Join dbo.CO_Address AS A ON

C2.AddressID = A.CO_ID 

) C -- Again, this is the nested query and it's named as 'C'

-- DOUBLE JOINS --

Left Join dbo.CO_RelatedParty            AS RP

On C.PropertyAccountID = RP.PropertyAccountID AND RP.RE1Flag = 'Y'

--- TRIPPLE JOINS ---

-- joining to ‘BSA’, 2 joins from ‘C’ and one join from ‘RP’

-- In an Universe, query such as this, used in a Derived table, will solve many loops (and traps)

Left Join dbo.CO_BrokerSaleArea BSA

On RP.PartyID = BSA.BrokerID 

And C.Company = BSA.Company 

And C.StateID = BSA.StateID 
 ------------------------------------------------------------------------