Forum Discussion

fabricpribeiro's avatar
fabricpribeiro
Icon for Post Patron rankPost Patron
4 months ago
Solved

shortcuts - Refresh Method

Dears,   I have a report which is fed by my views.   This views query shortcut tables . The shorcuts point to another Fabric wokspace where the true tables are   Many times, when I open the rep...
workspaces
  • v-echaithra's avatar
    v-echaithra
    4 months ago

    Hi fabricpribeiro ,

    Thanks for the detailed clarification. Based on your answers, this appears closer to a SQL Analytics Endpoint metadata/cache synchronization issue over shortcut tables rather than expected shortcut behavior.

    The important observation is that even the shortcut tables themselves become empty until refreshed. That suggests the SQL Endpoint is temporarily losing visibility of the shortcut metadata/state.

    For the workaround, you can schedule a notebook against the Lakehouse containing the shortcuts/views and periodically refresh the metadata.
    You can try the following PySpark code in a Fabric notebook to refresh the shortcut tables metadata, you can start with a simple refresh:

    spark.sql("REFRESH TABLE Purview_DQRules.table_name")


    For multiple tables:


    tables = ["table1","table2","table3"]

    for t in tables:
    spark.sql(f"REFRESH TABLE Purview_DQRules.{t}")
    print(f"Refreshed {t}")


    The REST based MetadataRefreshExternalCommand shared earlier is also valid and refreshes the SQL Analytics Endpoint metadata more directly, which may be more effective in this scenario than only running REFRESH TABLE.

    Recommended frequency:

    Start with every 15 minutes
    If the issue persists, try every 5 minutes
    If stable, increase gradually to 30 minutes

    Best Regards

  • svenchio's avatar
    svenchio
    3 months ago

    Hi fabricpribeiro  I read thru the conversation and I think the solution was to refresh the SQL Analytics Endpoint (SAE) metadata/cache on the source of the data and the community provided a couple of interest programatic solutions via yNotebooks, did it worked for you? Just curious since you have not accepted any solutions. If the solution is the refresh of the SAE and the question is wich alternatives are there to make it happend automatically and via schedule, I guess there's two: 

     

    #1. Calling the REST API  Programmatically Refresh & Sync SQL Analytics Endp... - Microsoft Fabric Communityvia Notebooks (seems mosth popular) ... fouind this blog post  from community member vojtechsima   well explain 

    Programmatically Refresh & Sync SQL Analytics Endp... - Microsoft Fabric Community

    #2. Calling new data pipeline activity  Refresh SQL Endpoint Activity - Microsoft Fabric | Microsoft Learn , this is currently in preview, but it works very well 😁  

     

    I guess it will looks something like this : 

     

    I guess there was already a a lot of good information on the current chat, hope you can mark the solution that works for you so the community is aware what worked! If you find this useful, a thumbs up would be nice 😉, proposed also as solution if appropiete. 

BHANUPURAM's avatar
BHANUPURAM
Icon for Advocate II rankAdvocate II
4 months ago

Great question — you're not alone in this!

Is it a bug?

No, it's expected behavior but poorly documented. The issue isn't the shortcut itself (that's a live pointer). The real culprit is the SQL Analytics Endpoint (SAE) metadata cache. Since your report reads through views, those views depend on the SAE layer. When the SAE goes idle (~15 min of inactivity), its metadata goes stale  that's why your report shows blank data until you manually refresh.

The Fix

Schedule a Fabric Notebook to auto-refresh the SAE metadata every 10–15 minutes. This is the most reliable, production-proven approach. 

fabricpribeiro's avatar
fabricpribeiro
Icon for Post Patron rankPost Patron
4 months ago

Thanks, which command should I put in the notebook? in order to refresh? also, is it in the LH which has the real data or the lakehouse which has the shorcuts and the views?

  • BHANUPURAM's avatar
    BHANUPURAM
    Icon for Advocate II rankAdvocate II
    4 months ago

    Try this Sample code: replace the parameters with your workspaceid, lakehouse id etc 
    import sempy.fabric as fabric
    import json, time

     

    # Get workspace and lakehouse IDs
    workspace_id = spark.conf.get("trident.workspace.id")
    lakehouse_id = spark.conf.get("trident.lakehouse.id")

     

    # Get SQL Analytics Endpoint ID
    client = fabric.FabricRestClient()
    lakehouse_info = client.get(
        f"/v1/workspaces/{workspace_id}/lakehouses/{lakehouse_id}"
    ).json()
    sql_endpoint_id = lakehouse_info['properties']['sqlEndpointProperties']['id']

     

    # Trigger metadata refresh
    uri = f"/v1.0/myorg/lhdatamarts/{sql_endpoint_id}"
    payload = {"commands": [{"$type": "MetadataRefreshExternalCommand"}]}
    response = client.post(uri, json=payload)

     

    data = response.json()
    batch_id = data["batchId"]

     

    # Poll until complete
    status_uri = f"/v1.0/myorg/lhdatamarts/{sql_endpoint_id}/batches/{batch_id}"
    progress = data["progressState"]
    while progress == "inProgress":
        time.sleep(2)
        progress = client.get(status_uri).json()["progressState"]

     

    print(f"SAE Refresh completed with status: {progress}")