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
SQL Language:
Learning sources:
Some common commands:
One table:
SELECT…FROM
WHERE CLAUSE
ORDER BY CLAUSE
Two tables:
Inner Join
Other Joins, e.g., LEFT OUTER JOIN
GROUP BY
If you are interested in or have any problems with Business Intellgence, feel free to contact me .
Or you can connect with me through my LinkedIn.
