Spreadsheet identifier (SSID)
each spreadshhet in an organisation be assigned a unique identifier
Busiess Intelligence Technologies
a type of advanced data analytics used to support business decision making.
Global spreadsheet query
Relational database or dimensional database
a dimensional database has one fact table as its center. A relational database does not have one central database table. Instead, it resembles an interlocking chain, linked together without a center.
How is data stored using BI technologies
Two types of database in the dimensional database?
1) fact table
2) dimension table
MS SQL server Relational Database
has one fact table & two or more dimension (DIM) tables
a referential constraint between two tables. A foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables.
Star & Snowflake
Two types of dimensional database structures. Both have one fact table at the centre in a star structure, the dimension database tables are all related onl to the central fact table, creating a star shape.
In a snowflake structure the dimension tables relate to the fact table like a snowflake. There is one inner circle of dimension tables & additional dimension tables are related to the inner dimension tables. At most, a query traverses only three tables.
includes data transferred from the operational database; can also include data from other sources, including shadow data; may contain duplication & inconsistencies.
To perform BI analytics, the data must be
- extracted from the relational database
- reorganised into dimensions
- transferred & loaded into data warehouse tables
Dimensional Database Star Structure
Difference between fact and dimension table
The word fact is added to the database fact table name. Dim is added to the dimension database table names.
a step-by-step solution to a problem
ETL for Loading DimSalesTerritory Table Using MS SQL Server
Fig7.24 pg 282
typically used on relatively unstructured data; can be used to mine text stored in various enterprise documents, including emails, word processing documents & reports.
searches the Internet for specific paterns on Web sites