Solved

How to sum over time period in MDX

Posted on 2008-10-29
3
2,771 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

21 Experts available now in Live!

Get 1:1 Help Now