The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

Question on Power BI Modeling View Referential Integrity and Normalization

Question on Power BI Modeling View Referential Integrity and Normalization

0
Votes
1
Answer

Hi –
So, say we go to a company as a contractor to help them design a few views…  I have a question for 2 scenarios for  import vs direct queries.
 

  1. Say they give us a bunch of tables and we import the file, fine…  do we need to normalize this data and check it for referential integrity….  Does this even matter in Power BI
  2. . Say we are working for an organization and the systems admins connect us to a SQL db and an Oracle db and some sales people give us a few tables.. maybe a salesforce connectivity… how do we know they normalized the data to match each other, before we start building…  I “think” they have to talk and normalize before we connect first through the architect… that seems logical to me, but let’s say we can’t get that consensus, if the data is small in either of these situations than we need to ensure referencial integrity, clean it, vet it, simplify throw out stuff and then normalize it.. right?  In theory… Right?  Do I understand right?
1 Answer

365 Team
0
Votes

Power BI is a data visualization tool, it requires relational data so it can connect based on primary – foreign key relationships. The data needs to be normalized, you can use some of the PowerQuery functions to remove duplicate rows, specify how to handle null values, etc, but only if you know what conditions need to be met for each table, and things might get messier as you get more data. Power BI can handle data imports from various sources, but it needs to be normalized. Have you looked for a data warehouse?

Hi, yes. Thank you for the response. You make perfect sense to me. For purposes of the school, I want to share my experience so you can help teach students to be most effective. Co. I worked for took a power point only person, no database, no previous analytics skills, company leaders not conceptually realizing how important database design is to any and all analytics credibility. That person leaned on existing programmers again and again and their architects to “link” sources, move data around as the company was a result of many, many M&A sequences. The data was purely a mish mash of similar product lines and suites due to an M&A’s- the operational goal was to streamline it into one set of product offerings as a consequence of multiple company acquisitions. However they leadership decided to use Power BI to deliver revenue analysis across all these databases having no concept of what drives db design and architecture. (One guy knew out of state but he worked on a clean area) Interesting, no? 😉 Months went by…. The master architect left, he was quite bright but I think most likely frustrated. I did ask the master architect and he told me there were several data warehouses and he was envisioning a master datalake (idk?). He had multiple teams oracle, SAP, Salesforce, SQL and more… world wide.. they had some nonrelational db… but the business folk didn’t fully grasp what this means I think. I knew bc my school pushed me into a DB class who was wonderful with concept. My experience was plan building, finance, operations forecasting, etc. . Anyway the owners brought in outside consultants. It’s so important, so important to link this for any company’s strategy. I think you can go at both ways. Learn technical than business than sky the limit for the right person. There is more than one way to skin a cat. THANK YOU!

5 months