I can comment on Power BI part.
The key issue here is that Power BI requires Dimensional Model, not relational one. There is a huge difference.
As described, the model from the book is not suitable for BI tools, it must be redesigned. For example, table "Weather" in the book is presented as a "dimension", while in reality it must be a fact table (similar to table "Flights"). As a result, "Flights" and "Weather" should never have direct connections - they must share common dimensions, such as:
Similarly, multiple keys and multiple connections between tables are very rare exceptions and are frowned upon (usually, they are indications of design mistakes). In a properly designed model, you should never see them.
If you want to understand the issue more, read this book:
Star Schema Complete Reference
To answer your Q3 specifically, in dimensional modeling "Flight" (I assume it's flight number) is called a "degenerate dimension". Normally, it would have been a key to a dimension table, but if it's absent, it stays in a fact table as an orphan key. Such situation is common for Order numbers, Invoice numbers, etc.
Overall, you are on the right track - if you figure out how to transform the model from the book into a proper star schema, and then use it in R and PowerBI, you will be impressed with the new capabilities - it's worth it.
The way it usually works:
Basic example (using T-SQL). Let's say you have the following 2 tables:
To Swap keys:
OLTP.Sales s LEFT JOIN Dim.Contract c ON s.Contract_BK = c.Contract_BK
-- Test for missing keys
Contract_SK IS NULL
On a side note, I believe you have some mistakes in your design.
If you need more information on this, I recommend this book:
Star Schema The Complete Reference
[Edit to answer a follow-up question]:
I did not mean to remove ClaimNbr field from Claim dimension. I suggested that you don't need such dimension at all.
This might be a bit hard to digest, but consider the following. "Claim" is essentially a container for information (same as "Invoice", "Order", etc). If you move all usefull pieces of data to their relevant dimensions, there should be nothing left but an empty container.
For example, let's assume that your OLTP claim table contains the following fields: Claim Number, Report Period, Claim Description, Claim Name, Contract Number, Claim Amount. You can model them as follows:
That leaves 3 fields: Claim Number, Claim Name and Claim Description. At this point, some designers create dimension "Claim" and park these fields there. As I mentioned before, this is a mistake, because you will then have as many records in your dimension as in your fact table, leading to serious problems.
A better design is to leave these fields in the fact table. Claim Number becomes a "Degenerate dimension" - a business key to "empty" (non-existent) dimension. Essentially, it's just an ID for an information container, like invoice number, order number, etc.
Claim Name and Claim Description also should stay in the fact table and become "non-numeric" (non-additive) facts. If you need to display them in a report, it's easy to do, and you can count them, do conditional logic on them, measure their length, etc.
Another way of looking at this: dimensions are usually used to "slice" (disect) facts BY some attribute/field. For example, "Sale Amount by Country", "Product Costs by Plant Location", etc. But you can't slice by descriptions, notes, or other free text - it makes no sense.
What if your descriptions or other claim attributes are structured? For example, if they are used to categorize/classify your claims? In that case, they are not a free text, they are an attribute that belongs to a dimension. For example, you can design dimension "Claim Type". Or "Claim Status". Etc. If there are too many of these little attribute fileds, you can combine them into what's called a "junk" dimension (aka "Profile" dimension), i.e., dimension "Claim Profile". Such designs are clean and efficient.
Read more on junk dimensions here