1

I am trying to create northwind database star schema.. this is what i create please let me know is this correct or not

and one more thing is that i want to create primary key icon .. like in suppliers table suppliers id is primary key so i create icon like this

image_PK

image star scehma

Bakhtawar Ashiq
  • 63
  • 1
  • 9
  • 1
    That's a "snowflake" rather than star as not every dimension is joined directly to the fact. However this is all just terminology. If it fits your business need it's fine – Nick.Mc Nov 13 '19 at 08:08
  • yes suppliers and cateogries is not joined with fact table "orders" however i want to join these 2 ID with fact .. but i did not find these 2 fields in orders table .. i download northwind database and in orders table i did not find these 2 fields in orders table so how i joined these 2 fields in fact ? – Bakhtawar Ashiq Nov 13 '19 at 08:16
  • If you strictly want to follow star schema, you need to adjust the data loading process to make Products table include denormalized Suppliers and Categories attributes inside it. – Kosuke Sakai Nov 13 '19 at 08:45
  • but this is just by default database.. can i share file with you ? – Bakhtawar Ashiq Nov 13 '19 at 09:07
  • ok .. i create the ID field in order table and when try to create relationship between order-category or order-supplier this shows an error check images .. model https://imgur.com/NCEQ9kl and error image https://imgur.com/undefined @KosukeSakai – Bakhtawar Ashiq Nov 13 '19 at 09:25
  • Oh, you cannot add SupplierID and CategoryID in Orders table. It makes ambiguous relationships in the model. Instead, you can add CategoryName, SupplierCompanyName, or any relevant columns you will need for analysis in *Products* table. – Kosuke Sakai Nov 13 '19 at 09:40
  • why this created ambiguous relationships ? – Bakhtawar Ashiq Nov 13 '19 at 09:55
  • this is the problem .. in product table they have only category id and supplier id not product name and compnay name.. – Bakhtawar Ashiq Nov 13 '19 at 10:02
  • If you create additional relationship between Categories and Orders, there are two paths from Categories to Orders; `Categories -> Orders` and `Categories -> Products -> Orders`. This is called an ambiguous model, because it is not obvious for Power BI engine how to propagate the filter context. – Kosuke Sakai Nov 13 '19 at 10:23
  • so what should i do to get category name and company name in order table ? – Bakhtawar Ashiq Nov 13 '19 at 10:27
  • It is most typical Products table has only CategoryID and no CategoryName in DWH. You can use Power Query to attach CategoryName to Products table. – Kosuke Sakai Nov 13 '19 at 10:29
  • will you please do it if i send you power bi file ? – Bakhtawar Ashiq Nov 13 '19 at 10:33
  • hello @KosukeSakai now i add category and company name in product table but still there is an error in relationships – Bakhtawar Ashiq Nov 13 '19 at 10:58
  • OK, let us see the file. – Kosuke Sakai Nov 13 '19 at 11:22
  • check this link @KosukeSakai https://www.dropbox.com/s/3eqqu88bewlvimg/test_fle.pbix?dl=0 now in this i remove category table and all fields of category table are now in product table.. now the problem is supplier table is not connected with fact table.. please look into this – Bakhtawar Ashiq Nov 13 '19 at 11:29
  • It looks you have successfully attached Supplier CompanyName in Products table. You may also attach Supplier Country, Region, etc. And now you will not need Suppliers table any more. – Kosuke Sakai Nov 13 '19 at 17:56
  • yes but i want to link Supplier table with order_Fact table .. which is i unable to do that @KosukeSakai... but supplier is completely different table and product is completely different.. so why i add all fields of supplier in product table ? – Bakhtawar Ashiq Nov 15 '19 at 04:36
  • In that case you may remove the relationship Suppliers -> Products, and create Suppliers -> Orders instead. Since supplier is uniquely identified by product in this model, you may choose either option. It's totally up to your choice. – Kosuke Sakai Nov 15 '19 at 04:47
  • yes i am trying this already but the point is SupplierID is come from Products table so unable to remove SupplierID from products table.. so because of this products table create relationship with Supplier @KosukeSakai – Bakhtawar Ashiq Nov 15 '19 at 07:35
  • You should be able to remove the relationship between Suppliers and Products while retaining the Products[SupplierID] column itself. In that case, Products[SupplierID] still exists but of no use. It would be even better to remove those redundant columns in data loading process (typically using PowerQuery). Anyway, I don't like to continue this topic here because it is getting to look like a discussion. You may create a chat room and invite me if you want. – Kosuke Sakai Nov 15 '19 at 07:46
  • will you please do that @KosukeSakai – Bakhtawar Ashiq Nov 15 '19 at 07:55

0 Answers0