OLAP Cube design question

[ueberbill]

Hello all,

I have a single database table I've been trying to design a cube for and I'm kinda stuck. I have flight types (commercial, military, etc), engine types (piston, jet, etc), departure airport names, and departure dates. Each entry in the table is one flight. What I need to be able to do is query this cube to pull back a row like so

Month | Year | Commercial Flight | Jet | Airport Name | # Flights that fit the previous criteria.

Any thoughts on an optimal cube to allow that kind of analysis and the queries to pull it?

Bill

 

[RiverGuy]

Its a pretty typical star schema scenario. The best design would be to also follow a dimensional modeling approach in the relational database. Instead of having one flat table, break down your individual dimensions and fact tables.

That being said, what you need in the SSAS database are the following:

Date Dimension,
FlightType Dimension,
EngineType Dimension,
Airport Dimension,
Flight Fact Table.

With that design, you could easily obtain the results you are after with a hand-coded MDX query, or an OLAP browser/reporting tool.