There are several parts of my Business Intelligence journey.
So I write this blog for recording some cheat sheets.
Installation:
Firstly, some tools need to be installed on the laptop.
All of them are Microsoft softwares, so I prepare a surface laptop 2.
SQL Server 2016 Developer Edition: Seclect the features we need to install, such as SQL Server Integration Services(SSIS), SQL Server Analysis Services(SSAS) and SQL Server Reporting Services(SSRS).
SQL Server Management Studio(SSMS)
SQL Server Data Tools(SSDT)
Report Builder
What is Business Intelligence(BI)?
I have mentioned it in the previous blog. It is to transform raw data into meaningful information for analysis.
BI Process:
Import data from different data sources
Produce reports or graghs through SSIS, SSAS and SSRS
Make business decisions
Some terms:
ETL: Extraction, Transformation and Loading
OLTP: Online Transaction Processing
Cube: Multi-dimensional data structure built using dimensions and facts, using MDX (Multi dimensional expression) language
SQL and Relational Database: which I have mentioned them in the previous blog
Primary Key: A single attribute or an unique id in a Relational Database
Foreign Key: A set of attributes that references a candidate key in a Relational Database
Some differences:
Traditional Normalized Database VS Data Warehouse: Formal one has no duplicate data but latter one has duplicate data for efficiency.
Null VS Blank(where clause): For null, the clause is where xxxxx is null; For blank, the clause is where xxxxx is ”
Delete VS truncate: truncate table xxxxx ->no rollback&faster; delete from xxxxx -> rollback
The terms and job titles in the data field are extraordinarily large, such as business analysis, data analysis, data science, etc.
They often stun everyone, so this article we will talk about all of them, especially the differences.
Business Analysis VS Data Analysis
Generally, data analysis means that using data to analyse a XXX problem. The pipeline includes data collecting, data warehousing, data cleaning, data visualisation, etc.
As we can see, there is a blank in the sentence.
Actually, data analysis can be applied to different areas, and that is also the meaning of ‘XXX’ .
It can be academic or commercial.
If ‘XXX’ is commercial, then data analysis will equal to business analysis. In other words, the business analysis is an application of data analysis.
There is a main difference between data analysis and business analysis, which is the data sources.
The data source of data analysis positions is often based on the company websites, Apps, ERP system, etc.
And those job descriptions will require candidates master SQL, Python or R.
However, the data collected from those platforms often exists several problems, mainly the data quality, such as some missing data or data noise.
Those are caused by weak IT infrastructures and ‘econnoisseurs’ who want to register more user accounts.
However, the data sources of business analysis positions not just include those internal data collected by companies but also include a large amount of external sources. For example:
Industry studies
Qualitative interviews
Quantitative interviews
Internal data
Business Analysis VS Data Science
Because of Alpha Go, Artificial Intelligence is well-known to everyone.
However, the successful area of AI is not related to data analysis.
They focus on the computer vision and natural language processing, and their industrial applications are mainly in security and supply chain.
However, the usage of algorithms in commercial industry is limited because some departments in the company are hard to be represented by data and algorithm models.
This causes that algorithm can only solve the specific problems.
Firstly, algorithm is related to users directly. The famous example is risk control. Because the factors contributing to the user credits are easy to be built by an algorithm model, commonly logistic regression.
Secondly, forecasting algorithms. Business Intelligence is highly required to do forecasting.
Thirdly, dimensionality reduction algorithms. It is often to reduce dimensions so it is easy to evaluate a problem, such as new products.
Summary
In summary, algorithms are useful in business intelligence, however, it cannot replace it.
Business Intelligence is an application in commercial problems and algorithm models are tools to solve specific problems.
If you are interested in or have any problems with Business Intellgence, feel free to contact me .
This article we will show how to install Microsoft SQL Server 2017 on macOS, as we talk it in previous blog.
Which method we use?
Prior to SQL Server 2017, if we want to install it on macOS, a virtual machine (like Parallels Desktop) is essential. Then we can install a Windows system in it and then we install and run SQL Server.
Docker is a platform which can make softwares to run in it. It is called a container, which is an isolated environment.
2 Download and install Docker
If you haven’t installed Docker on your Mac, the next step is to install Docker.
Go to Docker page to download the .dmg file and then double click and install it according to the instructions.
3 Run Docker and increase the memory
Run Docker as you used to run other softwares and the next step is to increase the memory. This is because the default value of Docker memory is 2 GB but SQL needs at least 3.25 GB.
I recommend we set the memory value to 4 GB.
Click Docker icon on top menu of your Mac
Click Preferences
Set Memory under Advanced to 4 GB.
Click Apply&Restart
4 Download Microsoft SQL Server 2017
The next step is to download SQL Server 2017 from Terminal, which is an easy way.
Copy and paste the command in Terminal of your Mac:
docker pull microsoft/mssql-server-linux
Through it, the latest version of MS Server SQL can be downloaded.
5 Run a Docker image
Copy, change and paste the command in Terminal of your Mac:
We need to change the name and password as your own here.
This step is to run an instance of Docker image. A Docker image is a file, which is used to execute codes in a Docker container.
If you want to check, copy and paste the following command to see whether the Docker container is running:
docker ps
If it works, it will show like this:
6 Install the sql-cli command line tool
The next step is to install the sql-cli command line tool. It can allow you to run commands against your SQL Server instance.
Copy and paste the command in Terminal of your Mac:
npm install -g sql-cli
If an error happened and shows you do not have the permissions to access this file as the current user, just try add sudo in your command:
sudo npm install -g sql-cli
7 Connect to SQL Server
After sql-cli is installed, now we can connect to SQL Server using the mssql command.
mssql -u xxxxxxx -p xxxxxxx
Here xxxxxxx and xxxxxxx means your name and password.
Then you will see:
Now you’ve successfully connected to your instance of SQL Server.
Next blog we will continue our journey with SQL Server and Azure Data Studio. Maybe you will want to read more about Azure and Microsoft Learn in the previous blog.
If you are interested in or have any problems with Business Intelligence, feel free to contact me .
If you have a skill that is needed by a New Zealand accredited employer and they offer you full-time work when you are abroed, you may be able to get a Talent (Accredited Employer) Work Visa.
From the job description, we can find SQL knowledge is important.
What is SQL?
Firstly, I want to talk about Database(DB). We all know DB is a kind of software to store a large amount of data, mainly Relational DB.
Structured Query Language(SQL) is always linked with DB. SQL can be used to operate data, such as querying and updating.
The relationship between DB, data and SQL is like: DB is a plate, data is dishes on the plate and SQL is your fork.
Currently, most of the websites and Apps are based on SQL and DB.
There are several popular DB on the world, e.g., SQLite, MySQL, Postgres, Oracle and Microsoft SQL Server.
Among them, MSSQL Server is the most popular in New Zealand.
Luckily, all of them can support SQL although they have different characteristics.
It is like if you have the fork, you can operate dishes on different plates.
In general, BI refers to that companies slice and dice data to look for patterns. Then they make business decisions by the data insights.
When I ask other people this question: Do you know what is Business Intelligence?
I usually got the answer “No”.
However, I found most of people know what is Big Data. Of course, this is a buzzword.
Even those people who are not familiar with this area, they can tell me Big Data means the data is big…
This is a good thing. Even sometimes our understanding for terms is not precise.
It is common because a glossary consists of many knowledge systems.
It is hard to explain it in one sentence.
For example, Business Intelligence is a professional area, including ETL, Data Warehousing, etc. You can’t explain all of them to others.
Simply, BI is a pipeline which can transform data into information and then it creates business value.
How can we create business value using BI?
Reporting, which is common in New Zealand.
Reporting means using data visualisation techniques to visualise company’s business data.
It mainly focuses on finance, supply chain, marketing and human source departments.
For example, sales and marketing department cares about sales amount, order quantity, return rate, etc.
Then BI can be used to filter and extract analysis results. Tables and figures differ according to their subjects and they relate to each other.
Analysis about “abnormal” data.
Through data visualisation results, the abnormal data means some data indicators are beyond our predictions.
Sometimes it is positive that we are pursuing.
For example, average user registration amount is 1000 per month.
However, we find one month the number reaches 3000.
It is a kind of positive abnormality. Of course, sometimes the abnormal data can be negative.
We can analyse which factor contributes to the abnormality through one or more dimensions data tables and figures.
Those results can help to make better business plans.
Conclusions:
BI is actually a business and management area.
It comes from the presentation of visual analysis reports. Then it tries to find business problems (positive or negative) to improve business operations.
BI jobs in New Zealand:
According to Business Analyst on careernz website, BI analyst is in high demand and its salary is $81K-$104K per year.
Many companies hire BI analysts in New Zealand, including:
Government departments or tertiary universities/institutes
Finance companies
Marketing companies
Professional firms such as law and accounting
Next blog I will talk about which techniques are popular in New Zealand and how to learn them.
If you are interested in or have any problems with Business Intelligence, feel free to contact me.
After I completed my bachelor degree of Information Security in Xidian University and experience in Huawei, I start to look for a new challenge.
Then I came to New Zealand to study the Master of Computer and Information Sciences in AUT.
February 2018 – July 2018: Selected Courses
In the first semester of my master degree, I enrolled in two papers: Data Mining and Machine Learning, Natural Language Processing.
The two courses have assisted me in broadening my knowledge while showing me the diverse research side of Computer Sciences, especially Machine Learning and Artificial Intelligence.
July 2018 – December 2018: Master Thesis
Then I enrolled in the 120 points thesis in the second semester.
The thesis research of my Master degree is totally a different challenge to me.
It differs from previous teaching courses because its requirements of the abilities of critical thinking and problem-solving are much stricter.
Moreover, research is also an iterative procedure, so patience and persistence are also essential when it comes with persistent failure.
Luckily, I met my primary supervisor and second supervisor, who guided and helped me a lot.
In 14th November (my birthday day) last year, my primary supervisor suggested me to apply for the Summer Research Award at AUT, which can further augment to my capabilities.
Summer Research Award is awarded to students who have got satisfactory results in the three-month project in AUT. The evaluation is based on the final data analysis report. Only 10 students in the Faculty of Computer Sciences can get the award.
December 2018 – February 2019: Summer Research Award
Then I applied for it and my proposal got approved.
Through three months, I successfully classified and achieved a 92% accuracy result by building a neural network model in this project.
In the last week of February this year, I developed creative contents as academic reports with online LaTeX Editor (Overleaf) to get my final payment.
I recommend the Summer Research Award in AUT, because not only it can bring you some extra gains and honour, but also it can offer you an opportunity to gain some practical skills.
The most important thing is that I start to realise I am more interested in practical industry rather than academia!
Attachment is the Summer Research Award I gained.
February 2019 – July 2019: Two Publications
I also developed my research and analytical skills while publishing two papers
“An automated privacy information detection approach for protecting individual online social network users” to the Japanese Society of Artificial Intelligence (JSAI) journal
“Privacy Information Classification: A Hybrid Approach” to The 4th International Workshop on Smart Simulation and Modelling for Complex Systems (SSMCS 2019)
Maybe some people think I should take a PhD degree to enhance my research skills. However, I am not interested in academia any more.
New Journey:
It is time for me to continue my journey and gain more experience in the industry of Business Intelligence and Data Science industry!
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.
