Dylan's BI Notes

Subscribe to Dylan's BI Notes feed Dylan's BI Notes
My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management
Updated: 15 hours 15 min ago

Incremental ETL : Streaming via Micro-Batch

Wed, 2017-10-11 23:40
A modern analytic application takes the approach of streaming data to perform the similar process as the traditional data warehousing incremental ETL. Actually, if we look into Spark Streaming in details, the concept of streaming in Spark and Incremental ETL are the same: Spark Streaming is a Micro-Batch based streaming. Each micro-patch is much like […]
Categories: BI & Warehousing

Incremental ETL – The last refresh date

Wed, 2017-10-11 15:31
There are multiple ways to model the last refresh date. In OBIA, DAC and Informatica based ETL, the last refresh date is maintained within DAC. It is maintained at the level of the source tables that populates the data. Oracle BI DAC User Guide > About Refresh Dates and DAC’s Incremental Load Strategy In OBIA […]
Categories: BI & Warehousing

Use Bit to represent groups

Wed, 2017-10-11 03:17
Here I am providing an alternate approach of supporting group membership in MySQL. It is a common seen requirement that a group may have multiple members and a person may be added to multiple groups.  This many to many relationship is typically modeled in an intersection table. When the group membership is being used as […]
Categories: BI & Warehousing

Schema On Read?

Sun, 2017-09-24 07:48
I saw “create external table ” first in Oracle DBMS 11G. It was created for the purpose of loading data. When Hive was introduced, a lot of data were already created in HDFS. Hive was introduced to provided the SQL interface on these data. Using the external table concept is a nature of the design.  […]
Categories: BI & Warehousing

Preserve Surrogate Key During Upgrade

Sat, 2017-09-23 07:00
The generated surrogate key is used everywhere in the data warehouse.  What do we do during upgrade? Here are some approaches: 1. Full Refresh You can perform a full refresh of the data warehouse.  The surrogate keys will be regenerated.  The FK will be updated. Obviously, this is not a good approach.  There are problems […]
Categories: BI & Warehousing

Unified Data Model or Not

Wed, 2017-09-13 17:07
Do we need to store the data all together in same places? Do we need to use the same data model ? Do we need to put data into cloud? Storing the data into a central place is not necessary, as nowadays, I do not really know where the data are stored.  If we talk […]
Categories: BI & Warehousing

How to – Incremental ETL

Wed, 2017-09-06 13:11
This is a very basic topic.  An ETL 101 question come up a lot in interview. Even we are moving to a different storage and different processing framework, the concepts are still important. The idea is simple – you do not need to keep extracting and updating all data in the data store that are […]
Categories: BI & Warehousing

Use Surrogate Key in Data Warehouse

Thu, 2017-08-31 07:46
Using surrogate key is part of dimensional modeling technique for populating a data warehouse using a relational database. The original idea was to generate the sequence generated IDs and use them in between the fact and dimension table, so we can avoid using the concatenated string or using composite key to join.  Also, due to […]
Categories: BI & Warehousing

Prebuilt BI Contents should replace BI Tools

Sun, 2017-08-13 09:03
Most school districts need the same kind of reports and dashboard for measuring the performance of students, teachers, and schools.   They do not really need to have IT to build reports for them if the vendors can provide the reports OOTB. There is really hardly a need to have a custom reporting tool for building […]
Categories: BI & Warehousing

Users of Analytics Applications

Sun, 2017-05-21 15:08
Business User who are consuming the data and the report.  They see the information pushed to them.  They can see alerts in their phone.  They see emails.  They add the page to a bookmark in their browser and periodically look at them.   They are executives, managers, busy users who have other duties.   They don’t […]
Categories: BI & Warehousing

Data Lake and Data Warehouse

Fri, 2017-04-07 11:23
This is an old topic but I learned more and come up more perspectives over time. Raw Data vs Clean Data Metadata What kind of services are required? Data as a Service Analytics as a Service Raw Data and Clean Data I think that assuming that you can use raw data directly in a dangerous thing. […]
Categories: BI & Warehousing

RTI and Actionable BI

Sat, 2017-04-01 15:59
RTI stands for Response to Intervention.  It is one the Data Driven Instruction methods. It is a way to monitor students’ progress and to determine what interventions students may need, assign the appropriate intervention to them, and monitor the progress closely. This is a good demonstration of the need of actionable BI.  The system collects the […]
Categories: BI & Warehousing

Analytics apps or embedded analytics

Thu, 2017-02-16 21:02
An analytics apps should be an application.   We should not see it as a report solution or just a way to view or download data. A best analytics apps is an apps, and lets people forget about it is an “analytics” apps. If you keep the end user in mind and drive the design of […]
Categories: BI & Warehousing

Dashboard vs Report

Thu, 2017-02-16 15:00
A dashboard is a data driven application.  It is important to think about: Who is using this?  User Persona.  Different people may have different level of accountabilities and thus have different interests. When the user will need to use? How often? What business process the information will need to be available? What is the best […]
Categories: BI & Warehousing

Schema-less or Schema On Demand

Sun, 2017-01-29 20:53
I am trying to define what are the criteria for a schema-less, or a schema on demand data storage. In relational database, we use DDL, data definition language, to define schema. We have to create table first, before we can insert data into a table.  When we update data, we update the data by mentioning the […]
Categories: BI & Warehousing

Uncommonly Common

Thu, 2017-01-26 17:41
An interesting concept. Significant Terms Aggregation – Elastic Search
Categories: BI & Warehousing

Elastic Stack

Wed, 2017-01-25 00:13
This post comes from notes I took while learning Elastic Stack. ELK is a combination of three tools: Elastic Search Logstash Kibana When ELK is changed to Elastic Stack, there is a forth tool: Beat There are a lot of information on the net.  I do not want to repeat anything.  I will just write my impressions. […]
Categories: BI & Warehousing

HR Reporting

Mon, 2017-01-16 10:05
HR is an important business function in any company.  It supports business growth.  It also satisfies the compliance requirements.  Reports are required for supporting the HR functions. Typical HR Reporting Requirements HR reports are surrounding the HR function:  Human Resource Planning, Recruitment and Selection, Orientation and Training, Performance Appraisal, Compensation and Benefit. They serves many […]
Categories: BI & Warehousing

How to access Fusion Cloud for building custom BI?

Tue, 2016-12-20 11:22
It was a challenge when we tried to build a BI application for Fusion Cloud application as Fusion Cloud applications, unlike those acquired solutions, such as RightNow, Elouque, and Taleo, do not have web services at that time. It was the reason why Oracle BI Application Configuration Manager was introduced.  It fills the gap by […]
Categories: BI & Warehousing

Use REST API to get the data

Sat, 2016-11-26 10:37
We now see more and more data are available in REST API.  Here are some of the data sources I worked earlier: ServiceNow Table API, REST API for Oracle Service Cloud, Here is a check list to enable a data source for BI: 1. How does the service perform the authentication? In the case of […]
Categories: BI & Warehousing

Pages