From Web to Web - MS BI demo

What are the Microsoft Business Intelligence tools (stack) and what are they used for?
Here I give you one easy and understandable demonstration of the SSIS, SSAS and SSRS tools using MS SQL Database and some sample data from a weather website.

Let's say that you want to do your own report and graphics of the temperatures of London.
What do you need? A machine (I am using a small VM), a database, where to store the data (in my case I choose MS SQL Database), the MS BI tools (SSIS, SSAS, SSRS) and some time :).
The idea is the following:
1. There is data of the temperature in a website. For every day we have the highest temperature and the lowest and some more data like humidity, pressure, etc.
2. The website allows us to download this data for specific period (in my case the month of January 2014) into a CSV file. Each line in the file is a day with it's data. The file has 31 lines.
3. Using the SSIS (Microsoft Visual Studio) we create a SSIS projects and create a package, which i Extracting the data from the file, Transforming it and Loading it into the database (ETL).
4. Using the SSAS (Microsoft Visual Studio) we create a SSAS project with a cube, dimensions and measures based on the data loaded into the database. We need to configure the SSAS server and deploy the SSAS project into it in order to be accessible by the SSRS.
5. Using the SSRS (Microsoft Visual Studio) we create a SSRS project with a report containing graphic and table with the temperature data based on the cube from the SSAS project.
6. Configuring the SSRS local server and publishing/deploying the report into it will allow us to visualize the report directly into our web browser. In this way the cycle is closed (from Web to Web).

So...MS BI is a set of tools allowing you to Extract, Test and Load data and then perform Analysis and at the end generate Reports of those analysis.
Somebody will say: "Why do you need all of this when it's possible to show the results directly from the web site using some scripting?". For the current example this is true, you don't need to have all of this, but for a complex system which has 10 or more different datasources (web sites, files, etc.) and If you want to create a lot of different reports, then you need this.
The cool things about the MS BI stack are:
- There are 3 separate parts (ETL, Analysis, Reports). Splitting the work.
- It is able to work with different databases (at the same time).
- The design is very visual, so the developing and debugging are easier.
- Migration is easy, everything is save as separate projects.
This is BI, it's the future and I like it :)

WIVOS - Web based Inteactive Visual Operating System

Click here to open - WIVOS
Using HTML, JavaScript and simple CSS I created this demo web page showing, how powerfull JavaScript can be.
It has a Windows Desktop looking design.
There is a task bar, start button and 4 sample icons:

You can move and place the icons all over the desktop and when double clicked, a window is opened:

The windows can be minimized, maximized, restored and closed.
There are some defects and is runs not so smoothly, but still demostrates the power of JavaScript.

SSIS - How to process SSAS cubes

In this post I will show how to create, configure and monitor a processment of cubes using Microsoft SSIS package.
I'll start from the beginnig:
1. Create a new SSIS project:

Automatically a SSIS package is created with a name "Package.dtsx". If you want you can rename it.

2. Create Data Sources. You need to create the data source of the cubes (where are located your cube/s). And also my recomendation is to create the data source of the log database - MS SQL Server Database. In this database will be stored a log of all actions that the SSIS package will perform. This is one way to monitor the SSIS package activities and also keep history and even create statistics.

2a. Create SSAS Cubes Data Source:

You can check this check box in order next time to not appear this kind of "warning" message.

2b. Create SQL Server Database Data Source
In the same way like the creation of the Cube Data Source, just using the SQL Server Database connector and connecting to a SQL Server database.

3. Creation of Analysis Services Processing Task:

We will use just this type of task to process cubes and/or dimensions.

4. Configuring the Dimensions processment

4a. You can rename the Task:

4b. Add connection to the Cubes SSAS database:

4c. Add the cube first:

4d. Add all related dimensions to this cube:

4e. Remove the cube (leave only the dimensions):

5. Add another Analysis Processment task (you can copy paste the previous) and configured it in the same way like the dimensions, but this time just leave the cube (steps 4b - 4c).

6. Connect the 2 tasks. When the first task is completed successfully the execution flow will execute the second task:

7. Test the package (execute it):

Done.

PL/SQL - How to create package with functions and procedures

In this post, I will show you the techniques, design, ideas and code for creating a package with procedures and functions.

The PL/SQL package has 2 parts of code: package structure (.pks) and package body (.pkb).
- Package structure: It is short declarative of the procedures and functions which the package has. Pay attention that the names and parameters are the same as in the package body.
Here is a sample code:

CREATE OR REPLACE PACKAGE EXAMPLE_MGR AS
/*
    This package is designed to manage ...
    
    Created by: Ivaylo Shalev
    Created on: 01 January 2014
    Last updated on:
 
*/

---------------------------
-- Help functions
---------------------------
  FUNCTION TABLE_EXIST(pTableName IN VARCHAR2) RETURN NUMBER;
  FUNCTION VIEW_EXIST(pViewName IN VARCHAR2) RETURN NUMBER;
  FUNCTION IS_RUNNING(pObjectName IN VARCHAR2, pFinishStr IN VARCHAR2) RETURN NUMBER;
  
---------------------------
-- LOG procedures
---------------------------
  PROCEDURE WRITE_LOG(pLogType IN VARCHAR2, pObjectName IN VARCHAR2, pMessage IN VARCHAR2);
  PROCEDURE LOG_INFO(pObjectName IN VARCHAR2, pMessage IN VARCHAR2);
  PROCEDURE LOG_ERROR(pObjectName IN VARCHAR2, pMessage IN VARCHAR2);
  
---------------------------
-- PARTITION procedures
---------------------------
  PROCEDURE P_UPDATE_PARTITIONS(pObjectName IN VARCHAR2,
                              pTableName IN VARCHAR2,
                              pPeriodDaysPast IN NUMBER,
                              pPeriodDaysFuture IN NUMBER);
         
  FUNCTION UPDATE_PARTITIONS(pObjectName IN VARCHAR2,
                              pTableName IN VARCHAR2,
                              pPeriodDaysPast IN NUMBER,
                              pPeriodDaysFuture IN NUMBER) RETURN NUMBER;
  FUNCTION DROP_PARTITIONS(pTableName IN VARCHAR2, pMinPartDate IN DATE) RETURN NUMBER;
  FUNCTION BUILD_PARTITIONS(pTableName IN VARCHAR2, pMaxPartDate IN DATE) RETURN NUMBER;

---------------------------
-- Custom procedures and functions
---------------------------
  
END EXAMPLE_MGR;
/

- Package body: It is the main source code of the package. Here you type the logic of the objects.
Here is a sample code:

CREATE OR REPLACE PACKAGE BODY EXAMPLE_MGR AS
/*
    This package is designed to manage ...
    
    Created by: Ivaylo Shalev
    Created on: 01 January 2014
    Last updated on:
 
*/

---------------------------
-- Help functions
---------------------------
  FUNCTION TABLE_EXIST(pTableName IN VARCHAR2) RETURN NUMBER -- 0 - exist, -1 doesn't exist
  IS
    tbl_count   number;
  BEGIN
    tbl_count := 0;
    select count(*) into tbl_count from all_tables where table_name = pTableName;
    
    IF tbl_count = 0
    THEN
      LOG_ERROR(pTableName,'Table "'||pTableName||'" does NOT exist!');
      RETURN -1;
    ELSE
      RETURN 0;
    END IF;
  END TABLE_EXIST;
  
  FUNCTION VIEW_EXIST(pViewName IN VARCHAR2) RETURN NUMBER -- 0 - exist, -1 doesn't exist
  IS
    vw_count   number;
  BEGIN
    vw_count := 0;
    select count(*) into vw_count from all_views where view_name = pViewName;
    
    IF vw_count = 0
    THEN
      LOG_ERROR(pViewName,'View "'||pViewName||'" does NOT exist!');
      RETURN -1;
    ELSE
      RETURN 0;
    END IF;
  END VIEW_EXIST;
  
  FUNCTION IS_RUNNING(pObjectName IN VARCHAR2, pFinishStr IN VARCHAR2) RETURN NUMBER
  IS
    vLastID NUMBER;
 vResult NUMBER;
  BEGIN
    
 SELECT MAX(ID) INTO vLastID
 FROM EXAMPLE_MGR_LOG
 WHERE OBJECT_NAME = pObjectName;
 
 SELECT 1 - COUNT(*) INTO vResult
 FROM EXAMPLE_MGR_LOG
 WHERE ID = vLastID
   AND (
     (TYPE = 'INFO' AND MESSAGE LIKE pFinishStr)
  OR
  (TYPE = 'ERROR')
   );
 
 RETURN vResult;
 
  END IS_RUNNING;

--------------------
-- LOG procedures
--------------------
  PROCEDURE WRITE_LOG(pLogType IN VARCHAR2, pObjectName IN VARCHAR2, pMessage IN VARCHAR2) AS
  BEGIN
    INSERT INTO EXAMPLE_MGR_LOG (TYPE, OBJECT_NAME, MESSAGE)
    VALUES (pLogType, pObjectName, pMessage);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END WRITE_LOG;
  
  PROCEDURE LOG_INFO(pObjectName IN VARCHAR2, pMessage IN VARCHAR2) AS
  BEGIN
    WRITE_LOG('INFO',pObjectName, pMessage);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END LOG_INFO;
  
  PROCEDURE LOG_ERROR(pObjectName IN VARCHAR2, pMessage IN VARCHAR2) AS
  BEGIN
    WRITE_LOG('ERROR',pObjectName, pMessage);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END LOG_ERROR;

---------------------------
-- PARTITION procedures and functions
---------------------------
  PROCEDURE P_UPDATE_PARTITIONS(pObjectName IN VARCHAR2,
                             pTableName IN VARCHAR2,
                             pPeriodDaysPast IN NUMBER,
                             pPeriodDaysFuture IN NUMBER) AS
  vRes NUMBER;
  BEGIN
      vRes := UPDATE_PARTITIONS(pObjectName,
                              pTableName,
                              pPeriodDaysPast,
                              pPeriodDaysFuture);
  END P_UPDATE_PARTITIONS;
  
           
  FUNCTION UPDATE_PARTITIONS(pObjectName IN VARCHAR2,
                              pTableName IN VARCHAR2,
                              pPeriodDaysPast IN NUMBER,
                              pPeriodDaysFuture IN NUMBER) RETURN NUMBER
  IS
  v_SQL VARCHAR2(2000);
  BEGIN
    LOG_INFO(pObjectName, 'Updating partitions...');
    
    IF BUILD_PARTITIONS(pTableName, SYSDATE + pPeriodDaysFuture) = -1
        THEN
            RETURN -1;
    END IF;
    
    IF DROP_PARTITIONS(pTableName, SYSDATE - pPeriodDaysPast) = -1
        THEN
            RETURN -1;
    END IF;
    
 LOG_INFO(pObjectName, 'Finished updating partitions...');
    RETURN 0;
  EXCEPTION
    WHEN OTHERS THEN
      LOG_ERROR(pObjectName,'Error at UPDATE_PARTITIONS() for table "'||pTableName||
                          '", for period of days past = '||pPeriodDaysPast||
                          ', future = '||pPeriodDaysFuture||' :'||chr(10)||
                          'SQL=>'||v_SQL||'<='||chr(10)||
                          'Error Message: '||SUBSTR(SQLERRM,1,4000));
      ROLLBACK;
      RETURN -1;
  END UPDATE_PARTITIONS;
  
  FUNCTION DROP_PARTITIONS(pTableName IN VARCHAR2, pMinPartDate IN DATE) RETURN NUMBER
  IS
    sql_stmt    varchar2(1000);
    day_count   number;
  BEGIN
  
    IF TABLE_EXIST(pTableName) = -1 THEN
      RETURN -1;
    END IF;
    
    FOR j IN (SELECT table_name, min(partition_name) v_partition_name
            FROM user_tab_partitions p
            WHERE table_name = pTableName GROUP BY table_name)
    LOOP
     day_count:=0;
     while to_date(j.v_partition_name,'YYYY_MM_DD')+day_count < pMinPartDate
     loop

        sql_stmt := 'ALTER TABLE ' || j.table_name  ||
                 ' DROP PARTITION "' ||
                    to_char(to_date(j.v_partition_name,'YYYY_MM_DD')+day_count,'YYYY_MM_DD') ||
                    '"';
        
        Execute Immediate sql_stmt;
        day_count:=day_count+1;  
     END LOOP;

    END LOOP;

    COMMIT;
    
    RETURN 0;
  EXCEPTION
    WHEN OTHERS THEN
      LOG_ERROR(pTableName,'Error at DROP_PARTITIONS() for table "'||pTableName||
                          '", with min partition date = "'||pMinPartDate||'" :'||chr(10)||
                          'SQL=>'||sql_stmt||'<='||chr(10)||
                          'Error Message: '||SUBSTR(SQLERRM,1,4000));
      ROLLBACK;
      RETURN -1;
  END DROP_PARTITIONS;
  
  FUNCTION BUILD_PARTITIONS(pTableName IN VARCHAR2, pMaxPartDate IN DATE) RETURN NUMBER
  IS
    sql_stmt    varchar2(1000);
    day_count   number;
  BEGIN
  
    IF TABLE_EXIST(pTableName) = -1 THEN
      RETURN -1;
    END IF;
    
    FOR j IN (SELECT table_name, max(partition_name) v_partition_name
              FROM user_tab_partitions p
              WHERE table_name = pTableName
                GROUP BY table_name)
    LOOP
      day_count:=1;      
      
      while to_date(j.v_partition_name,'YYYY_MM_DD')+day_count < pMaxPartDate
      loop
        sql_stmt := 'ALTER TABLE ' || j.table_name  ||
                    ' ADD PARTITION "' ||
                    to_char(to_date(j.v_partition_name,'YYYY_MM_DD')+day_count,'YYYY_MM_DD') ||
                    '" VALUES LESS THAN (TO_DATE(''' ||
                    to_char(to_date(j.v_partition_name,'YYYY_MM_DD')+day_count+1,'YYYY-MM-DD') ||
                    ' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) '||
                    'NOLOGGING '||
                    'TABLESPACE LARGE_TABLES_TS '||
                    'PCTFREE    10 ' ||
                    'INITRANS   1 ' ||
                    'MAXTRANS   255 ' ||
                    'STORAGE    (' ||
                    '      INITIAL          200K'||
                    '       BUFFER_POOL      DEFAULT' ||
                    '             )';

        Execute Immediate sql_stmt;

        day_count:=day_count+1;  
      END LOOP;
    END LOOP;
  
    COMMIT;
    
    RETURN 0;
  EXCEPTION
    WHEN OTHERS THEN
      LOG_ERROR(pTableName,'Error at BUILD_PARTITIONS() for table "'||pTableName||
                           '", with max partition date = "'||pMaxPartDate||'" :'||chr(10)||
                           'SQL=>'||sql_stmt||'<='||chr(10)||
                           'Error Message: '||SUBSTR(SQLERRM,1,4000));
      ROLLBACK;
      RETURN -1;
  END BUILD_PARTITIONS;
  
  
---------------------------
-- Custom procedures and functions
---------------------------
  
  
END EXAMPLE_MGR;
/