Knowledge Hub

As discussed in topic i.e., Data Warehouse or Data Mart that Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach. But what about Galaxy Schema?

You must be wondering that we have been hearing about Facts and Dimensions in both Star and Snowflake schema(s) but where are Conformed Dimensions, so here it is.

Galaxy Schema is also known as Fact Constellation Schema and is more complex than Star and Snowflake schema. It’s further divide Star Schema into small Star Schema(s) where there are more than one Fact Table and have Reusable Dimension to connect multiple Fact Tables.

These dimensions which can connect with multiple Fact tables across multiple Star Schema(s) are known as Conformed Dimensions.

Cubes are a very controversial element of OLAP world where most of the folks think, it not worth having those.

Cubes host data from facts and dimensions in one table so query can be super-fast, in Cubes multi-dimensional data is pre-loaded.

Disadvantages

• These are huge in size.
• Very difficult to refresh in real time.
• Housekeeping is a challenge.

Below is Star or Snowflake schema representation. When data is joined from Fact and Dimension and loaded on one table it is called an OLAP Cube.

There are multiple types of OLAP i.e.  ROLAP, MOLAP, HOLAP, HTAP, WOLAP, DOLAP and SOLAP.

• ROLAP [Relational On-Line Analytical Processing]: It sits directly on top of RDBMS which can be Star/ Snowflake Schema(s) and can be built using standard SQL. Another benefit, ROLAP doesn’t need additional storage as it runs on top of RDBMS.
• MOLAP [Multi-dimensional OLAP]: Cubes are the best example of MOLAP where multi-dimensional data can be loaded in a separate storage but in an extremely fast manner. Using Cubes, users can slice, dice, and drill up/ down, roll up and pivot the data within the same one cube eliminating the need to join with other tables. If a Cube has more than 3 dimensions of data in it, it’s called Hypercube.
• HOLAP [Hybrid]: This is a mixture of ROLAP or MOLAP.
• HTAP [Hybrid Transaction / Analytical Processing]: This is for in-memory data systems that do both online transaction processing (OLTP) and online analytical processing (OLAP).
• WOLAP [Web-enabled]: This refers to the only Web-Based OLAP with no connectivity with on-premises and is utilized through the internet browser.
• DOLAP [Desktop On-Line Analytical Processing]: In this OLAP, users can download a portion of OLAP on their machine for offline working.
• SOLAP [Spatial]: This is to tackle issues in the field of Geo-Business Intelligence.

Data Rooms or Datastore are both the same entity. Data Rooms are normally used for due diligence of confidential documents like handling Business Mergers or Acquisitions by bidders & advisers, compliance & audit teams.

Traditionally, there are Physical Data Rooms with Physical Documents. These Physical Data Rooms are for restricted people only, secured by guards with a maximum of one or two parties to go in at any given point-in-time which delays the process if multiple parties are using the same Physical Data Room.

With the growing businesses globally, it was a very expensive affair for multiple teams to fly over to be near these Physical Data Rooms. As an alternate and more viable solution, VDRs (Virtual Data Room) came into the picture which are available Online, behind Firewalls and with proper Access Control. VDRs have changed the game, now sellers can invite a bigger audience and teams sitting in one part of the world to participate in a bid process happening in the other part of the world. VDRs have cut down the cost and expedited the process.

Hadoop and its relationship with Big Data. The Big Data ecosystem has many components like NoSQL databases, In-Memory process, MPP, 3 Vs etc. but Hadoop is the backbone, and it was the main reason for the popularity of Big Data. In other words, Hadoop was the Selling Pitch for Salespeople.

'Hadoop is a Java-based framework.' Currently, its latest version is Apache Hadoop 3.3.4.’

Please note, Hadoop is not a solution, it's again an EMPTY storage. One must load data first just like we do in traditional databases before any analytics can be generated. For the first 5-7 years, Sales keep selling Big Data in such a way that you buy Big Data, and it will do magic for you, where all kinds of Analytics will be readily available but no it didn't happen, and you already bought it lolz. Learning: For every new technology, first hire a specialist internally and then invest 😊.

Let's go a little deeper.

Obvious question, difference between Hadoop, HDFS and Hive?

Hadoop is an Open-Source Framework to store structured, semi/unstructured data. Default block size of 128MB (changeable).

HDFS is a Hadoop Files System just like FAT, NTFS, EXT etc.

Hive is only a wrapper on top of files stored in HDFS. Hive behaves just like tables in traditional databases, but it doesn't store any data, it just manages table structures on top of HDFS files. There are 2 ways Hive works, either store files in HDFS directly or create tables which will create HDFS files at the back end. The difference in these approaches is 1) each file stored in HDFS creates its own block so if there are 2 small files e.g., 50 MB each, then there will be 2 blocks created. 2) on the other hand, Hive manages its own blocks so if there are 2 small files of e.g., 50 MB each, then Hive will store both in a single block.

Consider 1000s of 1MB files float in from social media means 1000s of 1MB small blocks created impacting MPP performance. The best practice is to have blocks of 128MB.

Dormant Data and Dark Data are mostly confused with each other.

Dark Data

‘Data that is not utilized is Dark Data.’

Dark Data is data that is not in use due to lack of technology and is not a Data Asset. Dormant Data is the data, which is useable, but decision-makers don't use it, or it’s not required for analysis but that doesn't mean you can delete it or purge it, so we keep it in Tape drives, in Hadoop and nowadays Object Storage is also good choice.

Before the introduction of Big Data, the % age of Dark Data was around 90% as it included semi and unstructured datasets and sometimes structured as well. For example, JSON or XML files, Video or Audio files, Logs, Metadata etc., all these datasets were there but technologies were either not there or the affair to analyze those were very expensive. The only reason companies kept this kind of data was for Compliance or Audit purposes. Since the Big Data ecosystem has come onto the surface and hardware has become dead cheap so hosting and processing these datasets has become possible.

As we discussed in topic i.e., Dark Data, that it is utmost important for a CDO (Chief Data Officer) to minimize Dark Data as much as possible, in the organization. In other words, the more the Dark Data, the more the cost without any ROI. Time is not far when Dark Data Management will become the most essential and obvious KPI for CDO (Chief Data Officer).

Now the questions are.

• Is all Dark Data bad? NO.
• Is all Dark Data delete able or purge able? Yes and No.

‘Data Detritus means that data, which is no more required, which is of no use anymore etc.’

• Dark data is something which was and is, not in-use.
• Dormant data is something which is hardly in-use.
• Detritus data is something which was in-use but will not be in-use, anymore.

In other words, Detritus data was once valuable before it became Dormant, as compared to traditional Dark Data (explained separately) which was never in-use due to technologies barriers etc. Detritus data is normally not deleted or purged as it can still be required for Regulatory or Compliance parties.

Data Dividend is something everyone must know either IT Professional or End User.

We all know what a Dividend is in the Stock Market. It's when a user buys a Stock of a particular company and keeps it for a certain time, the company pays the user a certain amount in Quarterly or Annual basis. The reason is the company is keeping users' money for a particular duration. At the backend, the company is investing that money in its business to generate revenue. Then a certain percentage of that revenue is shared with the user in the shape of a Dividend.

This era is to generate customer data either via mobile applications or via social media portals like Facebook, Twitter, Instagram, Amazon, Alibaba, etc. Users use these applications and, in a way, give their DATA instead of MONEY like their buying/ selling behavior, their search trend, their family/ friends interactions, etc. to above-mentioned companies which then use that DATA for marketing & advertisement, for selling services, to production houses, to industries, to inventory houses, etc. In this cycle, all these companies are making Fortunes, and nothing has been shared with the ENTITY i.e., end-users who are the core participants for the data generation.

Data Liabilities is not handled properly and can severely damage the value of Data Assets.

If Data Liabilities overcome Data Assets in an organization, it can create negative impact of data value.

In-contrast with Data Assets, if Data hosted in your databases, data warehouse, data marts, data lake, data vault, business vault etc. are not utilized properly with no proper ROI and at the same it can’t be used for revenue generation, nor for management decision making then it becomes a liability for your organization. On other words, you are paying for something and not getting anything.

Data Liabilities can also become the reason for Data Loss and failure to ensure proper handling of data usage, data sharing, data quality and data integrity.

One of the major KPI for any CDO should be to reduce data liabilities as much as possible.

Not to mention, hosting and processing data is not a cheap affair plus if management or users can’t utilize it then it’s not worth storing it.

We all have heard about Mean and Median values but not Spread.

Before jumping into Spread, let's have a quick definition of Mean and Median which will assist in understanding Spread.

• Mean: The mean (informally, the “average “) is found by adding all the numbers together and dividing by the number of items in the set: (10 + 10 + 20 + 40 + 70) / 5 = 30.
• Median: The median is found by ordering the set from lowest to highest and finding the exact middle. The median is just the middle number: 20.
• Spread: The simple explanation of Spread is e.g., how variable the data is or how spread out the data values are in the dataset, or it is also referred to as a measure of dispersion etc., from the Mean or Median values.

Let’s try to understand with an example. Let’s say we have a dataset with the customers’ age from 20 to 60.

Breakdown

• 8 customers with Age [10]
• 15 customers with Age [20]
• 12 customers with Age [30]
• 7 customers with Age [40]
• 3 customers with Age [60]