Tratando de mover mis informes ssrs a power bi No tengo ni idea de cómo empezar

Un usuario Pregunto ✅

Anónimo

Utilizo algunas consultas bastante complejas en algunos de mis informes en SSRS, pero no tengo idea de cómo comenzar a convertir algo como la consulta a continuación en power bi, cada paso depende de un paso anterior, las tablas base tienen entre 1000 y 100 millones registros (después de los filtros usados ​​en la consulta, el conjunto de resultados es <100 registros).

Así que aquí están mis preguntas (tenga en cuenta, muy poca experiencia en power bi, básicamente estoy probando para ver si un cambio a power bi es algo que deberíamos hacer)

Consulta directa o importación: dado que las tablas base tienen hasta 100 millones de registros, la consulta directa es la única opción o también es posible la importación, ya que el conjunto de resultados (después de los filtros) es <100 registros

¿Cómo manejo el hecho de que algunos pasos se basan en pasos anteriores?

Gracias de antemano por cualquier consejo que me puedas dar.

; WITH TMPMERGE AS (SELECT     INVENTTRANSID, DeliveredInvoiced, REPORTINGDATE, DELIVERYDATE, INVOICEDATE, RECID, NULL AS QTY, DeliveredInvoicedMST
                                                    FROM         DIM_VendPackingSlipInvoiced
                                                    WHERE     (REPORTINGDATE <= @SelDate) AND (DELIVERYDATE > @SelDate)
                                                    UNION ALL
                                                    SELECT DISTINCT INVENTTRANSID, NULL AS Expr1, NULL AS Expr2, NULL AS Expr3, NULL AS Expr4, RECID, QTY, NULL AS Expr5
                                                    FROM         DIM_VendPackingSlipInvoiced AS DIM_VendPackingSlipInvoiced_1
                                                    WHERE     (REPORTINGDATE <= @SelDate) AND (DELIVERYDATE <= @SelDate)), TMPRESULT AS
    (SELECT     INVENTTRANSID, SUM(COALESCE (DeliveredInvoiced, 0)) + SUM(COALESCE (QTY, 0)) AS RECEIVED
      FROM          TMPMERGE AS TMPMERGE_1
      GROUP BY INVENTTRANSID), TMP AS
    (SELECT     PARENT, CATEGORYGROUPID, CATEGORYID, CATEGORYTYPE, GDSORTORDER, PERCENTAGE, TRANSID, SUM(QTY) AS QTY, SUM(AMOUNTMST) 
                             AS AMOUNTMST, COALESCE (SUM(CCAmountMST), 0) AS ccamountmst, COALESCE (SUM(CCQTY), 0) AS ccqty, SUM(MarkupMST) AS MarkupMst, 
                             SUM(CCMarkupMST) AS CCMarkupMst, COALESCE (SUM(CCINVAmountMST), 0) AS ccINVamountmst, COALESCE (SUM(CCINVQTY), 0) AS ccINVqty, 
                             SUM(CCINVMarkupMST) AS CCINVMarkupMst, SUM(CASE WHEN P.GDREVERSEINVOICERECORD = 0 AND p.TransactionTypeID <> 14 THEN COALESCE (p.ccINVqty,
                              0) ELSE 0 END) AS ReverseInvoice, SUM(CASE WHEN P.GDCONSUMPTIONCOMMITTEDCOSTORIG = 1 THEN p.ccqty ELSE 0 END) AS ConsumedReceived, 
                             SUM(PPU) AS PPU
      FROM          FACT_ProjTransactions AS P
      WHERE      (PROJID LIKE CASE WHEN CHARINDEX('.', @ProjID) > 0 THEN @ProjID ELSE @ProjID + '%' END) AND (ISSubProject IN (0, @SubProjects)) AND 
                             (TransactionTypeID NOT IN (2, 3, 4)) AND (LOGDATE <= @SelDate)
      GROUP BY CATEGORYGROUPID, CATEGORYID, PARENT, CATEGORYTYPE, GDSORTORDER, TRANSID, PERCENTAGE), TMP1a AS
    (SELECT     P.PARENT, P.CATEGORYGROUPID, P.CATEGORYID, P.CATEGORYTYPE, P.GDSORTORDER, SUM(P.QTY) AS QTY, SUM(P.AMOUNTMST) AS AMOUNTMST, 
                             SUM(P.ccamountmst) AS ccamountmst, SUM(P.ccqty) AS ccqty, SUM(P.MarkupMst) AS MarkupMst, SUM(P.CCMarkupMst) AS CCMarkupMst, 
                             SUM(P.ccINVamountmst) AS ccINVamountmst, COALESCE (SUM(P.ccINVqty), 0) AS ccINVqty, SUM(P.CCINVMarkupMst) AS CCINVMarkupMst, SUM(P.ReverseInvoice)
                              AS reverseInvoice, SUM(S1.RECEIVED) AS RECEIVEDTMP, SUM(CONSUMEDRECEIVED) AS CONSUMEDRECEIVED, SUM(S1.RECEIVED) - SUM(ReverseInvoice) 
                             + SUM(P.Consumedreceived) AS Received, SUM((S1.RECEIVED - ReverseInvoice + P.Consumedreceived) * ABS(P.PPU)) AS receivedMst, 
                             SUM((S1.RECEIVED - ReverseInvoice + P.Consumedreceived) * ABS(P.PPU)) * (MAX(P.PERCENTAGE) / 100) AS receivedMstMarkup, SUM(CCAMOUNTMST) 
                             - SUM((COALESCE (S1.RECEIVED, 0) - COALESCE (ReverseInvoice, 0) + COALESCE (P.Consumedreceived, 0)) * ABS(P.PPU)) AS OpenMst, (SUM(CCAMOUNTMST) 
                             - SUM((COALESCE (S1.RECEIVED, 0) - COALESCE (ReverseInvoice, 0) + COALESCE (P.Consumedreceived, 0)) * ABS(P.PPU))) * (MAX(P.PERCENTAGE) / 100) 
                             AS OpenMstMarkup, SUM(P.ccqty) - SUM(S1.RECEIVED - ReverseInvoice + P.Consumedreceived) AS Openqty, TRANSID
      FROM          TMP AS P LEFT OUTER JOIN
                             TMPRESULT AS S1 ON S1.INVENTTRANSID = P.TRANSID
      GROUP BY P.CATEGORYGROUPID, P.CATEGORYID, P.PARENT, P.CATEGORYTYPE, P.GDSORTORDER, TRANSID
      HAVING      SUM(P.ccamountmst) <> 0 OR
                             SUM(P.ccINVamountmst) <> 0 OR
                             SUM(P.AMOUNTMST) <> 0), TMP1b AS
    (SELECT     PARENT, CATEGORYGROUPID, CATEGORYID, CATEGORYTYPE, GDSORTORDER, SUM(QTY) AS QTY, SUM(AMOUNTMST) AS AMOUNTMST, SUM(ccamountmst) 
                             AS ccamountmst, SUM(ccqty) AS ccqty, SUM(MarkupMst) AS MarkupMst, SUM(CCMarkupMst) AS CCMarkupMst, SUM(ccINVamountmst) AS ccINVamountmst, 
                             SUM(ccINVqty) AS ccINVqty, SUM(CCINVMarkupMst) AS CCINVMarkupMst, SUM(ReverseInvoice) AS reverseInvoice, SUM(RECEIVEDTMP) AS RECEIVEDTMP, 
                             SUM(CONSUMEDRECEIVED) AS CONSUMEDRECEIVED, SUM(RECEIVED) AS Received, SUM(receivedMst) AS receivedMst, SUM(receivedMstMarkup) 
                             AS receivedMstMarkup, SUM(OpenMst) AS OpenMst, SUM(OpenMstMarkup) AS OpenMstMarkup, SUM(Openqty) AS Openqty
      FROM          TMP1a
      GROUP BY CATEGORYGROUPID, CATEGORYID, PARENT, CATEGORYTYPE, GDSORTORDER), TMP2 AS
    (SELECT     PARENT, ADUVERSION, CATEGORYGROUPID, 'NA' AS CATEGORYID, SUM(ORIGINALBUDGET) AS budget, SUM(QUANTITY) AS QTY, source, CATEGORYTYPE
      FROM          FACT_Budgets AS P
      WHERE      (CutOff = 0) AND (PARENT = @ProjID) AND (ISSubProject IN (0, @SubProjects)) AND (VersionDate <= @SelDate) AND (ADUVERSION = 0) AND (source = 0) AND 
                             (@Version = - 1) OR
                             (CutOff = 0) AND (PARENT = @ProjID) AND (ISSubProject IN (0, @SubProjects)) AND (VersionDate <= @SelDate) AND (ADUVERSION = @Version) AND 
                             (@Version <> - 1)
      GROUP BY PARENT, CATEGORYGROUPID, CATEGORYID, ADUVERSION, source, CATEGORYTYPE
      UNION ALL
      SELECT     PARENT, ADUVERSION, CATEGORYGROUPID, CATEGORYID, SUM(ORIGINALBUDGET) AS budget, SUM(QUANTITY) AS QTY,  source, CATEGORYTYPE
      FROM         FACT_Budgets AS P2
      WHERE     (CutOff = 1) AND (PARENT = @ProjID) AND (ISSubProject IN (0, @SubProjects)) AND (VersionDate <= @SelDate) AND (ADUVERSION = 0) AND (source = 0) AND 
                            (@Version = - 1) OR
                            (CutOff = 1) AND (PARENT = @ProjID) AND (ISSubProject IN (0, @SubProjects)) AND (VersionDate <= @SelDate) AND (ADUVERSION = @Version)  AND 
                            (@Version > 0)  OR
                            (CutOff = 1) AND (PARENT = @ProjID) AND (ISSubProject IN (0, @SubProjects)) AND (VersionDate <= @SelDate) AND (ADUVERSION = 0)  AND 
                            (@Version = 0)  AND (source = 0) 
      GROUP BY PARENT, CATEGORYGROUPID, CATEGORYID, ADUVERSION, source, CATEGORYTYPE), TMP3 AS
    (SELECT     COALESCE (TMP_1.PARENT, TMP2a.PARENT) AS PROJID, COALESCE (TMP_1.CATEGORYGROUPID, TMP2a.CATEGORYGROUPID) AS CATEGORYGROUPID, 
                             COALESCE (TMP_1.CATEGORYID, TMP2a.CATEGORYID) AS CATEGORYID, TMP_1.QTY, TMP_1.AMOUNTMST, TMP2a.budget, TMP2a.QTY AS budgetQTY, 
                             TMP2a.ADUVERSION, TMP2a.source, TMP_1.MarkupMst, COALESCE (TMP_1.CATEGORYTYPE, TMP2a.CATEGORYTYPE) AS CATEGORYTYPE, 
                             TMP_1.CCMarkupMst, TMP_1.ccamountmst, TMP_1.ccqty, TMP_1.RECEIVEDTMP, TMP_1.CCINVMarkupMst, TMP_1.ccINVamountmst, TMP_1.ccINVqty, 
                             TMP_1.GDSORTORDER, TMP_1.Received, TMP_1.receivedMst, TMP_1.receivedMstMarkup, TMP_1.OpenMst, TMP_1.OpenMstMarkup
      FROM          TMP1b AS TMP_1 FULL OUTER JOIN
                             TMP2 AS TMP2a ON TMP2a.PARENT = TMP_1.PARENT AND TMP2a.CATEGORYID = TMP_1.CATEGORYID AND TMP_1.CATEGORYID <> 'NA'
      UNION ALL
      SELECT     TMP2_1.PARENT, TMP2_1.CATEGORYGROUPID, TMP2_1.CATEGORYID, 0 AS qty, 0 AS amountmst, TMP2_1.budget, TMP2_1.QTY AS budgetqty, 
                            TMP2_1.ADUVERSION, TMP2_1.source, 0 AS markupmst, S1_1.CATEGORYTYPE, 0 AS ccmarkupmst, 0 AS CCAmountmst, 0 AS ccqty, 0 AS ccdeliveredamount, 
                            0 AS ccINVmarkupmst, 0 AS CCINVAmountmst, 0 AS ccINVqty, 0 AS GDSORTORDER, 0 AS received, 0 AS receivedMST, 0 AS receivedMSTMarkup, 0 AS OpenMST, 
                            0 AS OpenMSTMarkup
      FROM         TMP2 AS TMP2_1 LEFT OUTER JOIN
                                (SELECT DISTINCT CATEGORYGROUPID, CATEGORYTYPE
                                  FROM          DIM_Category) AS S1_1 ON S1_1.CATEGORYGROUPID = TMP2_1.CATEGORYGROUPID
      WHERE     (TMP2_1.CATEGORYID = 'NA'))
    SELECT     TMP3_1.PROJID, TMP3_1.CATEGORYGROUPID, TMP3_1.CATEGORYID, TMP3_1.QTY, TMP3_1.AMOUNTMST, TMP3_1.budget, TMP3_1.budgetQTY, 
                            TMP3_1.ADUVERSION, TMP3_1.source, TMP3_1.MarkupMst, TMP3_1.CATEGORYTYPE, S1.Label, TMP3_1.CCMarkupMst, TMP3_1.ccamountmst, TMP3_1.ccqty, 
                            TMP3_1.Received, TMP3_1.CCINVMarkupMst, TMP3_1.ccINVamountmst, TMP3_1.ccINVqty, TMP3_1.receivedMst, TMP3_1.GDSORTORDER, 
                            TMP3_1.RECEIVEDTMP, TMP3_1.receivedMstMarkup, TMP3_1.OpenMst, TMP3_1.OpenMstMarkup, S2.PROJGROUPID, S2.STATUS, S9.LastBudget
     FROM         TMP3 AS TMP3_1 LEFT OUTER JOIN
                            DIM_EnumsFromModelStore AS S1 ON S1.Value = TMP3_1.CATEGORYTYPE AND S1.ID = 'CATEGORYTYPE' AND S1.Language = @Language LEFT OUTER JOIN
                            DIM_ProjTable S2 ON S2.PROJID = TMP3_1.PROJID LEFT OUTER JOIN 
(SELECT top 1 PROJID, SUM([ORIGINALBUDGET]) AS LastBudget,[ADUVERSION]
  FROM [FACT_Budgets] where projid = @ProjID
  GROUP BY PROJID,[ADUVERSION]
  ORDER BY ADUVERSION DESC) S9 ON S9.PROJID = TMP3_1.PROJID
     WHERE     ((TMP3_1.ADUVERSION = CASE WHEN @Version = - 1 THEN 0 ELSE @Version END) OR
                            (TMP3_1.ADUVERSION IS NULL)) AND NOT (TMP3_1.CATEGORYID = 'NA' AND QTY IS NULL) AND NOT (amountmst IS NULL AND budget = 0 AND 
                            ccamountmst IS NULL)
     ORDER BY TMP3_1.CATEGORYGROUPID, TMP3_1.CATEGORYID

Hola @Anónimo,

Me gustaría sugerirle que migre el informe SSRS al servidor Power bi Report, si tiene una licencia premium, puede incrustar el archivo de informe PBRS (*. Rdl) en el servicio Power Bi y verlo directamente en el lado del servicio Power Bi.

Enlaces de referencia:

Migrar SSRS 2017 a Power BI Report Server

¿Qué son los informes paginados en Power BI Premium? (Avance)

Saludos,

Xiaoxin Sheng

Anónimo

En respuesta a v-shex-msft

Hola Xiaoxin Sheng,

Es cierto, pero ¿por qué entonces usar power bi?

Estoy probando para ver si podemos pasar a power bi desde ssrs y tu sugerencia es hacerlo en ssrs y colocarlo en power bi.

No es la respuesta que estaba buscando 🙂

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *