asked on

Looking for solutions for huge data Report

Hi experts,

Anyone can offer any solutions for huge data. Our claim data is huge even though only 2 years data. For SSRS report, some parameters one year results are over 100 million rows, takes forever to deliver. e.g. some reports are 40 columns, need to join 20 tables, very slow.

We are thinking to make two flat tables, pre-join all the tables
or make a star schema data warehouse ...

Please give me some suggestions, if these solutions work or you have any better ones.

Thank you in advance.

Rose

Brian Crowe

Without knowing more specifics about your data it's hard to come up with a clear plan but I think moving towards a more traditional multi-dimensional star schema would be a good first step. You may also find the need to generate some pre-aggregated tables like claim totals by day/month/etc.

Jim Horn

>Please give me some suggestions
Either hire an experienced reporting developer or architect, or contract with a local consulting company to build a reporting data warehouse database, ETL jobs that feed it, and reports.

There's no reason why a reporting function should bang against tables that could output 100 million rows, and I would be highly suspect that such processes would slow down other non-reporting functions using the same data.

>We are thinking to make two flat tables, pre-join all the tables or make a star schema data warehouse ...
You're on the right track, but there is not enough information here to be actionable, and what you need is way more then the scope of a single EE question.

Good luck.
Jim

Rose Johnson

ASKER

Hi Brian Crowe,

Thank you for replying!

It happens just providing data, no aggregation needed, here's the example of sp of the report. Trouble is our DBA does give us any rights, can't connect Analysis, Report, Integration Service engine, everything has to be on production server.

USE [ReportsDB]
GO

/****** Object: StoredProcedure [MY].[USP2000577M_Provider_Claims] Script Date: 02/10/2016 09:27:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/**************************************************************************/
--CREATE PROCEDURE [MY].[USP2000577M_Provider_Claims-1]
--(
      Declare      @FROMDATE        AS DATETIME
            ,@THRUDATE        AS DATETIME
            ,@PROVID             AS VARCHAR(MAX) = NULL
            ,@TAXID             AS VARCHAR(MAX) = NULL
            ,@NPI             AS VARCHAR(MAX) = NULL
            ,@PROCCODE        AS VARCHAR(500) = NULL
            ,@STATUS             AS VARCHAR(500) = NULL
            ,@HEADER_LINE AS CHAR(6) = NULL
            ,@DATETYPE        AS VARCHAR(7) = NULL
            ,@GRPNPI AS VARCHAR (MAX)= NULL
            ,@AHCCCSID AS VARCHAR (100)= NULL
--)
--AS

SET NOCOUNT ON

******************************************************************************/

BEGIN
            if (@PROVID IS NOT NULL AND @PROVID <> '') set @PROVID = REPLACE(@PROVID,' ','')
            if (@NPI IS NOT NULL AND @NPI <> '') set @NPI = REPLACE(@NPI,' ','')
            if (@TAXID IS NOT NULL AND @TAXID <> '') set @TAXID = REPLACE(@TAXID,' ','')
            if (@AHCCCSID IS NOT NULL AND @AHCCCSID <> '') set @AHCCCSID = REPLACE(@AHCCCSID,' ','')
            if (@GRPNPI IS NOT NULL AND @GRPNPI <> '') set @GRPNPI = REPLACE(@GRPNPI,' ','')
            if (@STATUS IS NOT NULL AND @STATUS <> '') set @STATUS = REPLACE(@STATUS,' ','')
            if (@PROCCODE IS NOT NULL AND @PROCCODE <> '') set @PROCCODE = REPLACE(@PROCCODE,' ','')

            declare @StartDate varchar(19)
            declare @EndDate varchar(19)

            SET @StartDate      = CONVERT(varChar(19), @FROMDATE, 101) + ' 00:00:00'
            SET @EndDate      = CONVERT(varChar(19), @THRUDATE, 101) + ' 23:59:00'

            IF OBJECT_ID('TEMPDB.dbo.#USP2000577M_1') IS NOT NULL
            DROP TABLE #USP2000577M_1


            select distinct
                         [claimid] = RTRIM(cl.claimid)
                        ,[dos] = cl.startdate
                        ,[carriermemid]= RTRIM(ek.carriermemid)
                        ,[member] = RTRIM(m.fullname)
                        ,[provid] = RTRIM(p.provid)
                        ,[npi] = RTRIM(p.npi)
                        ,[serv_provider] = RTRIM(p.fullname)
                        ,[payto] = RTRIM(p2.fullname)
                        ,[tin] = p2.fedid
                        ,[status] = RTRIM(cl.status)
                        ,Check_date = cl.paiddate
                        ,RecDate = cl.logdate
                        ,[pat_control_num] = cl.controlnmb
                        ,[programid] = ek.programid
                        ,[rev_programid] = ek.programid
,pc.groupnpi
,cl.referralid
,cl.lastupdate
,cl.totalpaid
,cl.totalamt

            INTO #USP2000577M_1
            from PLANREPORT_QNXT_MMIC.DBO.claim                                          cl (NOLOCK)
            JOIN PLANREPORT_QNXT_MMIC.DBO.member                                    m (NOLOCK)        ON cl.memid        = m.memid
            join PlanReport_QNXT_MMIC.dbo.enrollkeys                              ek (NOLOCK) on cl.enrollid = ek.enrollid
            JOIN PLANREPORT_QNXT_MMIC.DBO.affiliation                              a (NOLOCK)        ON cl.affiliationid = a.affiliationid
            JOIN PLANREPORT_QNXT_MMIC.DBO.provider                                    p (NOLOCK)        ON a.provid                    = p.provid
            JOIN PLANREPORT_QNXT_MMIC.DBO.provider                                    p2 (NOLOCK)        ON a.affiliateid        = p2.provid
            left join MMIC_Data_Mart.MY.provider_contracts                  pc (NOLOCK)            ON cl.provid = pc.provid
              AND ek.programid = pc.programid
              AND p.npi = pc.provnpi
              AND a.affiliationid = pc.affiliationid
            LEFT JOIN (SELECT pxv.paymentid, pxv.claimid
                               FROM PLANREPORT_QNXT_MMIC.DBO.payvoucher (NOLOCK) pxv
                               INNER JOIN [planreport_QNXT_MMIC].[dbo].[payment] (NOLOCK) pxmt ON pxv.paymentid = pxmt.paymentid AND pxmt.status <> 'VOID') pv ON cl.claimid      = pv.claimid

       WHERE ((@DATETYPE = 'SERVICE' AND cl.STARTDATE BETWEEN @StartDate AND @EndDate)
                         OR
                        (@DATETYPE = 'UPDATE' AND cl.LASTUPDATE BETWEEN @StartDate AND @EndDate)
                         OR
                        (@DATETYPE = 'PAID' AND cl.PAIDDATE BETWEEN @StartDate AND @EndDate))
            AND ((@STATUS = '' OR @STATUS IS NULL) OR ((@STATUS != '' OR @STATUS IS NOT NULL) AND (cl.status IN (SELECT value FROM ReportsDB.DI.USF_Split(@STATUS,',')))))
            AND ((@PROVID = '' OR @PROVID IS NULL) OR ((@PROVID != '' OR @PROVID IS NOT NULL) AND (p.provid in (SELECT value FROM ReportsDB.DI.USF_Split(@PROVID,',')))))
       AND ((@NPI = '' OR @NPI IS NULL) OR ((@NPI != '' OR @NPI IS NOT NULL) AND (p.NPI in (SELECT value FROM ReportsDB.DI.USF_Split(@NPI,',')))))
            AND ((@TAXID = '' OR @TAXID IS NULL) OR ((@TAXID != '' OR @TAXID IS NOT NULL) AND (p2.fedid in (SELECT value FROM ReportsDB.DI.USF_Split(@TAXID,',')))))
            AND ((@AHCCCSID = '' OR @AHCCCSID IS NULL) OR ((@AHCCCSID != '' OR @AHCCCSID IS NOT NULL) AND (ek.carriermemid in (SELECT value FROM ReportsDB.DI.USF_Split(@AHCCCSID,',')))))

       CREATE INDEX IDX_ClaimID ON #USP2000577M_1(claimid)

           IF OBJECT_ID('TEMPDB.DBO.#USP2000577M_2') IS NOT NULL DROP TABLE #USP2000577M_2

            select distinct
                         cl.[claimid]
                        ,case when @HEADER_LINE = 'HEADER' THEN 'N/A' ELSE cast(cd.claimline as varchar(8)) end [claimline]
                        ,cl.[dos]
                        ,cl.[carriermemid]
                        ,cl.[member]
                        ,cl.[provid]
                        ,cl.[npi]
                        ,cl.[serv_provider]
                        ,cl.[payto]
                        ,cl.[tin]
                        ,[Authorizationid] = CASE WHEN (cl.referralid = '' AND cd2.referralid = '')      THEN 'No Auth' ELSE (CASE WHEN cl.referralid > '.' THEN cl.referralid ELSE cd2.referralid END)       END
                        ,case when @HEADER_LINE = 'HEADER' THEN 'N/A' ELSE cd.prindiag END Diagnosis
                        ,case when @HEADER_LINE = 'HEADER' THEN 'N/A' ELSE cd.servcode END servcode
                        ,case when @HEADER_LINE = 'HEADER' THEN 'N/A' ELSE CAST(cd.servunits AS CHAR(6)) END [Servunits]
                        ,case when @HEADER_LINE = 'HEADER' THEN 'N/A' ELSE CAST(cd.modcode AS CHAR(6)) END [modifier]
                        ,case when @HEADER_LINE = 'HEADER' THEN 'N/A' ELSE CAST(RTRIM(cd.revcode) AS CHAR(6)) END [Rev_code]
                        ,cl.[status]
                        ,Remit_comments = CAST(eob.eobexplanation AS VARCHAR(MAX))
                        ,[CHECK #] = pc.checknbr
                        ,cl.Check_date
                        ,cl.RecDate
                        ,case when @HEADER_LINE = 'HEADER' THEN cl.lastupdate else cd.lastupdate end Last_Update
                        ,case when @HEADER_LINE = 'HEADER' THEN cl.totalamt else cd.claimamt end [billed_amt]
                        ,case when @HEADER_LINE = 'HEADER' THEN
                                          (Select sum([contractpaid]) FROM planreport_QNXT_MMIC.DBO.claimdetail (NOLOCK) cd3 where cl.claimid = cd3.claimid)
                                    else cd.[contractpaid]
                         end [contractpaid]
                        ,case when @HEADER_LINE = 'HEADER' THEN cl.totalpaid else cd.amountpaid end [paid_amt]                        ,cl.[pat_control_num]
                        ,cl.[programid]
                        ,cl.[rev_programid]
,cl.groupnpi
,cl.referralid
,cl.lastupdate
,cl.totalpaid
,cl.totalamt

INTO #USP2000577M_2
from #USP2000577M_1 cl (NOLOCK)
join PLANREPORT_QNXT_MMIC.DBO.claimdetail      AS cd (NOLOCK)      ON cl.claimid = cd.claimid
LEFT JOIN PLANREPORT_QNXT_MMIC.DBO.claimdetail AS cd2 (NOLOCK) ON cL.claimid = cd2.claimid AND cd2.claimline = 1
            LEFT OUTER JOIN planreport_qnxt_MMIC.dbo.claimeobexplain eob (NOLOCK) ON cd.claimid = eob.claimid
            LEFT JOIN (SELECT pxv.paymentid, pxv.claimid
                               FROM PLANREPORT_QNXT_MMIC.DBO.payvoucher (NOLOCK) pxv
                               INNER JOIN [planreport_QNXT_MMIC].[dbo].[payment] (NOLOCK) pxmt ON pxv.paymentid = pxmt.paymentid AND pxmt.status <> 'VOID') pv ON cl.claimid      = pv.claimid
            LEFT JOIN PLANREPORT_QNXT_MMIC.DBO.paycheck AS pc (NOLOCK) ON pv.paymentid      = pc.paymentid AND cd.fundid = pc.fundid
            LEFT JOIN PLANREPORT_QNXT_MMIC.DBO.claimattribute AS ca (NOLOCK) ON cl.claimid = ca.claimid      AND attributeid IN ('T02491957', 'T02579383', 'T02580499')
WHERE 1 = 1
            and ((@PROCCODE = '' OR @PROCCODE IS NULL) OR ((@PROCCODE != '' OR @PROCCODE IS NOT NULL) and (cd.servcode       in (SELECT value FROM ReportsDB.DI.USF_Split(@PROCCODE,',')))))
            and ((@GRPNPI = '' OR @GRPNPI IS NULL) OR ((@GRPNPI != '' OR @GRPNPI IS NOT NULL) and (cl.groupnpi in (SELECT value FROM ReportsDB.DI.USF_Split(@GRPNPI,',')))))
order by cl.claimid

       CREATE INDEX IDX_ClaimID ON #USP2000577M_2(claimid)
       if @HEADER_LINE <> 'HEADER'
       select * from #USP2000577M_2 order by claimid,CAST(claimline as int)
       ELSE
             select * from #USP2000577M_2 order by claimid

END

/**************************************************************************/

GO

Rose Johnson

ASKER

Hi Jim Horn,

Thank you for replying!

Please see the my last comment, does it give you any information you need?

I'm thinking the problem is we have to run report directly in production server, and we don't have right to move data to any where, only have a small ref database we can create a table. They said no budget to hire anyone else, even though buy Analysis, Report, Integration Service engines, DBA said they are other databases which I've never heard. Anyway, we only can deal with it under such circumstances.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rose Johnson

ASKER

Thank you all your contributions, I'm having hard time to decide which suggestions I can apply. Since some of them are out of my knowledge especially DBA's parts ( I can't expect on DBA's collaboration though, but it's good to have some knowledge) so please give me some time to figure out what can really help. Sorry for the delay comments.

Rose Johnson

ASKER

Hi All,

I took part suggestions of each of you.
Thank you all the answers.

Rose