Analysis Service - Design and Architecture

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Analysis Service - Design and Architecture

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2011-12-22 : 08:13:57

I have star schema like structure for my database for stock finacial application. I want to know if there is a better way to acheive the same and some clarifications.

dbo.Company (Fact Table)
------------
CompanyID
CompanyName
DateCreated

There will be a table for every entry in the Company table. Which will have the below outlined fields.

dbo.Axis
-----------------
EntryID
CompanyID
Price
Time
Volume
DateTime

I have around 8 companies so I will have 8 more tables with the fields displayed above.

Based on the price field above I want to calculations Multidimensional Expressions (MDX) for calculating
1. Moving Averages
2. MACD
3. ADX
4. RSI

How can I write the MDX for above calculations?

How would be my Cube like. What will be dimensions and measures.

Please guide.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-22 : 08:39:13

Why will you have 8 more tables? That doesn't make sense as you are splitting by CompanyID anyway.

What will the averages be on? If you put the price and date (time) fields into your fact table, your calculations will be easier and these are naturally your facts. Your dimensions are things like the companyname, datecreated, address details etc..

Here is a good place to start with MDX calculations:

http://sqlserverpedia.com/wiki/MDX_with_Analysis_Services

mapidea
Posting Yak Master

124 Posts

Posted - 2011-12-22 : 10:36:56

There will be millions of rows in each of the 8 company tables about the time and price. It will be like star schema.

Fact Table - Company
Measures - Price Date Volume
Dimensions - Company Name
Calculations - Moving Average, ADX, MACD etc

Please let me know if you still think this would be wrong

mapidea
Posting Yak Master

124 Posts

Posted - 2011-12-23 : 05:57:14

Correction Date should be a Dimension.

Fact Table - Company
Measures - Price, Volume
Dimensions - Company Name, Date
Calculations - Moving Average, ADX, MACD etc

Please let me know your thoughts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources