Data Warehousing and Business Intelligence

Finances and Customer Care to the Client by Operator Horizonte show different amounts of "active customers." Both consultations conduct correctly. The problem lies with the definitions, period and granularity used to produce the indicator.

Key concepts

A data warehouse organizes data for consistent and historic analysis. An approach lakehouse It combines analytical capabilities with data stored in open formats and different sources. Nobody guarantees that all consumers have an equal interpretation of data: agreed definitions, models and controls are required.

The grain indicates what a row represents. At Horizonte, a table may have an invoice row or an invoice line. The dimensions describe context as a client, plan and date. and facts they have events and measures. Mixing grains can double amounts even if SQL doesn't make mistakes.

The fact represents an invoice line: F-01 has a line of 60 and another of 40. The Client, Plan and Date dimensions provide context through their relationships. Declare the grain invoice + line before adding: each row represents a line, and does not represent a complete invoice or a different client. ↗

Semantics, Granularity and Publication

The indicator's owner agrees with definition, population, period, exclusions and use. "Active customers at closure" may have identities with at least one contract currently in place at the date of closure; "active contracts" counts contracts. A client with three contracts represents one at first and three at second.

The semantic cape expresses reusable measures and relationships. It should preserve unity, aggregation and treatment of missing values. A fee requires supported numerator and denominator. Promising percentages of different size segments can have an wrong result: their base quantity should be added as required by the definition.

At the September 30 cut, K-01, K-02 and K-03 contracts belong to the same C-17 client. Counting different customers production 1 To count different contracts production 3. Both measures may be right but they answer different questions. Define population, unit, period and exclusions before comparing indicators. ↗

Publish an indicator includes approval and reconciliation evidence. The consumer knows the date of update and the state: preliminary, closed or corrected. If the definition changes, the historic impact will be evaluated. A number that have already supported decisions are not replaced quietly.

Analytics Rebound with Oracle AI Data Platform and Oracle Autonomous AI Lakehouse

Prepare data with Oracle AI Data Platform and publish the tables that you will analyze with SQL with Oracle Autonomous AI Lakehouse. In Data Studio Oracle Autonomous AI Database data analysis, create a Analytical View about the fact table and its dimensions; Check the hierarchies, measurements and aggregations before publishing. The guide to creation of Analytical Views shows how to set up that model. At Horizonte, the measure should implement an approved definition of an active client and count different customers, even if they have several contracts.

In Oracle Analytics Cloud, the Oracle AI Data Platform connection for the catalogue that you are about to consume. create another connection if you need another catalogue. Use the Oracle Autonomous AI Lakehouse connection for information published in the database. Apply the same definition, period and granularity to the indicators that you will compare between both environments.

In this example, the approved definition counts each customer once if they have at least one active contract on 30 September. Oracle AI Data Platform prepares three active contracts for C7; the team publishes the data in Oracle Autonomous AI Lakehouse, where an Analytic View counts distinct customers. Oracle Analytics Cloud queries that indicator through a configured connection: C7 contributes 1 active customer, not 3. Publication and the connection require configuration and do not represent automatic synchronization. ↗

The federates have access to information at their location. a load copies and a cache retains a local representation to accelerate access. Updating, access and evidence should be evaluated at each modality. Data Sharing offers interchange mechanisms with different ranges: posting a version does not amount to promise continuous update.

When generating SQL with natural language, check measurements, filters and permissions before accept the result. It contrasts with the graph of the metric and reconciles a sample against its origin. Keep the grain, the definition and result of the reconciliation with the published version of the indicator.

Explanated example

A bill from 100 currency units has two lines. A analyst binds the header with the lines and adds the total header: gets 200 because the amount appears twice. The error comes from the relationship with grains.

Finance confirms that the indicator is invoiced and decides to add the line amounts, 60 and 40, or to add once every invoice to its grain. The engineer fixes the shared model and the steward document the measure. invoices with a line, several lines and matching notes are checked.

Following the join, F-01 appears once for each line. The header amount 100 is repeated and its sum production 200. The 60 and 40 line amounts maintain their grain and add up to the actual 100 total. Choose the measure that corresponds to the granularity of the result and conceive against the invoice of origin. ↗

The monthly figure is published after reconciliation with the authorized source and registration of the period. If an invoice is late, the agreed policy to tighten the closure will be applied and the earlier version will be retained as necessary to explain an already issued report.

Common

• "A valid consultation produces a correct metric." The technical validity does not check grain or definition.
• "All dashboards have the same truth." The model, filters, date and version of each consumer should be verified.
• "Federar means there's no copies." The design can incorporate cachés, extractions and persisting results.

Decision exercise

After we have published the September closure, a late invoice arrives. Sales wants to immediately replace the total Finance requires an explanation of what the directory saw. Decides how to publish the correction and what to preserve to avoid two results without context.