Download Business Intelligence Tools for Small Companies: A Guide to Free and Low-Cost Solutions PDF

TitleBusiness Intelligence Tools for Small Companies: A Guide to Free and Low-Cost Solutions
File Size10.9 MB
Total Pages340
Table of Contents
                            Contents at a Glance
About the Authors
About the Technical Reviewer
Chapter 1: Business Intelligence for Everybody
	What Is Business Intelligence?
		BI Evolution
			From Strategic to Tactical
			Big Data
			Internet of Things
	BI Characteristics
		Hidden Relevant Information
		Significant KPIs
		On Time
		Company Analytics Life Cycle and Continuous Improvement
	Benefits of BI
		Direct Benefits
		Indirect Benefits
		Who Can Get Benefit from BI?
	BI Platform Components
		Source ERP
		Front-End Tool
		Budgeting Tool
	BI Platform Location
	BI Concepts
		Logical Model
			Relational Model
			Normalized Model
			Denormalized Model
			Star Model
			Snowflake Model
		Physical Model
		BI Main Objects
	BI Approaches
		Query and Reporting
		Information Sharing
		Data Import
		Data Discovery
		Data Mining
		Incoming Approaches
	BI Capabilities
			Map Visualizations
		Conditional Expressions
		User Interface
Chapter 2: Agile Methodologies for BI Projects
	Introduction to Agile Methodologies
	Agile Approaches
		Our Recommended Mix between Scrum and Kanban
		Developing Projects with Scrum
				Product Owner
				Scrum Master
				Development Team
				Product Owner Assistant
			Specific Scrum Meetings
			Artifacts Used in Scrum
				User Story
				Developer Story
				Product Backlog
				Definition of Done
				When to Start - Ready Definition
				Product Backlog Burndown Chart
				Sprint Backlog
				Sprint Backlog Burndown Chart
				Impediment List
				Improvement List
				Limiting Spent Time with Timeboxing
		Maintenance with Kanban
			Kanban Concepts
				Task Workflow
				Task Board and Visual Management
				Work In Progress
				Lead Time
		Mix of Both Methodologies, Scrumban
	Peculiarities of Scrum for BI
		Sprint 0 - Longer Initial Analysis
		BI Project Segmentation
		Front-End vs. Back-End Activities
		Role Isolation - Specific Knowledge
		Developer Story Types in BI
			Data Modeling Developer Stories
			ETL Developer Stories
			BI Model Developer Stories
			Report and Dashboarding Developer Stories
			MOLAP Developer Stories
	Agile Management Tools
		JIRA Software
Chapter 3: SQL Basics
	What Is SQL?
	The Relational Model
	Databases and Database Vendors
		ACID Compliance
		Types of SQL Statements
		SQL Datatypes
			Numeric Datatypes
			Text Datatypes
			Date Datatypes
			Other Datatypes
		Retrieving Data from a Table
			The select * Statement
			The select column Statement
			The select count (*) or count (column) Statement
			The select distinct Clause
			The Where Clause
			Logical Operators
		Grouping Data
		Using Subqueries
		Joining Tables
			Types of Joins
				Cartesian Join
				Inner Join
				Outer Join
		Set Operations
			Union and Union All Operators
			The Intersect Operator
			The Minus Operator
		Working with Dates
			Adding Days to a Date
		Conditional Expressions
			The Case Expression
			The Decode() or IF() Expressions
Chapter 4: Project Initialization – Database and Source ERP Installation
	The Need for Data
	Setting the System Up with Odoo ERP
		The Bitnami Odoo Package
		Downloading and Installing Odoo
		Bitnami and Odoo Configuration Files
		Installing psql and Checking the Database Connection
		Accessing the Application
		Configuring and Installing Modules
		Selecting our Datawarehouse Database
		Sourcing and Installing MariaDB
			Windows Installation
			Linux Installation
				Installation in Ubuntu
				Installation of MariaDB in centos
				Solving Connectivity Issues
		Creating Our First Database
			Show Databases and Tables Commands
			Creating Users for the Two New Databases
			Grant Permission to Databases
	Analyzing the Data Source
		Inspecting our Model
		Setting up a PostgreSQL Connection to the Server
Chapter 5: Data Modeling for BI Solutions
	Naming Convention and Nomenclature
	Modeling Steps
		Business Model
		Logical Model
		Dimensional Model
		Physical Model
			Data Load Strategies
	Defining our Model
		Sales Dimension
		Status Table
		Currency Dimension
		Customer Dimension
		Employee Dimension
		Product Dimension
		Time Dimension
	Exploring Data Modeling Possibilities
		View Layer
			Materialized Views
		Data Split
		Fact Normalization and Denormalization
		Time Transformations
		Fact Phases
		Real vs. Potential
		Historical Hierarchies
		Multiple Views Using a Table, Entity Isolation
		Modifying Existing Structures, Adding More Sales Document Status
		Currency Transformation
		Geographical Information
	Data Modeling Tools
		Erwin DataModeler
		MySQL Workbench
	Preparing the ETL
		Source Systems
		Source Tables
		Source Fields
Chapter 6: ETL Basics
	Why Do We Need an ETL Process?
	Details of the Solution
	Open Source ETL Suites
	Downloading and Installing Pentaho Data Integration
	Understanding ETL Concepts
		Repositories and Connections
		Transformations, the Core of Kettle
		Jobs or How to Organize a Set of Transformations in a Workflow
		Create and Share a Connection
		The Global Picture
		The Product, Product Category, and Product Parent Category Tables
		The Customer and Customer Country Tables
		The Employee and Employee Category and Employee Department Tables
		The Fact Table: How to Create the Transformation for the Sales
		Creating the Time Dimension
			Generate as Many Days as We Need to Store
			Using the Calculator to Compute Some Other Fields
			Calculating More Complex Fields and Dropping Unneeded Ones
			Doing Advanced Manipulations
				The formula step
				The Modified JavaScript Value step
				Fixing a few things
				The last step
		Connecting All of It Up
			The Time Tables
			The Product Tables
			The Employee and Customer Tables
			The Sales Table: t_f_sales
		Designing the Job
		Open Source Alternatives to PDI
Chapter 7: Performance Improvements
	Database Optimizations
		Avoid Using Dates for Join Keys (and Primary Keys Too)
		Analyze the Tables in Your Database
		Indexing, or How to Speed Up Queries
			Standard or B+ TREE Indexes
			Bitmap Indexes
			FULLTEXT Indexes
			Partitioning Types
				Range Partitioning
				List Partitioning
				Partitioning Considerations
		Using the EXPLAIN Sentence
		Views and Materialized Views
		Disabling Triggers and Constraints
	ETL Optimizations
		Offloading Operations to the Database
		Check the Network Links
		Performance Tips with PDI
			Increase the Number of Rows in the Rowset
			Parallelize Transformations
			Bulk Loads and Batch Updates
			Ordering in Merge Operations
Chapter 8: The BI Reporting Interface
	How to Choose the BI Tool
	Best Practices in Dashboarding
		Starting from Top Left
		Joining Related Information
		Focus on Relevant Data
		Formatting Recommendations
			Corporative Integration
			Related Data Alignment
			Conditional Formatting
			Intensity vs. Color
			Visibility of Data
		Graphic Usages
			Pie Chart – Percentage Contribution for a Few Values
			Stacked Bar Graph – Percentage Comparison
			Horizontal Bars – Top N Elements Comparison
			Vertical Lines, Bars, or Areas – Time Evolution
			Vertical Bars – Histogram
			Bubble Chart – Correlation
	BI Tools
		Microstrategy Desktop
		Microsoft Power BI
		Qlik Sense
Chapter 9: MOLAP Tools for Budgeting
	Multidimensional Databases
		Dimensions or Axes for Analysis
		Dimension Members
		Sharing Properties across Cubes
	MDX Language
	MOLAP Data Interfaces
		Data Import
		Source Data Preparation
		Data Export
		Starting with PowerOLAP
		Creating Dimensions in PowerOLAP
		Cube Definition
		Data Import and Export
Chapter 10: BI Process Scheduling: How to Orchestrate and Update Running Processes
	Finishing the ETL
		Creating the Job in PDI
	Overview at PDI Command-Line Tools
		Launching Transformations from the Command Line with Pan
		Launching Jobs from the Command Line with Kitchen
	Scheduling Jobs in the Task Scheduler
		Scheduling a PDI Job in Windows
		Scheduling a PDI Job in Unix/Linux
	Running Database Maintenance Tasks
		Backing Up Our MySQL/Maria DB Database
		Perform Checks and Optimizations in the Database
Chapter 11: Moving to a Production Environment
	Multienvironment Scenario
		Deploying a Productive Environment
			Server Capacity Plan
				Database server capacity planning
				ETL server capacity planning
				BI server capacity planning
				MOLAP server capacity planning
			Licensing and Support Costs
		Adding Environments
		Isolation of Environments
		Multienvironment Recommendations
			Server Paths
			Naming Alignment for Connectivity Objects
	Maintaining Your Environment
		Object Life Cycle
			Transport Procedure
				Transport windows
			Transport Automation
			Testing Procedure
				Test automation
		Monitoring Tools
		High Availability
		Using Testing as Disaster Recovery of Production
		Backing Up Your Platform
		Historification Process
		Security Management, Using Roles and Groups
		User Role Definition
		Security Matrix
		Security Audits
		Auditing Best Practices
Chapter 12: Moving BI Processes to the Cloud
	Deciding our Cloud Provider
		Initial Considerations
			Sizing of the Platform: Performance and Scalability
			The Physical Design
			Data Location
			Health Checks
			SLA and Support Plans
			Calculate the Running Cost of the Platform
		A First Look at the Physical Design
	Choosing the Right Cloud Provider
		Amazon Web Services (AWS)
			Implementing the dev Environment in AWS
				Launching an EC2 instance
				Connecting to our EC2 instance
				Launching a RDS Database
			Calculating Prices with the AWS Calculator
			Having a Go with AWS Quicksight
			Using Software from the AWS Marketplace
		Microsoft Azure
			Creating a Machine in Azure
			Creating a Database in Azure
		Google Cloud
		Vendor-Based Cloud Solutions
Chapter 13: Conclusions and Next Steps
	BPM Software
	Big Data

Similer Documents