There are several parts of my Business Intelligence journey.
So I write this blog for recording some cheat sheets.
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.
- Import data from different data sources
- Produce reports or graghs through SSIS, SSAS and SSRS
- Make business decisions
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
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
Some common commands:
ORDER BY CLAUSE
Other Joins, e.g., LEFT OUTER JOIN
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.
Originally published at http://jacquiwu.com on August 7, 2019.