I saved the JSON and used it as the second source in PowerBI.
Votes details (View)
Now, we need to join these two sources.
To makes things secure I did a left join.
I want all rows from Votes and only those rows which match from Members.
In practice, all Votes should have a row in Members, but just in case there aren't I use this type of join.
It took me a bit of time, but with some websearch I managed to write a left join formula, which returns me only the fields I'm interested into for the moment:
Both data sets contain the ID number of the member.
In Votes, there is a field containing the result of the vote (Yes, No) and the Member ID.
In Members source, there is field containing the Member ID, Name of consituency (Geo Area) and Gender.
I used this view to create the Map and Gender distribution of votes.
Using JSON as a source in PowerBI is easy, however takes time to get used to where to click and what to do.
I'll not go into details how to do it here.
Essentially, it is expansion of fields and use of simple string function to format the values.
In a nice way, PowerBI saves all your transformations in a script and essentially executes them every time the data sources are refreshed.
This is my first video tutorial explaining how to replace broken screen of a laptop. I did it by myself (just watching some other tutorials). I also composed the "elevator" music at the background and edited the video using Movie Maker. I hope you like it and will be of good use!
New screen bought from: http://www.laptopscreen.com/ Music: AGeDaCi Smooth Composed by: Iviglious (using GarageBand) Video created by: MS Movie Maker
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 :)
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.
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:
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;
/
