multiple fact tables vs lookupcube

Question

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

Answer 1

Hi，

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

Answer 2

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

Answer 3

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.