MDX parameters in SSRS with large data set

Question

I have a lot of data in a cube and I have a query that works perfectly.

I need to make this smaller and the user needs to add the Number and Year and the data displayed because there is just too much information.

I have added a parameter on the number field and also year field. But once I build this in SSRS the query takes 15 minutes. In SQL if you add a where clause and let the user enter the number it will reduce the amount of time the query takes. Will this also work with a cube.

Here is the MDX script:

SELECT 
  NON EMPTY 
    {
      [Measures].[Ontvangen Verbruik]
     ,[Measures].[Gecorrigeerd Verbruik]
    } ON COLUMNS
 ,NON EMPTY 
    {
        [Aansluitingen].[EAN].[EAN].ALLMEMBERS*
        [Aansluitingen].[EDSN Manier Van Levering].[EDSN Manier Van Levering].ALLMEMBERS*
        [Aansluitingen].[EDSN Gebruiker Type].[EDSN Gebruiker Type].ALLMEMBERS*
        [Aansluitingen].[SJV Bron].[SJV Bron].ALLMEMBERS*
        [Aansluitingen].[SJV Electra Hoog].[SJV Electra Hoog].ALLMEMBERS*
        [Aansluitingen].[SJV Electra Laag].[SJV Electra Laag].ALLMEMBERS*
        [Aansluitingen].[SJV Electra Totaal].[SJV Electra Totaal].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Straat].[Aansluiting Straat].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Huis Nummer].[Aansluiting Huis Nummer].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Huis Nummer Toevoeging].[Aansluiting Huis Nummer Toevoeging].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Postcode].[Aansluiting Postcode].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Plaats].[Aansluiting Plaats].ALLMEMBERS*
        [Aansluitingen].[SJV Gas].[SJV Gas].ALLMEMBERS*
        [Additionele informatie meteropnames].[Bron Meetgegevens Nederlands].[Bron Meetgegevens Nederlands].ALLMEMBERS*
        [Collectieven].[Collectief Naam].[Collectief Naam].ALLMEMBERS*
        [Collectieven].[Collectief ID].[Collectief ID].ALLMEMBERS*
        [Klanten].[Klant Nummer].[Klant Nummer].ALLMEMBERS*
        [Klanten].[Klant Naam].[Klant Naam].ALLMEMBERS*
        [Meetdatums].[Meetdatum Jaar].[Meetdatum Jaar].ALLMEMBERS*
        [Telwerken].[Telwerk].[Telwerk].ALLMEMBERS*
        [VerbruikEinddatums].[VerbruikEinddatum].[VerbruikEinddatum].ALLMEMBERS*
        [VerbruikStartdatums].[VerbruikStartdatum].[VerbruikStartdatum].ALLMEMBERS
    }
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_VALUE
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM 
(
  SELECT 
    StrToSet
    (@AdditioneleinformatiemeteropnamesBronMeetgegevensNederlands
     ,CONSTRAINED
    ) ON COLUMNS
  FROM 
  (
    SELECT 
      StrToSet
      (@AansluitingenEAN
       ,CONSTRAINED
      ) ON COLUMNS
    FROM 
    (
      SELECT 
        StrToSet
        (@MeetdatumsMeetdatumJaar
         ,CONSTRAINED
        ) ON COLUMNS
      FROM [Meteropnames]
    )
  )
)
CELL PROPERTIES 
  VALUE
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

Just some tips to improve the query of this cube. Some direction or help

score 0 · Answer 1 · answered Jun 23 '23 at 14:08

It looks like a huge cross join on rows - hence the complexity and time taken to process. Can you delete any of those dimensions from the cross join without upsetting stakeholders?

There are three nested sub-selects - you could try moving those directly in to the ROWS clause?

SELECT 
  NON EMPTY 
    {
      [Measures].[Ontvangen Verbruik]
     ,[Measures].[Gecorrigeerd Verbruik]
    } ON COLUMNS
 ,NON EMPTY 
    {
        StrToSet( @AansluitingenEAN ,CONSTRAINED ) *
        [Aansluitingen].[EDSN Manier Van Levering].[EDSN Manier Van Levering].ALLMEMBERS*
        [Aansluitingen].[EDSN Gebruiker Type].[EDSN Gebruiker Type].ALLMEMBERS*
        [Aansluitingen].[SJV Bron].[SJV Bron].ALLMEMBERS*
        [Aansluitingen].[SJV Electra Hoog].[SJV Electra Hoog].ALLMEMBERS*
        [Aansluitingen].[SJV Electra Laag].[SJV Electra Laag].ALLMEMBERS*
        [Aansluitingen].[SJV Electra Totaal].[SJV Electra Totaal].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Straat].[Aansluiting Straat].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Huis Nummer].[Aansluiting Huis Nummer].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Huis Nummer Toevoeging].[Aansluiting Huis Nummer Toevoeging].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Postcode].[Aansluiting Postcode].ALLMEMBERS*
        [Aansluitingen].[Aansluiting Plaats].[Aansluiting Plaats].ALLMEMBERS*
        [Aansluitingen].[SJV Gas].[SJV Gas].ALLMEMBERS*StrToSet(@AdditioneleinformatiemeteropnamesBronMeetgegevensNederlands,CONSTRAINED)*
        [Collectieven].[Collectief Naam].[Collectief Naam].ALLMEMBERS*
        [Collectieven].[Collectief ID].[Collectief ID].ALLMEMBERS*
        [Klanten].[Klant Nummer].[Klant Nummer].ALLMEMBERS*
        [Klanten].[Klant Naam].[Klant Naam].ALLMEMBERS*
        StrToSet( @MeetdatumsMeetdatumJaar, CONSTRAINED ) *
        [Telwerken].[Telwerk].[Telwerk].ALLMEMBERS*
        [VerbruikEinddatums].[VerbruikEinddatum].[VerbruikEinddatum].ALLMEMBERS*
        [VerbruikStartdatums].[VerbruikStartdatum].[VerbruikStartdatum].ALLMEMBERS
    }
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_VALUE
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM [Meteropnames]
CELL PROPERTIES 
  VALUE
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

Another simplification I'd suggest is do you need the DIMENSION PROPERTIES? I'd suggest deleting this section to see if it affects the speed, or breaks the results?

MDX parameters in SSRS with large data set

1 Answers1