Report Model Inefficiencies. Need Advice.

Question

I'm trying to help my power users have more access to our data so I don't have to interrupt my work (playing Pac-Man) 25 times a day writing Ad Hoc Queries and such.

I'm trying to use Data Source Views, Data Models, and Report Builder 2 and 3 to allow them to have access to cleansed data in which they can safely do their own basic analysis. I want to create generic Report Models covering business processes rather than a specific report model for each ad hoc report they would need.

I have to create the Data Source View (DSV) with a named query because the source database lacks primary keys, but does have unique clustered indexes on identity_columns.

Here's my problem. When I use a relatively simple query like this:

SELECT SOM.FSONO AS SalesNo
     , SOM.FCUSTNO AS CustNo
     ,SLC.fcompany as CustName
     , SOM.FCUSTPONO AS CustPONo
     , SOM.fsoldby AS SalesPerson
     , SOR.FENUMBEr AS ItemNo
     , SOR.finumber AS IntItemNo
     , SOR.frelease AS Rels
     , SOI.fprodcl AS ProdClass
     , SOI.fgroup AS GroupCode
     , rtrim(SOR.FPARTNO) AS PartNo
     , SOR.fpartrev AS PartRev
     , cast(SOI.fdesc AS VARCHAR(20)) AS PartDescription
     ,SOM.forderdate as OrderDate
     ,SOR.fduedate as DueDate
     , SOR.FORDERQTY AS QtyOrd
     , SOR.FUNETPRICE AS NetUnitPrice
     , (SOR.FORDERQTY * SOR.funetprice) AS NetAmountOrdered
FROM  slcdpm SLC inner join 
somast SOM on SLC.fcustno = SOM.fcustno
     LEFT OUTER JOIN soitem SOI
       ON (SOM.fsono = SOI.fsono)
     LEFT OUTER JOIN sorels SOR
       ON (SOI.fsono = SOR.fsono)     
AND       (SOI.finumber = SOR.finumber)

Let's assume the user takes the Report Model in Report Builder 3 and only requests SalesNo, PartNo, PartRev, OrderDate, and TotalNetAmount for their dataset.

The SQL Generated to pull that data is:

SET DATEFIRST 7
SELECT
    CAST(1 AS BIT) [c0_is_agg],
    CAST(1 AS BIT) [c1_is_agg],
    CAST(1 AS BIT) [c2_is_agg],
    CAST(1 AS BIT) [c3_is_agg],
    4 [agg_row_count],
    [CustomerSales].[TotalNetAmountOrdered] [TotalNetAmountOrdered],
    [CustomerSales].[SalesNo] [SalesNo],
    [CustomerSales].[PartNo] [PartNo],
    [CustomerSales].[PartRev] [PartRev],
    [CustomerSales].[OrderDate] [OrderDate]
FROM
    (
        SELECT
            SUM([CustomerSales].[NetAmountOrdered]) [TotalNetAmountOrdered],
            [CustomerSales].[SalesNo] [SalesNo],
            [CustomerSales].[PartNo] [PartNo],
            [CustomerSales].[PartRev] [PartRev],
            [CustomerSales].[OrderDate] [OrderDate]
        FROM
            (
                SELECT        SOM.fsono AS SalesNo, SOM.fcustno AS CustNo, SLC.fcompany AS CustName, SOM.fcustpono AS CustPONo, SOM.fsoldby AS SalesPerson, 
                         SOR.fenumber AS ItemNo, SOR.finumber AS IntItemNo, SOR.frelease AS Rels, SOI.fprodcl AS ProdClass, SOI.fgroup AS GroupCode, RTRIM(SOR.fpartno) AS PartNo, 
                         SOR.fpartrev AS PartRev, CAST(SOI.fdesc AS VARCHAR(20)) AS PartDescription, SOM.forderdate AS OrderDate, SOR.fduedate AS DueDate, SOR.forderqty AS QtyOrd, 
                         SOR.funetprice AS NetUnitPrice, SOR.forderqty * SOR.funetprice AS NetAmountOrdered
FROM            slcdpm AS SLC INNER JOIN
                         somast AS SOM ON SLC.fcustno = SOM.fcustno LEFT OUTER JOIN
                         soitem AS SOI ON SOM.fsono = SOI.fsono LEFT OUTER JOIN
                         sorels AS SOR ON SOI.fsono = SOR.fsono AND SOI.finumber = SOR.finumber
            ) [CustomerSales]
        WHERE
            CAST(1 AS BIT) = 1
        GROUP BY
            [CustomerSales].[SalesNo], [CustomerSales].[PartNo], [CustomerSales].[PartRev], [CustomerSales].[OrderDate]
    ) [CustomerSales]
ORDER BY
    [SalesNo], [PartNo], [PartRev], [OrderDate]

I would have expected only the fields pulled which the user requests in the report and not every single field in the DSV. Also, if parameters are created which constrain the data such as a beginning and ending date for OrderDate, the full data set is returned anyway.

Am I doing something wrong here?

Is there a better way to approach this?

Do other administrators find themselves with performance issues when using Report Models?

Answer 1

There are sometimes performance issues when dealing with Report Models. This is one of the reasons that report models are not meant for rolling out to all of your users to replace all reports. The queries generated by the semantic query engine behind reports models are not tunable and are often totally NOT the way you yourself woudl write them.

The engine essentially treats the named query as a view, which it expands into the underlying query, just as it would a view. This is often an issue when building a model directly overlaying your database.

The ideal situation, from my perspective, is to have a separate database (datawarehouse possibly) that is preferrably housed on a separate server. This dw would be flattenned out such that you could optimize it for read performance. Then, you could use those tables directly in your data source view and the semantic query engine behind the model should be able to make better queries.

This ideal is often not possible due to economic or other constraints. Could you try having a job more or less ETL from your base tables into a new set of tables that you could optimize for reporting to support your model?