Can I use Analysis Services/MDX to do this?

 

i have 2 tables between there one-to-many relationship. simplified structure follows:

 

code snippet

master

 

id   keywordid   url   isactive

 

detail

 

detailid   masterid   capturedatetime

 

 

i want slice according datetime, , produce resultset mdx incorporating count of detail rows each master row child rows' capturedatetime fall within specified datetime range.

 

in addition, should have fields such detailidstart , detailidend (the min , max detailid columns given masterid in date range) , startdatetime , enddatetime (the min , max capturedatetime given masterid in date range):

 

code snippet

masterid   keywordid   url   count   detailidstart  detailidend    startdatetime     enddatetime   isactive

 

 

previously in usage of analysis services, have had measures based on fact tables, sliced these according dimensions. how fields such masterid, keywordid , url returned? these been dimensions opposed measures, since measures seem confined being aggregates (or semi-additive measures, don't have access in edition of analysis services)

 

is possible structure fact , dimension tables allow type of query in mdx?

 

thanks

 

 

one approach comes mind set both measure groups , associated fact dimensions on master , detail tables. master dimension have aregular relation detail measure group, , detail dimension have many-to-many relation master measure group, via detail intermediate measure group.

 

the capturetime dimension have regular relation detail measure group , many-to-many relation master measure group.

 

assuming there appropriate member values configured detailid , datetime attributes, calculated measures created return first , last member values in natural order, lowest , highest. like:

 

code snippet

with

member [measures].[detailidstart] as

head(nonempty([detail].[detailid].[detailid], {[measures].[mastercount]})).membervalue

member [measures].[detailidend] as

tail(nonempty([detail].[detailid].[detailid], {[measures].[mastercount]})).membervalue

member [measures].[startdatetime] as

head(nonempty([capturetime].[datetime].[datetime], {[measures].[mastercount]})).membervalue

member [measures].[enddatetime] as

tail(nonempty([capturetime].[datetime].[datetime], {[measures].[mastercount]})).membervalue

...

 

 

SQL Server  >  SQL Server Analysis Services