Querying OLAP cubes 3

[joelwenzel]

Hi,

I am new to star schema's a cubes. I'm making reports based on a star schema. When I query, do I query the sql server database like

SELECT factvalue FROM FACT JOIN DIM_TIME ON DIM_TIME.year= 1999 AND FACT.timeid= DIM_TIME.timeid

Or do I somehow just query analysis services?

 

[MDXer]

if you are using Cubes then your queries don't utilize T-SQL or even any SQL which would look like:

Select
d1.DimMember,
d2.DimMember,
f.FactValue
From Fact f
   JOIN dim1 d1 ON d1.sk = f.sk
   JOIN dim2 d2 ON d2.sk = f.sk

But rather utilize MDX which basically looks like the following.

SELECT
CROSSJOIN({Dim1.Level1.Members},{Dim2.Level3.Members}) On Columns,
Measures.FactValue On Rows
From MyCube
Where DateDimension.Year.@005

This is not to say that you can't report using SQL directly against your tables using T-SQL which you could do and would be considered ROLAP , which is different than a rolap cube which would still use MDX. Also you can use query analyzer to query your cubes using OPENQUERY but again using MDX. There are a few MDX query tools available a couple even allow drag and drop query development or AS ships with the MDX sample app which is a bare bones MDX query tool.

If you have never used MDX it can be a real pain to get used to or even understand the basics. I recommend the Book by George Spofford which unfortunately I don't remember the name of or have with me at the time.




Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[joelwenzel]

I will look it up. Is there any reason taht I should use MDX then? I'm assuming it is faster then a straight TSQL query, right?

 

[MDXer]

If your going against a cube the answer would be that T-SQL can't query a cube. if it is Cube versus underlying datasource then yes it will be much faster.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[joelwenzel]

ok, I thought a cube was just a conceptual thing. I figured if I had a bunch of dimension and a fact table then it pretty much is a cube. I thought analysis services just puts the queries together and creates a nice interface but that the exact same results could be achieved through TSQL.

 

[MDXer]

Analysis services preaggregates the underlying fact data which is what makes it faster than SQL. AS has 3 storage mode MOLAP, ROLAP and HOLAP with MOLAP being the fastest. MOLAP is the fastest because the fact data is built into a File set that is seperate from the underlying DB. with MOLAP cubes you can actually take you DB server offline after the cubes have built without any impact to your cubes unless you utilize drillthrough. Rolap and Holap require access to the underlying DB. Other performance enhancemets you canapply in AS is preaggregations. AS by default aggregates all data to the Leaf level of the dimensional intersections. Preaggregations allow you to preaggregate higher up the dimensional structure in theory you could apply enough agregations that would make it where the cube was never actually queried. AS also allows for user defined functions which you can target queries which run longer than X or have been ran more than x times. you can preaggregate all queries excuted by a set user (nice way to keep the Cs and Vs happy), you can also apply preaggregations to queries that run longer than X seconds. Preaggregations allow you specify the percentage of performance gain 0-100% or allow you to set a max number of agg storage space utilized. Cubes are bery powerful datasets that can enable the user to do very basic or extremely complex analysis. AS in it's current structure is essentially a design and administration tool and has no real reporting piece. Reporting services allows for reports to be built but lacks an ad hoc query tool which is the true strength of the cube. The cube structure allows the user to do Analysis at the Speed of thought meaning answers typically appear as fast as the user drags and drops dimensions also called slicing and dicing.

Hope this has cleared up some of your questions.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[blom0344]

MDX-er,

My compliments to your reply. It packs a maximum of much-needed info for us beginners!!
Here's a star...

Ties Blom
Information analyst

 

[joelwenzel]

make it 2

 

[wwwSQLOBSERVERcom]

Just as an appendix to MDXer's excellent post - ROLAP is relational and HOLAP is a hybrid (combo of both MOLAP multidimensional and ROLAP).

There is also DOLAP (just in case you were running out of things to think about...)

http://www.SQLobSERVER.com

the Microsoft BI/DW community