Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[William Skelley]

Hello:

My question has to do with the process flow for initially assigning the primary and foreign keys. I feel very comfortable how to create indexes and use power query to set up course work scenarios but when you approach a data warehouse situation,  is the assigning and maintenance of these keys with the Power BI developer ( me ask data warehouse to implement them) or is it proper to ask that part to be dome further upstream?
Apologies for the run-on sentence!

I was thinking the initial keys would be on my end but could use some input on how to approach in the beginning.

Thank you in advance.

Best regards,

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————

[Audrey Abbey]

Primary keys and foreign keys should be set up in the data warehouse, by the SQL Developer (or team of devs) who designed and built the data warehouse.
The BI developer should not have to ask for this – it is a basic design principle for both OLTP (transaction) and OLAP (analysis) databases.

If the BI Developer is involved in the design of the data warehouse, then yes, they would be involved in researching the source system to identify the candidate key(s) that could be used as a PK.

——————————
Audrey Abbey
SR. BI Developer/Analyst
LeapFrogBI
Portland OR
——————————
——————————————-

[William Skelley]

Hi Audrey:

Thank you very much for responding to me! Very helpful!

Best regards,

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-

[William Skelley]

Hi Audrey:

I was wondering if I could ask you this question as you and Armando were very helpful to me before. Thank you in advance if you can give me any input on this. My question is directly below…

Best regards,

Bill S

Hello PBIUG:

I have a more technical question concerning a situation I have right now. I am helping(as a contract employee) a smaller company get up and running on Power BI. They have given me access to their VPN but no one on their side ( they have IT consultants) are familiar with Power BI. Their view is that they have to install Power BI Desktop on their own internal server for this to work out. A smaller issue is they have asked me to call my internet provider and ask to change my IP address 10 10.a (it starts with 192). This last pat is not super-important.

My question is what is really necessary for me to access their SQL transactional dbase? I understood as long as I had the server name and database name I would be OK, later using me login credentials of username and password. Does the company really have to install the product on their server as the only developer would be me, a non-employee  working remotely?

Since I’m sensitive to being paid, etc for work is the only way for the product to be installed on their server?

If I were to buy my own server, would that change how the connection would go?

I will do research on this but thought I could get some useful feedback from the community on how I should be thinking about this and what to communicate back with.

Thank you very much.

Best regards,
Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-

[Armando Lacerda]

Hi ,

No, there is not need to install Power BI Desktop in any server. As the name implies, it is a desktop (end user) software. It is not intended and should not be installed on servers.

You are correct. All you need is credentials to the database instance either through integrated credentials or SQL login. Power BI supports both.

The IP address requirement is funny. I’ve seen this before and I usually help the client to fix their routing tables.

Good luck with the project and happy reports! :)?

——————————
Armando Lacerda
Senior Cloud and BI Architect
San Francisco CA
——————————
——————————————-

[Armando Lacerda]

Hi Bill,

I am not clear by reading your question whether you’re asking about Power BI or external database storages. 

On the index topic, If we are talking about external database engines that your dataset read from, the DBA should look into the queries issued by Power BI to collect data and create indexes accordingly. This is even more important when you’re doing direct query datasets. An important reminder is that we don’t create indexes in Power BI. The Veripaq engine behind Power BI creates column store index for every column in the model. We don’t have to worry about that from a Power BI stand point. 

On the primary and foreign keys topic, it is very usual for the model in Power BI to reflect the model in the underlying database. But it is not mandatory and sometimes not possible. For instance, if the underlying model uses composite keys (keys formed by more than one column in the table) you will have to make up surrogate keys since Power BI does not support composite keys. 

The bottom line is the PK and FK in Power BI are designed by the Power BI developer. He or she should consider granularity reduction and denormalization from the original model. And it does not need to follow the original model in the data warehouse. This is specially true for very large data warehouses with hundreds of gigabytes of data.

——————————
Armando Lacerda
Senior Cloud and BI Architect
San Francisco CA
——————————
——————————————-

[Audrey Abbey]

Hi Armando,

In Power BI, there is a Power Query transformation called “Index”.
It adds a column to the query with a sequential number.
So it is like adding an auto-incrementing surrogate key in SQL Sever.
That is what he is talking about – not the database indexes.

——————————
Audrey Abbey
SR. BI Developer/Analyst
LeapFrogBI
Portland OR
——————————
——————————————-

[Armando Lacerda]

Hi Audrey,

Thanks for the clarification. I am familiar with the index feature in Power Query. I did not relate that with the original question since it is referring to data warehouse, PKs and FKs. 

In this light I would highly recommend not to create indexes, specially for fact tables on large datasets. In small datasets it is just extra work but for large datasets it can have bad side effects. 

As much as possible the keys should come from the underlying data source. 

Thanks again, Audrey.

——————————
Armando Lacerda
Senior Cloud and BI Architect
San Francisco CA
——————————
——————————————-

[William Skelley]

Hi Armando:

Thank you too for giving me a nice response. Between Audrey and you, you have abled me to have that conversation. The client has not mentioned this issue (where assigning get done) and I took their data and assigned indexes ( F & P keys) to show them the model and to discuss next steps. 

From your answers it seems like a really important component to align on.

I’ll try to mark both answers as best.

Thank you.

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-