Miscellaneous questions 1

[flicg]

These are my outstanding questions, that is to say they are the statements that I think are probably wrong. Please can you help me to correct them?

True or False? If false – please say why!

1. An ODS stores data in non-relational format
2. Fact tables and dimension tables exist at the following levels: ODS, data warehouse, datamart
3. There is no difference between fact tables and reference tables.
4. There is no difference between dimension tables and dimensions
5. Like fact tables, dimensions are distinguished one from another by business process.
6. De-duplication happens at the datawarehouse layer , not the ODS.
7. In a datamart data is not stored in relational format.
8. The OLAP layer can only interface to a non-relational data structure like the datamart. It can’t interface to the data warehouse.
9. HOLAP is always the best of the OLAP options (MOLAP / ROLAP)
10. There is no difference between a BI tool’s ‘repository reports’ and ‘cube reports’.
11. There are no tools to automate the UAT phase of reports designed by a data warehouse because they would have to check data in reports by the same methods used by the OLAP layer to generate them.

 

[johnherman]

1. An ODS stores data in non-relational format.
FALSE - It depends. An ODS has many purposes, some of which are better accomplished using relational structures.

2. Fact tables and dimension tables exist at the following levels: ODS, data warehouse, datamart
TRUE - and also in OLAP structures and sometimes Data Mining and other Decision Support structures.

3. There is no difference between fact tables and reference tables.
WELL...where I come from, a reference table is like a "code" or "lookup" table, a set of codes and values with, perhaps, some attributes associated with them. Where I come from, reference tables change slowly, if at all. Examples are State and Zip code tables, Standard Industry Codes (SICs), medical Diagnosis and Procedure codes, etc.

4. There is no difference between dimension tables and dimensions.
TRUE - but keep in mind that there could be several dimension tables supporting one dimension. Geography could be supported by Zip Code, County, and State tables, for instance.

5. Like fact tables, dimensions are distinguished one from another by business process.
Dimensions are qualitative entities or attributes by which business success is measured. Facts are quantitative attributes measuring business success. Facts are evaluated across dimensions. So you might have profit by region by product by time. If you can preface the entity or attribute with "by", it's likely a dimension.

6. De-duplication happens at the datawarehouse layer , not the ODS.
Deduplication occurs where it is needed and/or easiest. Do not let a set of generalized rules such as these drive your business. The business drives the Data Warehouse design, not vice-versa.

7. In a datamart data is not stored in relational format.
USUALLY True.

8. The OLAP layer can only interface to a non-relational data structure like the datamart. It can’t interface to the data warehouse.
FALSE. Many OLAP building processes can read relational data and drill down to relational detail sources.

9. HOLAP is always the best of the OLAP options (MOLAP / ROLAP)
It depends. MOLAP gives the best performance but costs disk space and processing time to build. ROLAP is best for real-time and near real-time data warehousing. HOLAP is an in between solution, saving some disk and processing time, etc.

10. There is no difference between a BI tool’s ‘repository reports’ and ‘cube reports’.
A report is a report is a report. Once built, it's a static, usually 2-3 dimensional representation of a multidimensional world.

11. There are no tools to automate the UAT phase of reports designed by a data warehouse because they would have to check data in reports by the same methods used by the OLAP layer to generate them.
Not sure what you mean by this. Reports are a static view of the data. They can be filtered (WHERE clause), sorted, subtotaled, etc. Reports are subject to error just as cube building processes are subject to error. Since these softwares are usually commercial third-party tools, they are usually well tested, so the error is usually in the user's interpretation of data and its meaning.


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[flicg]

Thanks for the great info! Felicity