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
v-shex-msft
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 🙂