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
Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Introduction
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
		Accuracy
		Significant KPIs
		On Time
		Company Analytics Life Cycle and Continuous Improvement
	Benefits of BI
		Direct Benefits
		Indirect Benefits
		Objectives
		Who Can Get Benefit from BI?
	BI Platform Components
		Source ERP
		Database
		ETL
		Front-End Tool
		Budgeting Tool
	BI Platform Location
	BI Concepts
		Datawarehouse
		DataMart
		Logical Model
			Relational Model
			Normalized Model
			Denormalized Model
			Star Model
			Snowflake Model
		Physical Model
		BI Main Objects
			Project
			Tables
			Facts
			Dimensions
			Reports
	BI Approaches
		Query and Reporting
		Information Sharing
		Dashboarding
		Data Import
		Data Discovery
		MOLAP
		Data Mining
		Incoming Approaches
	BI Capabilities
		Drilling
		Pivoting
		Visualizations
			Map Visualizations
		Sorting
		Grouping
		Filtering
		Conditional Expressions
		Subtotals
		Administration
		User Interface
	Conclusion
Chapter 2: Agile Methodologies for BI Projects
	Introduction to Agile Methodologies
	Agile Approaches
		Our Recommended Mix between Scrum and Kanban
		Developing Projects with Scrum
			Roles
				Product Owner
				Scrum Master
				Development Team
				Stakeholders
				Users
				Product Owner Assistant
			Sprint
			Specific Scrum Meetings
			Release
			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
				Spike
		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
		Trello
		JIRA Software
	Conclusion
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
		Sorting
		Filtering
			The Where Clause
			Operators
			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
			The SYSDATE, NOW(), CURRENT_DATE and GETDATE() Functions
			Adding Days to a Date
		Conditional Expressions
			The Case Expression
			The Decode() or IF() Expressions
	Conclusion
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
	Conclusion
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
	Conclusion
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
	Conclusion
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
			Partitioning Types
				Range Partitioning
				List Partitioning
				Partitioning Considerations
		Using the EXPLAIN Sentence
		Views and Materialized Views
		HINTS
		Denormalization
		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
	Conclusion
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
	Conclusion
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
	Calculations
	PowerOLAP
		Starting with PowerOLAP
		Creating Dimensions in PowerOLAP
		Cube Definition
		Slices
		Formulas
		Caching
		Data Import and Export
	Conclusion
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
	Conclusion
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
			Parameterizing
			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
		Security Management, Using Roles and Groups
		User Role Definition
		Security Matrix
	Auditing
		Security Audits
		Auditing Best Practices
	Conclusion
Chapter 12: Moving BI Processes to the Cloud
	Deciding our Cloud Provider
		Initial Considerations
			Sizing of the Platform: Performance and Scalability
			The Physical Design
			Security
			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
	Conclusion
Chapter 13: Conclusions and Next Steps
	Documentation
	BPM Software
	Big Data
Index
                        

Similer Documents