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

Quick steps to recover BO content from Backup

Steps to recover Business Objects objects, including all types of Reports and Universes, from Backup. (look for a better, more detailed version in BO Admin guide)


You always have the following 2 components in a BOXI environment: CMS Database and Filestore folder. We'll call the current/present versions CMS_DB and Filestore. 


1) Stop all BOXI services from CCM.


2) Get a copy of CMS_DB from yesterday or whenever you had a proper working BO environment, from where you could retrieve your lost objects; rename it to CMS_Backup. System Admins or DBA will get you this. Place CMS_Backup in any DB server. 


3) Rename your current Filestore folder to Filestore_Current. 

4) Get a copy of the Filestore folder from the same date as you got your CMS_DB backup in #2; put this in the same directory where you have Filestore_Current. 

5) Create a Data Source X (ODBC or whatever u guys use) and point to CMS_Backup.

8) Point CMS (service) from CCM > CMS > Configuration > using X to CMS_Backup. 

9) Start CMS, Input File Repository, Output File Repository. BO will act as if it's in the past; we'll call this BO Historic. 

10) Start Import Wizard and Import your lost objects. Source BO > Destination BIAR > browse and pick your Reports, Universe, etc from this BO Historic > BIAR them. 

11) STOP all BO services

12) Rename Filestore (which is really from Filestore folder from backup) to Filestore_Backup. 

13) Point CMS to your CMS_DB using the existing Data Source. 

14) Start ALL BO services. 

15) Import your lost objects from the BIAR file to present environment. Import Wizard > Source BIAR > Destination BO > Automatically Rename blah blah blah > done.

BO 3.1 Deployment Part 2 - Installation

Video Walkthrough: Steps to perform the initial installation, including pointing the BOE 3.1 installation to existing CMS and Audit databases, deploying TomCat and IIS web servers, introduction to Server Intelligence Agent and a quick glance at the 3.1 CMC.

BO 3.1 Deployment Part 1 - ODBC

This is the beginning of the series of videos that will cover a complete deployment of Business Objects R 3.1 in VM Windows 2003 environment with SQL Server 2005. 

Video Walkthrough: Assuming your DBA (or you) will create the 2 databases for a BO 3.1 deployment, we will start off with the creation of ODBCs pointing to these DBs, which are of course a CMS database and an Audit database. 

Universe LOVs are bad for your health

Well, it's bad for the health of the environment. And if your BO environment is not healthy, you'll be bugged constantly by users--'...oh this report is so slow...ohh, the dates don't load up properly...ohh, Business Objects is acting weird...' and so on. Ring a bell? :) And thus, you too will be sick; sick and tired of complaints and not knowing what the heck is up in your environment.


Topic Intro: Properly designing Universes so that unnecessary LOVs do not get associated with objects.

Video Walkthrough: We're gonna go over the basic steps required to identify objects that should not have LOVs associated with them and how to proactively avoid the problem from universe by disassociating the LOVs options in objects' properties.

Dissecting Auditor using VIEW

Topic Intro: Querying Business Objects Audit Database directly by creating a View.

Video Walkthrough: As I've mentioned in the previous video, we're gonna create a VIEW in the BO Audit Database using the following code that's available on Bob Forum and other places on the web. We will also run some test SQL queries to find some Audit information directly from the Audit database.

-------------------------BEGIN--------------------------

create view AUDITOR_View as
SELECT        server_process.application_type_id as [Application_Type_ID],
              cast(application_type.application_type_description as varchar(50)) as [Application_Type_Description],
              audit_detail.detail_id as [Detail_ID],
              audit_detail.detail_text as [Detail_Text],
              detail_type.detail_type_id as [Detail_Type_ID],
              cast(detail_type.detail_type_description as varchar(50)) as [Detail_Type_Description],
              audit_event.Duration as [Duration],
              audit_Event.Error_Code as [Error_Code],
              cast(audit_event.Event_ID as varchar(32)) as [Event_ID],
              audit_event.Event_Type_ID as [Event_Type_ID],
              cast(event_type.event_type_description as varchar(50)) as [Event_Type_Description],
              cast(audit_event.Object_CUID as varchar(32)) as [Object_CUID],
              audit_event.Start_Timestamp as [Start_Timestamp],
              cast(audit_event.User_Name as varchar(50)) as [User_Name],
              cast(audit_detail.server_cuid as varchar(32)) as [Server_CUID],
              cast(server_process.server_name as varchar(50)) as [Server_Name],
              cast(server_process.server_fullname as varchar(50)) as [Server_Fullname],
              cast(server_process.server_version as varchar(50)) as [Server_Version]
FROM APPLICATION_TYPE INNER JOIN
             (SERVER_PROCESS INNER JOIN (
                 (AUDIT_DETAIL INNER JOIN (
                      AUDIT_EVENT INNER JOIN
                          EVENT_TYPE ON
                              AUDIT_EVENT.Event_Type_ID =
                              EVENT_TYPE.Event_Type_ID) ON (
                                  AUDIT_EVENT.Server_CUID =
                                  AUDIT_DETAIL.Server_CUID) AND
                                     (AUDIT_DETAIL.Event_ID =
                                      AUDIT_EVENT.Event_ID)) INNER JOIN
                                          DETAIL_TYPE ON
                                              AUDIT_DETAIL.Detail_Type_ID =
                                              DETAIL_TYPE.Detail_Type_ID) ON
                                                  SERVER_PROCESS.Server_CUID =
                                                  AUDIT_DETAIL.Server_CUID) ON
                                                      APPLICATION_TYPE.Application_Type_ID =
                                                      SERVER_PROCESS.Application_Type_ID

-------------------------END--------------------------

Business Objects Auditor: SETUP

General Intro: Proper Auditing is an important part of any business, which allows decision makers to see the behind-the-scene views of their business. Business Objects is no different. It is essential for all BO Administrators to have in depth knowledge about the Auditing concepts in Business Objects XI platform. Auditing in Business Objects reveals second by second detailed activities of all users actions including refresh, read, schedules, failures, logins; and server actions including, session duration, session server, session server type, and much much more.

Video Walkthrough: We will go over the basics of Auditor and how to configure Auditing in Business Objects.

Topics Covered:
1) CMS configuration to set up Audit.
2) Enabling available auditable actions for auditable servers via CMC.