~2 Billion row fact table in star schema on Oracle. Advisable? Let me start with: I am not a DBA; I am a backend developer.

It has been suggested that, for a report schema, I implement a star schema where-in the fact table can have ~2B rows before being archived. ~1M rows a day will be added to said table. The information in the report can be no more than 15 minutes stale and page load time must be &lt; 7s

The report to be offered will be a sort of "summary-and-drill-down" where in when a selected summary portion is selected a subset of the rows that made up said summary will be displayed in another section Example:


I am inclined to move to a NoSQL store like Cassandra because I am really concerned about a fact table (and perhaps attribute tables) stretching to 2 Billion rows. Hoping someone here can shed some light on both the use of a star schema for this and how well I can expect Oracle (or any RDBMS, for that matter) to handle selecting and joining through a table with ~2B records.

Thanks
Row count isn't a great indicator of database size. I would not be worried about Oracle scaling to 2 billion rows. Relational databases can practically scale up into the many terabyte range. The ability to scale comes down to data model, hardware, and developer skill, user requirements, and budget. It's feasible to scale an Oracle (or SQL Server) data warehouse up into the 10s to 100s of terabytes.

Using a star schema is the right starting point for a relational data warehouse. There are a number of well thought out techniques for data warehousing outlined in The Data Warehouse Tookit. This will help you design a data warehouse that can scale.

At some point, you'll want to look into features that your vendor offers like partitioning, materialized views, and dimensional hierarchies. You should be able to put that off in your first attempt, though.