Solved

How to sum over time period in MDX

Posted on 2008-10-29
3
2,750 Views
Last Modified: 2013-11-16
I got time dimension based on DateTime column in fact table. Now i'am trying to build MDX query to sum fact rows on custom time periods. For example:

SELECT [Events Count] ON Columns, [20 seconds time periods] on Rows FROM [Common]

Result i expect:

                                             Events Count
2008-10-29 17:22:00                     1
2008-10-29 17:22:20                     2
2008-10-29 17:22:40                     1
2008-10-29 17:23:00                     56

How to build MDX query?
regards
Kuba F.
0
Comment
Question by:kubaf
  • 2
3 Comments
 
LVL 8

Expert Comment

by:srnar
ID: 22838829
Use a new dimension with 60 members - one for each second. Map this dimension to your fact table. Then you can construct 3 custom sets e.g. {Second.[0]:Second.[19]}, {Second.[20]:Second.[39]}, {Second.[40]:Second.[59]} and put them on the row axes.
0
 

Author Comment

by:kubaf
ID: 22838856
srnar can you write sample MDX with sets? I'am kinda new in analysis services.
0
 
LVL 8

Accepted Solution

by:
srnar earned 500 total points
ID: 22838925
Here is example on Adventure works that creates virtal members based on sets and puts them on the rows.
with set Decade01Set

as

	{[Date].[Fiscal].[Date].[July 1, 2001]:[Date].[Fiscal].[Date].[July 10, 2001]}

set Decade02Set

as

	{[Date].[Fiscal].[Date].[July 11, 2001]:[Date].[Fiscal].[Date].[July 20, 2001]}	

member [Date].Fiscal.Decade01

as

	aggregate(Decade01Set)

member [Date].Fiscal.Decade02

as

	aggregate(Decade02Set)

select Measures.Defaultmember on columns,

{[Date].Fiscal.Decade01, [Date].Fiscal.Decade02} on rows

from [Adventure Works];

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now