Alternative to table-valued function in MSSQL

[BrzDaq]

Hi all,

looking for some suggestion for following problem:

I have an SQL database for a project where i need to compare two tables where one of them is a calendar and the second one is a set of records created by users every day. I compare them user by user and end up with a final table where missing dates are shown along with the user id basically.

Now the thing is when I want to replicate this in Dataverse I simply dont know about any easy way to do this, since the final table is made by table-valued function and then created as a view in Database.

I thought that maybe some every day PowerAutomate could run and give me some table as a result but this feels cumbersome.

Do You guys have any idea how to approach this?

Thanks in advance for all Your suggestions 🙂

[Anonymous]

Hi @BrzDaq,

why are you using a TVP in the first place? What inputs do you need to justify it being a TVP instead of a simple view?

[BrzDaq]

Hi @Anonymous,

sometimes only a push is enough for a one to start thinking differently. I feel that if I simplify things it will work with just a view.... 

Thanks for this 🤓

[Anonymous · Accepted solution]

Exactly @BrzDaq,

a view could be simpler and do the job anyway!

Supposing you are accepting the UserId as input, you could rewrite using that using set-based code and define your result inside a single query, for all users.

In these "set-based rewrite" scenarios, sometimes it comes handy to try using "lateral joins" (in T-SQL search for "cross apply" and "outer apply"), at least as a first approximation to get across the finish line - even if they're not usually the most performant way to accomplish a task. After that you can review the code again as needed and optimize for performance as you need.

Cheers,

Davide

---

If my previous post solved your question, please consider clicking on "Accept as solution" to help the other members find it more quickly. Thanks! 🙂

[BrzDaq]

Hello @Anonymous once again,

when thinking about this do You have any idea how to substitute cross join in Dataverse? I really need the view on data layer and not calculate it in the app every time. I really feel lost coming from MSSQL background and try to do data model in Dataverse... 😞 many-to-many relationship doesnt really do it because I cant create a view with related records from each of the tables... Any advice is appreciated!

[Anonymous]

Wait, maybe I misunderstood your scenario from your first message.

If you have your data inside Dataverse and need a quick and straightforward way to query your data and see the results inside SSMS or ADS, use the TDS endpoint! You can query your Dataverse tables just as regular SQL Server tables, directly from SSMS/TDS/PowerBI/...

If you have your data inside SQL Server and need PowerApps only as a FrontEnd, I think Virtual Tables would be the right choice.

Otherwise, if you are migrating the entire solution inside Dataverse, that's a little bit more complicated.

Which scenario is yours? Am I missing something? How much stale data can the solution tolerate?

Cheers,

Davide

---

If my previous post solved your question, please consider clicking "Accept as solution" to help the other members find it more quickly. Thanks! 🙂

[BrzDaq]

In Your last but one sentence you are correct 😄 Iam kind of trying to migrate entire solution to Dataverse and finding ways to do that... But right now its more of a struggle and I see more problems than solutions 😄

[Anonymous]

Do not despair: there’s a solution for everything!

About your question, consider that Dataverse is absolutely an OLTP system, not a OLAP. If you need to perform complex set-based operations as part of your app functionality, I’d suggest going out from Dataverse (eg TDS endpoint or Data Factory) and then back into Dataverse with a virtual table or a table for final result storage.

The embedded methods to apply calculations (flows, plugins, workflows, ..) are really designed for simple, single row operations.

Also, there’s nothing inherently wrong in having a triggered batch process that refreshes some data, especially in highly transactional workloads where you want fast inserts and updates!

Best,

Davide

---

If my previous post solved your question, please consider clicking "Accept as solution" to help the other members find it more quickly. Thanks! 🙂

[BrzDaq]

Thank You very much for your kind advices in the first place @Anonymous.

Going out of Dataverse kind of making things more complicated than I want them to be. But maybe thats the way it is... To put it simply - I have a table with users called Users, then there is a table with dates called Calendar(lets suppose dates for next 5 years). What do You think is a most simple solution to this view in SQL.

SELECT userName, calendarDate FROM Calendar

CROSS JOIN Users

Another complication is that Fetch XML doesnt support cross joins or any kind of cartesian product. Maybe its time to rethink the whole model at this stage...

[Anonymous]

Hi @BrzDaq,

I'm unaware of any way to perform cross-joins in Dataverse: again, that's an OLTP system!

As you correctly pointed out, you can either go "outside" the system and use an external SQL engine (I use this therm rather loosely on purpose) and then back inside, or restructure your logic so that it becomes less broad (User/Time dimensions), set-based and more on-demand and procedural instead.

Either way is equally viable, and it mainly depends on the specific nature of the logic itself.

At this point, I suggest moving the discussion to DMs. Feel free to ping me there! 🙂

Best,

Davide

---

If my previous post solved your question, please consider clicking "Accept as solution" to help the other members find it more quickly. Thanks! 🙂