Developing Dashboards from FnO Data

[Ashwath_Bala_S]

Hi,

I am developing Power BI Dashboard in Fabric. My flow of Data is from, FnO to Dataverse to Microsoft Fabric (As Shortcut Tables, here data is not present in OneLake, It is present in Dataverse Environment).
My Requirement is to Develop a dashboard on top of it.
I have came to know about two methods:

1. Using SQL End-Point - Creating a visual query, choosing specified columns, replacing values, etc.. and then saving as a view (The required view alone saves in OneLake). If needed we can also create a measure for the same in SQL EndPoint.

2. Using Notebooks on Lakehouse - Using a Notebook, loading tables as df, making transformations and then saving the same (Here the transformed gets saved in OneLake) (We can run notebooks at specified intervals, such that once data gets refreshed in Dataverse, it gets refreshed in Dashboard).

Is there any common persona to use, both Replace Feature (From SQL Endpoint) and the New Calculated Column Feature (From Warehouse).

I would like to know about the Warehouse Method, where I can get like the below mentioned image. If possible, I would like to know if I should use all the tables in OneLake or is there any way to use the transformed tables alone in OneLake.

(I have taken this image from Semantic Model)

Below image is for Visual Query in SQL Endpoint

Kindly assist me with all the possible ways and the solution of creating measures, calculated fields, etc.. all possible solutions with SQL Endpoint Notebooks and Warehouse methods, since fabric is a fresh perspective.

And, if I bring my data into datawarehouse using the "+" options, will that cause storing data into OneLake, or the same will come as a Shortcut tables (I believe data doesn't gets duplicated into OneLake here)

Any Assistance on this will be highly helpful.
Thanks in Advance.

[Anonymous]

Hi @Ashwath_Bala_S ,

Thanks for using Fabric Community.
As I understand you would like to build reports from the data in One Lake.
Did you got a chance to look into this Sematic Model Doc - Default Power BI semantic models - Microsoft Fabric | Microsoft Learn

It helps in creating reports with various options,

Hope this is helpful. Please let me know incase of further queries.

[Ashwath_Bala_S]

Hi,

Thanks for your reply and providing me the resource.

My data is from FnO to Fabric via Dataverse as Shortcut tables.

I have few queries:

1. One of the approaches which I have checked is using a SQL Analytics EndPoint, making changes and saving it as a view (The view gets saved in OneLake).

2. For creating a calculated column, we can use the Visual Query to work on.
    For creating a measure, either with transformed (can use from View) or from shortcut tables.

3. Is this approach of using SQL Endpoint and saving it as a view the best approach, where I get tables as "Shortcut Tables", over creating notebooks and running them at regular intervals.

4. On a short Note, I prefer not to use a Warehouse, since I host a Lakehouse already.

5. Kindly assst me over other methods if available, and I believe, using a notebook in the lakehouse, uses the method of loading it in a "df" and saving it as a "table".

Any assistance on this would be highly helpful.

Thanks in Advance

[Anonymous · Accepted solution]

Hi @Ashwath_Bala_S ,

Apologies for the delay in reply from our side.

You're absolutely right! Choosing the appropriate method for building dashboards from FnO in Fabric depends on several factors, particularly the complexity of your data transformations and your team's skillset.

• For simpler transformations:
  SQL Endpoint: This is a great option for straightforward operations like filtering, aggregating, and joining data. It's user-friendly and doesn't require coding knowledge. However, its capabilities are limited for complex scenarios.
• For complex transformations:
  Lakehouse notebooks: If you need more advanced data manipulation, notebooks offer greater flexibility and power. You can write code to clean, transform, and enrich your data before using it in Power BI. However, this approach requires coding knowledge and might have a steeper learning curve.
  
  

Additional Reference:
Microsoft Dynamics 365 - Finance & Operations AND Fabric AND PowerBI

Hope this is helpful.

[Ashwath_Bala_S]

Hi @Anonymous ,

Yeah it helped me.

Thanks

[Anonymous]

Hi @Ashwath_Bala_S ,

Glad to know that your query got resolved. Please continue using Fabric Community for your further queries.