OLTP Architecture Design

[Anonymous]

hi team

we are in process of developing power bi reports for our business and i need few architectural questions for below :

we have OLTP system with normalized data and the data size is 4GB and we have like 180 tables out of which most of them tables are interrelated . In such case should i go with modelling at power bi ? It seems really compicated where i am uncessarly pulling all the tables into power bi .

we have the full access to the datawarehouse (It's not but we have history for OLTP records) . Where i am thiking to go with below option :

Suppose we have 4 tables

customer

account

dimdate

purchase

As i told all these are raw tables and there is no particlaur like fact and dimensions . Instead of pulling all these tables into powerbi and create realtionship which in my case is not possible where i need to remove dupcliatees and apply business logics.

select p.x,a.customer,z.accountnumber  from purchase p join customer a on a.x=p.x

            join account  z

            join date c

         where -------** some conditions--------***

this will be popualted like fact_transformation table . This what i am hoping to pull into powerbi where i can preety straightforward use the table where i can create the filters from dimension columns and do it .

what you do suggest here on my approach ?

[v-frfei-msft]

Hi @Anonymous,

You’d better not to go with modeling at power BI.

Setting them up in Power BI takes more time. I don't find it quite as easy as doing it in SQL. For example, Power BI doesn't allow you to join two tables together on more than one column. So if you need to join two tables together on more than one column, you need to create another column in Power BI to concatinate the other columns, and then join on that. joins in DAX are costly on performance, so your reports may be slow, which could be a disaster.

Regards,

Frank

[Anonymous]

hi @v-frfei-msft

so you want me do everything at SQL and pull the data right?

[v-frfei-msft]

Hi @Anonymous,

In your situation, Yes!

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank