Forum Discussion

azakir's avatar
azakir
Resolver I
2 years ago
Solved

Schedule Refresh Not Working - Dynamic Data Source

Hi Guys.  I have a report that uses a number of tables. It refreshes fine on Power BI Desktop, but on service, I can't schedule refresh and get the following error:    I looked into my table...
Need Help
Show and Tell
Tips and Tricks
  • azakir's avatar
    azakir
    2 years ago

    Thanks for the reply Anonymous. I did try the following to change from dynamic to static. Although I do like your solution better: 

     

    Data_ODC = Sql.Database("PSGSQL05", "BlastLogic_PRODUCTION_703_Site_ODC", [Query="
    SELECT 'BlastLogic_PRODUCTION_703_Site_ODC' AS DatabaseName, a.*,
    CASE WHEN a.CollarError > CollarErrorTolerance THEN 1 ELSE 2 END AS [SpatialAccuracy(XY)],
    CASE WHEN a.CollarZError > CollarZToleranceLowerBound AND a.CollarZError < CollarZToleranceUpperBound THEN 2 ELSE 1 END AS [SpatialAccuracy(Z)],
    CASE WHEN a.DrillDepthError > DrillDepthErrorToleranceLowerBound AND a.DrillDepthError < DrillDepthErrorToleranceUpperBound THEN 2 ELSE 1 END AS [DepthCompliance],
    CASE WHEN a.DrillDepthError > DrillDepthErrorToleranceLowerBound AND a.DrillDepthError < DrillDepthErrorToleranceUpperBound THEN 2
    WHEN a.DrillDepthError < DrillDepthErrorToleranceLowerBound THEN 3 ELSE 1 END AS [DepthCompliance2],
    FORMAT(a.DrillingCompletedTime, 'dd/MM/yyyy') AS DrillingCompletedDate,
    a.ActualDepth,
    c.LastDipDepth,
    a.TargetDrillDepth,
    CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END AS [Depth],
    (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth AS [Length],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - c.TargetChargeDepth > DrillDepthErrorToleranceUpperBound THEN 'Long'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - c.TargetChargeDepth < DrillDepthErrorToleranceLowerBound THEN 'Short'
    ELSE 'WithinTolerance' END AS [DrillCompliance],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN ' <1m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN ' 1-3m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN ' 3-5m'
    ELSE '>5m' END AS [LengthGrouping],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth < -1 THEN '<-1m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 0 THEN '-1-0m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN '0-1m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN '1-3m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN '3-5m'
    ELSE '>5m' END AS [LengthGrouping],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth < -1 THEN 1
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 0 THEN 2
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN 3
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN 4
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN 5
    ELSE 6 END AS [LengthGroupingSort]
    FROM DW.DrillingEntry a
    LEFT JOIN DW.SiteParameter b ON 1 = b.Id
    LEFT JOIN DW.Hole c ON a.HoleId = c.Id
    "]),

     

    Had to do it 4 times for all the DB

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,azakir .I am glad to help you.
Although I am not very good at data conversion, I would like to make the following suggestion to you
I noticed that your dynamic data source is using a SQL Server database.
I recommend the following suggestions:
1. Use parameterized queries: create parameterized queries in Power BI Desktop and then use these parameters in Power BI Services.
2. You can use your data source to get M code to create an identical dataflow, because it is a SQL Server type of data source, I think it should be possible to achieve through dataflow
I also found articles about dynamic data source conversion, hope this helps:
URL:

Solved: Power BI Service with dynamic data sources - Microsoft Fabric Community
Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code (crossjoin.co.uk)
Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk)

Load data from a dynamic data source into a Power BI data stream and then use the data stream as the data source in a report. Data Stream Support for Scheduled Refresh

You can create a dataflow in the power BI service/datafactory using the gen1/gen2 dataflow

like this:

 


Paste your M code into the blank query


Choose the right data destination

Setting up incremental refresh for dataflow
Configuring the dataflow in Power BI service is similar:


Configure a refresh schedule for dataflow to use the newly created dataflow as a data source

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


 

  • azakir's avatar
    azakir
    Resolver I
    2 years ago

    Thanks for the reply Anonymous. I did try the following to change from dynamic to static. Although I do like your solution better: 

     

    Data_ODC = Sql.Database("PSGSQL05", "BlastLogic_PRODUCTION_703_Site_ODC", [Query="
    SELECT 'BlastLogic_PRODUCTION_703_Site_ODC' AS DatabaseName, a.*,
    CASE WHEN a.CollarError > CollarErrorTolerance THEN 1 ELSE 2 END AS [SpatialAccuracy(XY)],
    CASE WHEN a.CollarZError > CollarZToleranceLowerBound AND a.CollarZError < CollarZToleranceUpperBound THEN 2 ELSE 1 END AS [SpatialAccuracy(Z)],
    CASE WHEN a.DrillDepthError > DrillDepthErrorToleranceLowerBound AND a.DrillDepthError < DrillDepthErrorToleranceUpperBound THEN 2 ELSE 1 END AS [DepthCompliance],
    CASE WHEN a.DrillDepthError > DrillDepthErrorToleranceLowerBound AND a.DrillDepthError < DrillDepthErrorToleranceUpperBound THEN 2
    WHEN a.DrillDepthError < DrillDepthErrorToleranceLowerBound THEN 3 ELSE 1 END AS [DepthCompliance2],
    FORMAT(a.DrillingCompletedTime, 'dd/MM/yyyy') AS DrillingCompletedDate,
    a.ActualDepth,
    c.LastDipDepth,
    a.TargetDrillDepth,
    CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END AS [Depth],
    (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth AS [Length],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - c.TargetChargeDepth > DrillDepthErrorToleranceUpperBound THEN 'Long'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - c.TargetChargeDepth < DrillDepthErrorToleranceLowerBound THEN 'Short'
    ELSE 'WithinTolerance' END AS [DrillCompliance],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN ' <1m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN ' 1-3m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN ' 3-5m'
    ELSE '>5m' END AS [LengthGrouping],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth < -1 THEN '<-1m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 0 THEN '-1-0m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN '0-1m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN '1-3m'
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN '3-5m'
    ELSE '>5m' END AS [LengthGrouping],
    CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth < -1 THEN 1
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 0 THEN 2
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN 3
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN 4
    WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN 5
    ELSE 6 END AS [LengthGroupingSort]
    FROM DW.DrillingEntry a
    LEFT JOIN DW.SiteParameter b ON 1 = b.Id
    LEFT JOIN DW.Hole c ON a.HoleId = c.Id
    "]),

     

    Had to do it 4 times for all the DB