View VS SYNONYM VS TRIGGER in SQL

What is a view?

A view is a virtual table based on the result set of an SQL statement.

Here is an example.

create view [sales].[v.salesbyperson2]
as
select
  salespersonid, round(totaldue,2) as salesamount
 from
sales.sales

What is a synonym?

A synonym, like the name, is an alternate name we create for another database object. 

Here is the syntax:

create synonym [synonym_name]
for [server_name].[database_name].[schema_name].[object_name]

What is the trigger?

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. 

Regardless of whether or not any table rows are affected.

Here is an example:

create trigger reminder1
on sales.customer
after insert, update
as raiserror ('notify customer relations', 16, 10);
go

If you are interested in or have any problems with SQL or Business Intelligence, feel free to contact me.

Or you can connect with me through my LinkedIn.

How to do web scrapping in Power Query in Power BI?

On web, there are public data sourse which can be imported and transformed to make insight reports or dashboards.

There are two circumstances:

This kind of importing of tables is super easy.

For example, we can try to make an easy one.

Here is the web link: https://en.wikipedia.org/wiki/Demographics_of_China. What we want is to import the tables from a Wikipedia webpage.

The simple steps are as follow:

Open Power BI Desktop–>Click Get Data–>Click Web–> Paste the URL in the dialog and click OK.

So, there is the screenshot:

In Navigatot dialog, we can select the corresponding table that we want to import and transform it.

This kind of importing from web in Power BI is simple. Because Power Query can identify the table in HTML and tables are in the <table>…</table> tags.

However, many tables in HTML are different.

If we meet this kind of circumstance, we will end up seeing the Document entity in the Navigator.

Next blog, we will demonstrate how to extract the relevant elements in HTML with Power Query in Power BI.

The tricky part id how to find the correct path in the HTML source tree, which needs you to have a basic but not prior HTML knowledge.

To motivate you to keep reading, here is the final web scarping screenshot which i made recently:

If you are interested in or have any problems with Power BI or Power Query, feel free to contact me.

Or you can connect with me through my LinkedIn.

Create a SSIS Project in Visual Studio 2015

Installation:

1. SQL Server Data Tools 2015(install a shell of Visual Studio 2015)
2. Visual Studio 2015

What is SSIS?

SSIS stands for SQL Server Intergration Services. SSIS is to do the ETL(Extract, Transform and Load) task for data warehouse.

SSIS can also update data warehouse, clean and mine data, create ‘packages’, manipulate data, etc.

To design ETL task in Visual Studio, we use the data flow in Visual Studio.

What is data flow?

A data flow defines a flow of data from a source to a destination.

Before designing the data flow, we can try to drag a data flow and drop it in the control flow.

What is a control flow?

It is a flow we can use to control the flow for different tasks. It provides the logic for when data flow components are run and how they run.

For example:

1. perform lopping
2. call stored procedures
3. move files
4. manage error handling
5. check a condition and call different tasks

It defines a workflow of tasks to be executed, often a particular order (assuming your included precedence constraints).

we can rename the data flow task in control flow.

For example, we can drag OLE DB Source, so we can connect to Relational Database.

Then we can follow the Microsoft official toturial to do it step by step.

https://docs.microsoft.com/en-us/sql/integration-services/lesson-1-1-creating-a-new-integration-services-project?view=sql-server-ver15

If you are interested in or have any problems with SSIS, feel free to contact me.

Or you can connect with me through my LinkedIn.

Dimensional Modelling

First of all, a concept of Data Warehouse is supposed to be clear. Data Warehouse is not a copy of source database with a name prefixed with ‘DW’.

DW is we can store data from multiple data sources to be used for historical or analysis report. Some data sources are .csv format, some are google docs, etc. So we need to aggregate them into one data source.

This is called Data Warehouse.

How to design it? The procedure is Dimensional Modelling.

What is the difference between relational and dimensional?

It is same as the difference like normalised VS denormalised.

Normalised:

1. Minimal data redundancy
2. Optimised for fast read and fast write
3. Current data only
4. Realtime data

Denormalised:

1. Redundancy data storage for performance
2. Fast read only
3. Non-realtime data
4. Current and historical data

The next part is about fact table and dimension table in dimensional design.

Fact table:

1. Data that can be measured
2. Contains surrogate key, linking the associated measures or facts

Dimension table:

1. Descriptive information

Some types of dimensional models:

1. Accumalating snapshot table
2. Aggregate fact
3. Fact table
4. Factless Fact table
5. Snapshot table

If you are interested in or have any problems with Dimensional Modelling, feel free to contact me.

Or you can connect with me through my LinkedIn.

Some Prerequisite Knowledge About Spatial Data

Spatial Data Types:

Two kinds of spatial data types:

1. geometry: flat 2D surface with two dimensions. Supposed X = 3 and Y = 4, then our point representation will be like POINT (3 4).
2. geography: uses the same methods but the data type reflects the fact the we live on a curved 2D surface.

However, the two kinds of spatial data types is the need for the aforementioned Spatial Reference IDs (SRID).

SRID:

Both geometry and geography data types have two parts, the coordinates of the object and the SRID number

To check the list of SRID in SQL server, we can execute query statement as belows:

Geography::Point(Latitude,Longitude,SRID); 

The SRID number is set by EPSG standard. It dictates that the SRID of any geometry data is 0 and for Geography the default of SRID is 4326.

An example:

Here is a link of my Github of stored procedure, which aims to get the nearest suburb for each public transport stop:

https://github.com/jacquiwuc/PropertyAnalysis/blob/master/SQ_StationForSuburb.sql

In this stored procedure, it can be seen that

SET @geo1=geography::Point(@stationlat,@stationlong,4326);

Which is an application of transformation of the two spatial data types.

If you are interested in or have any problems with SQL, feel free to contact me.

Or you can connect with me through my LinkedIn.

SQL Correlated Subqueries

Recently I often made some mistakes about subqueries, so I wrote this blog about correlated subqueries.

Firstly, here is a SQL practicing website: https://sqlzoo.net.

It is free and easy for SQL beginners to do SQL exercise step by step.

A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query.

The technique relies on table aliases to identify two different usages of the same table, which means one usage is in the outer query and another one is in the subquery.

Here is a table called world, which is an example on the sqlzoo website:

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
…	…	…	…	…

Question: Find the largest country (by area) in each continent, show the continent, the name and the area.

SQL answer using subquery:

SELECT continent, name, area 
FROM world x
WHERE area >= ALL
    (SELECT area FROM world y
     WHERE y.continent=x.continent AND area>0)

One way to interpret the SQL line in the WHERE clause that references the two table is “… where the correlated values are the same”.

In this example, we can tell “select the country details from the world table where the area is larger than or equal to the area of all countries where the continent is the same”.

If you are interested in or have any problems with SQL, feel free to contact me.

Or you can connect with me through my LinkedIn.

Be granted my degree with First Class Honours

I once wrote a blog to record my Master journey, and I receive a big news today.

I was granted my Master of Computer and Information Sciences wirh First Class Honours!

My Master journey is full with precious memories, including Summer Research Award, Master research thesis(A grade), two publications and the winner of X Challenge: The Idea. I already recorded the whole journey in my blog: https://lnkd.in/gVpHmTh 

Until now, I am also a regular blogger on wordpress (https://jacquiwu.com/) and medium(https://lnkd.in/gUfrYwQ), where I share technical experience about Business Intelligence and Data Science. 

Thanks for all the support in my nearly two years study, especially my supervisor. He taught me a lot, including enhancing research and learning ability. Although I am not ready to enhance my research ability in academia any more, I will keep powering up myself in Business Intelligence and Data Science area.

First Class Honours

If you are interested about New Zealand University, selecting papers, Summer Research Award, or how to publish papers on journals or international conferences, feel free to contact me and I can give you some suggestions.

Or you can connect with me through my LinkedIn.

Thesis Grade！！

Congratulations myself for securing an ‘A’ grade for my Master thesis (120 points)!

Looking forward to my graduation ceremony this December!

If you are interested about New Zealand University, selecting papers, Summer Research Award, or how to publish papers on journals or international conferences, feel free to contact me and I can give you some suggestions.

Or you can connect with me through my LinkedIn.

An Introduction to Power BI (Power BI 101)

This article we will provide an introduction to Power BI.

It is a Microsoft business analytics service to provide interface to create reports and dashboards with interactive visualisations.

It has an advantage: easy to use (self service BI).

First of all, Power BI official guided learning material.

It’s very structured and good training tutorial.

As I talk in previous blog about this learning platform, it is fun to get points and badge.

What is the workflow of Power BI?

1. Bring data in using Power BI desktop, manipulate data and build reports.
2. Publish it to Power BI service.
3. Share reports and dashboards to others.

Here we go to the first step:

Download the Power BI desktop.

Power BI desktop version provides data warehouse capabilities:

1. ETL
2. Calculates column
3. Measures

After we download it, we can find there are five panels in Power BI interface:

1. Fields: where the datasets
2. Data: view and manipulate the data
3. Reports: place visualizations to build reports
4. Dashboards: choose the graphs to use
5. Relationships: view/change relationships in the dataset

Through these panels, we can manipulate data and build reports.

Once the report is completed, we can publish it onto Power BI Service on the cloud.

Reference sources:

https://www.sqlbi.com/ref/power-bi-visuals-reference/

If you are interested in or have any problems with Power BI, feel free to contact me.

Or you can connect with me through my LinkedIn.

Natural Language Processing：A Brief Agenda to Get You Started

Recently I start to tutor one paper (COMP700 Text and Vision Intelligence) for AUT students in the weekends.

This paper mostly involves in Computer Vision and Text Mining (Natural Language Processing).

The two aspects are common applications about Aritificial Intelligence.

So, in this blog, I will provide a brief agenda about Natural Language Processing for people who want to get kick-start in Artificial Intelligence area.

General Intro NLP
~General overview
~Tasks to describe languages

Language model:
~Statistical model
~Neural network model

NLP with deep learning:
~Recurrent Neural Network
~Convolutional Neural Network

NLP at different levels:
~Phonetic/Phonological Analysis
~OCR
~Morphological analysis
~Syntactic analysis
~Semantic interpretation
~Discourse processing

NLP in industry:
~Search
~Automated/assisted translation
~Speech recognition
~Sentiment analysis
~Chatbot

What need to be done-representing:
~Morphology
~Sentence structure
~Senmantic
~Vectors

Word vector:
~Wordnet: a resource containing lists of synonym sets and hypernyms
~One-hot: as discrete symbols
~TF-IDF
~Word embedding: A word’s meaning is given by the words that frequently appear close-by

Knowledge graph-semantic drive way:
~Ontologies&Description logic
~OWL&RDF
~Semantic web
~Dgraph, Neo4j

Describing Language tasks:
~Segment(for Chinese)
~POS: Part Of Speech
~NER: Named Entity Recognition
~Algorithml:Rule-based taggers, Probabilistic tagger: HMM and Veterbi, Perceptron, Conditional model: CRF

Statistical Language Model:
~NNLM 2003
~RNNLM 2010
~CBoW: Continuous Bag-of-Words Model
~Skip-gram
~Word2vec

Neural Network:
~Neuron
~Activation function
~Back propagation: What’s cost function, How cost function used updated parameters

Activation function:
~Sigmoid
~Tanh
~ReLu (rectified lieaner)
~Softmax
~…

Deep Learning:
~Learn (multiple levels of) representation and an output from ‘raw’ inputs x
~Universal, learnable framework for representing world, visual and linguistic information
~Can learn unsupervised (from raw text) and supervised
~Why now popular:A large dataset, Faster machines and multicore CPU/GPU
~Why it works

Deep Learning Models:
~Feed-Forward Networks
~Recurrent Neural Networks: simple RNN, LSTM, GRU
~Generative Neural Networks

If you are interested in or have any problems with Natural Language Processing, feel free to contact me.

Or you can connect with me through my LinkedIn.