Using OLAP Cubes and a Star Schema to Aggregate Tick Data into Bars

Summary

The document discusses a lower-cost approach to storing and querying market tick and bar data when specialized financial databases are too expensive. The response suggests using an OLAP server with cube-based aggregation. It describes persisting ticks at their finest available granularity, then defining measures for open, high, low, and close with appropriate aggregation behavior. Dimensions such as time and instrument type can organize queries over those measures.

The proposed structure is a conventional star schema, with tick records as the underlying data and dimensions supporting analysis. This offers a conceptual route from raw observations to bars without storing every bar separately. The answer does not provide benchmarks, schema details, or a direct evaluation of SQL Server Analysis Services, the system asked about. Its recommendation is framed around another OLAP product, so performance, cost, and suitability for a particular workload remain unverified.

Key ideas

• The response proposes cube-based aggregation as one approach to querying tick data.
• Ticks can be stored at the finest granularity, with bar fields defined as measures using suitable aggregation rules.
• Time and instrument dimensions can organize queries in a star schema.
• The answer does not benchmark the approach or directly assess SQL Server Analysis Services.

Tags

# Should I use SSAS to store Tick and bar data?


# Should I use SSAS to store Tick and bar data?












I have been looking for a low cost solution to effectively store and query tick and bar data.

Databases like kdb+ and Streambase are too expensive for me.

Building a custom solution with SSAS (Sql Server Analysis Services) is one of the options that can think off. Has anyone attempted this?

If you did attempt this, how did you organize your data? Any ideas in that direction would be helpful.

## Answer by Marc Polizzi (score 1, accepted)

https://quant.stackexchange.com/a/4180

As you said, this sort of financial data can be well aggregated using cubes; icCube is for example a fast in-memory OLAP server you can access via XMLA clients or JAVA or Javascript native API (on top of the MDX language and HTTP protocol).

[edit after comment] open/close/high/low are types of aggregation supported by icCube; so creating this kind of 'bars' feature in icCube is a matter of defining several measures (with their own aggregation type) from the tick data (lowest granularity and only one to be persisted). Then around these measures several dimensions can be built (time, financial instrument types, etc...). Quite a classic star schema actually with no specific requirements.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.