question

greglipman-4888 avatar image
0 Votes"
greglipman-4888 asked greglipman-4888 commented

multiple fact tables vs lookupcube

suppose i have policies for each policy i have transactions - premium payments each has transaction date and for each policy i have claims and for each claim i have claim payments if i try to compare premium against claim payments for a given set of policies - no problem i just filter on policy start date and of course that will give me claims measures for that slice of policies The problem starts when i want to compare premium for one transaction date interval with claim payments for a DIFFERENT period say 6 months later e.g. premium on policy coming in March but claim payments on policy paid in Sept is it where one uses multiple fact tables with shared dimensions? example schema: transaction date (key) policy_number (key) zip_code (key) mileage (key) premium amount (measure) and payment date policy number claim number zip code mileage payment amount (measure) so i need to do sum of premiums for a given parameterized policy in March and sum of payments for claims for that policy's claims in Sept I tried lookupcube against claims separate cube it is sloooooow and stops working all the time

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered

Hi,

Are you looking for LookupCube performance tuning or are you wanting to change the methods ?What is your currenct problem now ?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

greglipman-4888 avatar image
0 Votes"
greglipman-4888 answered greglipman-4888 commented

Hi Lukas
the performance kills me
and when i un-hook that second cube it flies on 2 mil records in main fact table/cube

basically i have two cubes (fact tables)
one main (premium) and second with one more measure (claims)
they share lots of conformed dimensions
but the second one is also filtered on its own parameter date

so I hook them together with lookup cube for claims
mdx is below

WITH MEMBER [Measures].[Incurred] AS
LookupCube('claims',
'
Sum(
NonEmpty(


Filter( [Dol Time].[Full Date].AllMembers,
[Dol Time].[Full Date].CurrentMember.MemberValue > CDate(''1/1/2000'')
and [Dol Time].[Full Date].CurrentMember.MemberValue < CDate(''1/1/2200'')
) *

Filter( [Pol Eff Date].[Full Date].AllMembers,
[Pol Eff Date].[Full Date].CurrentMember.MemberValue > CDate(''1/1/2017'')
and [Pol Eff Date].[Full Date].CurrentMember.MemberValue < CDate(''1/10/2017'')
) *

Filter( [Dor Time].[Full Date].AllMembers,
[Dor Time].[Full Date].CurrentMember.MemberValue > CDate(''1/1/2000'')
and [Dor Time].[Full Date].CurrentMember.MemberValue < CDate(''1/1/2200'')
)


,
{ [Measures].[Inc Amt] }
)
,
( ' +
[Dim Coverage].[Coverage Cd].CurrentMember.UniqueName + ',' +
[Dim Zip].[Zip Cd].CurrentMember.UniqueName + ',' +
[Dim Mileage].[Mileage Cd].CurrentMember.UniqueName + ',' +
[Dim Producer].[Producer Cd].CurrentMember.UniqueName + ',' +
[Dim Program].[Program Cd].CurrentMember.UniqueName + ',' +
' [Measures].[Inc Amt]
)
)
'
)


member [Measures].[Loss Ratio] AS
iif ( [Measures].[Vwp] = 0, null,
([Measures].[Incurred])/[Measures].[Vwp]
),Format_string='Percent'
member [Measures].[EP Loss Ratio] AS
iif ( [Measures].[Vep] = 0, null,
([Measures].[Incurred])/[Measures].[Vep]
),Format_string='Percent'



SELECT
NON EMPTY
{ [Measures].[Vwp], [Measures].[Incurred],[Measures].[Loss Ratio],[Measures].[Vep],[Measures].[EP Loss Ratio]}
ON COLUMNS,
NON EMPTY
{
NonEmpty(
{
[Dim Coverage].[Coverage Cd].[Coverage Cd].AllMembers
[Dim Zip].[Zip Cd].[Zip Cd].AllMembers

[Dim Mileage].[Mileage Cd].[Mileage Cd].AllMembers

[Dim Producer].[Producer Cd].[Producer Cd].AllMembers

[Dim Program].[Program Cd].[Program Cd].AllMembers



         },
         { [Measures].[Vwp] }
     )
 } 

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME
ON ROWS
FROM [premium]
WHERE
(
Filter( [Dim Time Tran].[Full Date].AllMembers,
[Dim Time Tran].[Full Date].CurrentMember.MemberValue > CDate('1/1/2017')
and [Dim Time Tran].[Full Date].CurrentMember.MemberValue < CDate('1/10/2017')
)
)
CELL PROPERTIES
VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It's been a while, have you found any work around ?

0 Votes 0 ·

Lukas

thanks for asking
indeed i overcomplicated
it is a case of 2 measure groups
one time dimension claims related is non-conformed
so it works out

basically i am following the specs of the existing code based reports and trying to find out
can i fully substitutes cubes and mdx for writing c# code
then the gain is in maintenance less work
each time another customer wants a small change

0 Votes 0 ·
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

With the opening post only, this looked like a straightforward case of two measure groups and two time dimensions, one for each. However, from the MDX it appears you already have more than one time dimension in a cube, [Pol Eff Date] and [Dor Time], which may complicate things a bit. Still, multiple measure groups are a natural way of modelling related facts with common dimensions, and you now have first-hand experience that lookupcube is more of a last resort.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.