Designing a Business Intelligence Solution for Pharmaceutical Sales Tracking and Commission Calculat

Fact tables:
- Things that are recorded like actual sales and budget, this can then contain foreign keys to your dimension tables

Dimension tables:
- Think of this as the best way to trim down your fact tables, for example could Distribution Channels be one (or multiple) fact tables

Measures
- Generally if raw data contains calculated fields that you could recalculate given other fields, it's safer to do them as measures. I.e. % is usually a flag that it should be a field so it can be calculated dynamically assuming you have the fields that make up the numerator and denominator.

I pushed this through AI and refined it a little to give you a starting point (though generally I would suggest Measures should be in their own table.

Fact Tables:

1. FactSales

   • Measures:
     • SalesAmount (actual revenue)
     • QuantitySold
     • CommissionAmount
   • Foreign Keys:
     • DateKey
     • ProductKey
     • BusinessUnitKey
     • MedicalRepKey
     • DistributionChannelKey
     • CustomerKey
     • SalesTypeKey (Primary/Secondary/Wholesale)

2. FactBudget

   • Measures:
     • BudgetAmount
   • Foreign Keys:
     • DateKey
     • ProductKey
     • BusinessUnitKey
     • MedicalRepKey (nullable)
     • DistributionChannelKey (nullable)
     • CustomerKey (nullable)

Dimension Tables:

1. DimDate

   • DateKey (PK)
   • FullDate
   • Day
   • Month
   • MonthName
   • Quarter
   • Year
   • IsWeekend
   • FiscalPeriod

2. DimProduct

   • ProductKey (PK)
   • ProductID
   • ProductName
   • TherapeuticCategory
   • Formulation
   • Strength
   • PackageSize
   • IsActive

3. DimBusinessUnit

   • BusinessUnitKey (PK)
   • BusinessUnitID
   • BusinessUnitName
   • Region
   • Manager

4. DimMedicalRepresentative

   • MedicalRepKey (PK)
   • EmployeeID
   • FullName
   • HireDate
   • Territory
   • CommissionRate
   • ManagerID

5. DimDistributionChannel

   • DistributionChannelKey (PK)
   • ChannelID
   • ChannelName (Pharmacy/Hospital/Export etc.)
   • ChannelCategory

6. DimCustomer

   • CustomerKey (PK)
   • CustomerID
   • CustomerName
   • CustomerType
   • Location
   • CreditRating

7. DimSalesType

   • SalesTypeKey (PK)
   • TypeName (Primary/Secondary/Wholesale)
   • CommissionRules

Hi Yakout ,

answer is yes to both questions.

Best

FB

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI