NR
Data Dictionary
Model reference · NovaRetail RLS Demo
Model overview
Tables
9
2 fact · 6 dim · 1 security
Total rows
458,685
Across all tables
Date range
2024–2025
731 days of transactions
Schema type
Star
+ hierarchical Employee
Tables & columns
FactSales
Fact 452,948 rows
Transaction-level sales records — one row per line item sold. Grain: sale × product.
ColumnTypeDescription
SalesIDPKTEXTUnique transaction line identifier (T0000001…)
DateFKDATESale date → DimDate[Date]
StoreIDFKTEXTStore where sale occurred → DimStore[StoreID]
ProductIDFKTEXTProduct sold → DimProduct[ProductID]
EmployeeIDFKTEXTSales associate who made the sale → DimEmployee[EmployeeID]
QuantityINTEGERUnits sold (1–5, weighted toward 1)
DiscountDECIMALApplied discount rate (0, 0.05, 0.10, or 0.20)
RevenueDECIMALNet revenue after discount (₺)
CostDECIMALProduct cost for units sold (₺)
ProfitDECIMALRevenue − Cost (₺)
FactPerformance
Fact 4,416 rows
Monthly performance metrics per employee. Grain: employee × month. Covers SalesAssociate and StoreManager roles only.
ColumnTypeDescription
PerformanceIDPKTEXTUnique performance record identifier
EmployeeIDFKTEXTEmployee being measured → DimEmployee[EmployeeID]
MonthFKDATEFirst day of the month → DimDate[Date]
SalesTargetINTEGERMonthly sales target assigned (₺80K–150K)
SalesActualINTEGERActual sales achieved that month
PerformanceScoreDECIMALScore on 0.0–5.0 scale (actual ÷ target × 5, capped)
DimEmployee
Dim 201 rows
Employee master data with self-referencing hierarchy (ManagerID). Critical for hierarchical RLS via PATH() & PATHCONTAINS().
ColumnTypeDescription
EmployeeIDPKTEXTUnique employee identifier (E0001…E0201)
FullNameTEXTEmployee full name
EmailTEXTCorporate email — matches USERPRINCIPALNAME() for RLS
RoleTEXTGM · RegionDirector · CityManager · StoreManager · SalesAssociate · HR · Finance
ManagerIDTEXTDirect manager (self-reference to EmployeeID). Empty for GM.
StoreIDFKTEXTAssigned store (empty for HR, Finance, GM)
CityIDTEXTAssigned city
RegionIDTEXTAssigned region
SalaryINTEGERAnnual salary (₺). Hidden from Finance role via OLS.
HireDateDATEEmployment start date
ManagerPathCALCTEXTDAX-computed pipe-delimited hierarchy path (e.g. "E0001|E0005|E0023")
HierarchyLevelCALCINTEGERDepth in org tree (1 = GM, 5 = Sales Associate)
ManagerNameCALCTEXTFull name of direct manager (LOOKUPVALUE)
DimStore
Dim 34 rows
Store master data with geographic hierarchy (city → region).
ColumnTypeDescription
StoreIDPKTEXTUnique store code (S001…S034)
StoreNameTEXTStore display name (e.g. "İstanbul Park", "Ankara AVM")
CityIDFKTEXTCity reference → DimCity[CityID]
CityNameTEXTDenormalized city name
RegionIDTEXTDenormalized region reference
RegionNameTEXTDenormalized region name
OpenDateDATEStore opening date (2018–2023)
DimCity
Dim 9 rows
Cities where NovaRetail operates.
ColumnTypeDescription
CityIDPKTEXTCity code (C01…C09)
CityNameTEXTİstanbul, Bursa, Kocaeli, Ankara, Konya, Kayseri, İzmir, Aydın, Muğla
RegionIDFKTEXTParent region → DimRegion[RegionID]
DimRegion
Dim 3 rows
Top-level geographic grouping.
ColumnTypeDescription
RegionIDPKTEXTRegion code (R01, R02, R03)
RegionNameTEXTMarmara · İç Anadolu · Ege
DimProduct
Dim 133 rows
Product catalog across 4 categories × 4 brands.
ColumnTypeDescription
ProductIDPKTEXTSKU identifier (P0001…P0133)
ProductNameTEXTHuman-readable product name
CategoryTEXTGiyim · Ayakkabı · Aksesuar · Ev Tekstili
SubCategoryTEXTDetailed sub-category (Tişört, Elbise, Spor, etc.)
BrandTEXTNovaBasic · NovaSport · NovaPremium · NovaKids
UnitPriceDECIMALList price per unit (₺80–1,200)
UnitCostDECIMALCost per unit (55–75% of price)
DimDate
Dim 731 rows
Standard date dimension for time intelligence. Marked as Date table.
ColumnTypeDescription
DatePKDATECalendar date (2024-01-01 to 2025-12-31)
YearINTEGERCalendar year
QuarterTEXTQ1–Q4
MonthINTEGERMonth number (1–12)
MonthNameTEXTJanuary…December
DayINTEGERDay of month (1–31)
DayOfWeekTEXTMonday…Sunday
IsWeekendBOOLEANTRUE if Saturday or Sunday
DimUserSecurity
Security 201 rows
RLS lookup table. Isolated — no relationships to other tables. Filtered exclusively via DAX using USERPRINCIPALNAME().
ColumnTypeDescription
EmailPKTEXTUser email — matches USERPRINCIPALNAME() at runtime
EmployeeIDTEXTLinks to DimEmployee (via DAX, not model relationship)
FullNameTEXTDenormalized for debug display
RoleTEXTDetermines which RLS role applies
RegionIDTEXTUser's access scope — region level
CityIDTEXTUser's access scope — city level
StoreIDTEXTUser's access scope — store level
Model relationships
From To Cardinality Direction Active
FactSales[Date]→ DimDate[Date]Many-to-OneSingle●
FactSales[StoreID]→ DimStore[StoreID]Many-to-OneSingle●
FactSales[ProductID]→ DimProduct[ProductID]Many-to-OneSingle●
FactSales[EmployeeID]→ DimEmployee[EmployeeID]Many-to-OneSingle●
FactPerformance[EmployeeID]→ DimEmployee[EmployeeID]Many-to-OneSingle●
FactPerformance[Month]→ DimDate[Date]Many-to-OneSingle●
DimStore[CityID]→ DimCity[CityID]Many-to-OneSingle●
DimCity[RegionID]→ DimRegion[RegionID]Many-to-OneSingle●
DimEmployee[StoreID]→ DimStore[StoreID]Many-to-OneSingle○
DimUserSecurityNo relationships — filtered only via DAX (USERPRINCIPALNAME lookup)
Note on inactive relationship: DimEmployee → DimStore is marked inactive to avoid ambiguous filter paths between FactSales and DimStore. Employee-store analysis uses DimEmployee[StoreID] as a direct column instead of navigating via relationship.
Calculated columns (DAX)
DimEmployee[ManagerPath]
Pipe-delimited path of managers up the hierarchy. Foundation of hierarchical RLS.
PATH(DimEmployee[EmployeeID], DimEmployee[ManagerID])
DimEmployee[HierarchyLevel]
Depth in the organizational tree (1 = top).
PATHLENGTH(DimEmployee[ManagerPath])
DimEmployee[ManagerName]
Human-readable name of direct manager.
LOOKUPVALUE(DimEmployee[FullName], DimEmployee[EmployeeID], DimEmployee[ManagerID])
Measures reference
Measure Table Format Purpose
Total Revenue_Measures₺#,##0Sum of FactSales[Revenue]
Total Profit_Measures₺#,##0Sum of FactSales[Profit]
Total Quantity_Measures#,##0Sum of FactSales[Quantity]
Profit Margin %_Measures0.0%Profit ÷ Revenue
Sales YoY %_Measures0.0%Year-over-year revenue growth (SAMEPERIODLASTYEAR)
Store Count_Measures#,##0Distinct stores with sales
Employee Count_Measures#,##0Distinct employees
Avg Performance Score_Measures0.00Mean of FactPerformance[PerformanceScore]
Target Achievement %_Measures0.0%Actual ÷ Target across FactPerformance
Current User Email_MeasuresTextUSERPRINCIPALNAME() — RLS debug
Current User Role_MeasuresTextRole lookup from DimUserSecurity — RLS debug
Current User Name_MeasuresTextName lookup from DimUserSecurity — RLS debug
Visible Stores_Measures#,##0Count of stores current user can see — RLS debug
Visible Employees_Measures#,##0Count of employees current user can see — RLS debug
Security implementation notes
Three-layer RLS model:
Static — HR & Finance roles use fixed FALSE() filters to hide sensitive tables.
Dynamic — RegionDirector & CityManager roles filter via USERPRINCIPALNAME() against DimUserSecurity.
Hierarchical — StoreManager role uses PATHCONTAINS(ManagerPath, currentUserEmpID) to filter DimEmployee to the manager's org sub-tree.

OLS bonus: DimEmployee[Salary] is hidden from Finance role via Tabular Editor's Object-Level Security.
NovaRetail RLS Demo · Data Dictionary v1.0 · Portfolio project