How to design model and relationship from snowflake views

[Gopal_PV]

Hi Folks,

I am having source as Snowflake datamart views. The business have created multiple views on specific logic. Now i want to created reports on that. 

Can you please help me on how to give relationship between these tables using below sample data . Can you please help me on the modelling. 

Inputdata view from snowflake

				Date View
CALENDAR_DATE	YEAR	MONTH	DAY	YEAR_MONTH	DATE_STR	DAY_OF_WEEK_NAME	DAY_OF_WEEK_NUMBER
1/1/2015	2015	1	1	2015-01	1/1/2015	Day	4
1/2/2015	2015	1	2	2015-01	1/2/2015	Day	5
1/3/2015	2015	1	3	2015-01	1/3/2015	Day	6
1/4/2015	2015	1	4	2015-01	1/4/2015	Day	0
1/5/2015	2015	1	5	2015-01	1/5/2015	Day	1
				Units View
YEAR	MONTH	ACCOUNTID	NAME	BEGINNING_OF_PERIOD_UNITS	NEW_UNITS	EXPANSION_UNITS	CONTRACTION_UNITS
2020	5	13	Netflix	0	3	0	0
2020	5	14	Hotstar	0	0	0	0
2020	6	13	Prime	3	0	16	0
2020	6	14	Voot	0	0	0	0
2020	7	13	Youtube	19	0	0	0
2020	7	14	Sony	0	0	0	0
2020	8	13	Aha	19	0	1	0
2020	8	14	Mx Player	0	0	0	0
				Screening View
YEAR	MONTH	ACCOUNTID	NAME	BEGINNING_OF_PERIOD_SCREENING	NEW_SCREENING	EXPANSION_SCREENING	CONTRACTION_SCREENING
2021	4	18	Liv	0	0	0	0
2021	4	22	Netflix	0	0	0	0
2021	5	18	Hotstar	0	0	0	0
2021	5	19	Prime	0	0	0	0
2021	5	22	Voot	0	0	0	0
2021	5	23	Youtube	0	0	0	0
2021	5	24	Sony	0	0	0	0
2021	5	25	Aha	0	0	0	0
2021	5	26	Mx Player	0	0	0	0
				Subscription View
YEAR	MONTH	ACCOUNTID	NAME	BEGINNING_OF_PERIOD_SUBS	NEW_SUBS	EXPANSION_SUBS	CONTRACTION_SUBS
2021	4	18	Liv	0	498	0	0
2021	4	22	Netflix	0	142.5	0	0
2021	5	18	Hotstar	498	0	7.5	0
2021	5	19	Prime	0	49	0	0
2021	5	22	Voot	142.5	0	1.5	0
2021	5	23	Youtube	0	1932	0	0
2021	5	24	Sony	0	99	0	0
2021	5	25	Aha	0	99	0	0
2021	5	26	Mx Player	0	109.5	0	0
				Payments View
YEAR	MONTH	ACCOUNTID	NAME	BEGINNING_OF_PERIOD_PAYMENTS	NEW_PAYMENTS	EXPANSION_PAYMENTS	CONTRACTION_PAYMENTS
2021	4	18	Liv	0	1126.58	0	0
2021	4	22	Netflix	0	208.2	0	0
2021	5	18	Hotstar	1126.58	0	224.76	0
2021	5	19	Prime	0	179.4	0	0
2021	5	22	Voot	208.2	0	9.6	0
2021	5	23	Youtube	0	3553.59	0	0
2021	5	24	Sony	0	0	0	0
				AAS View
YEAR	MONTH	ACCOUNTID	NAME	BEGINNING_OF_PERIOD_PAYMENTS	NEW_PAYMENTS	EXPANSION_PAYMENTS	CONTRACTION_PAYMENTS
2021	4	18	jio	0	1126.58	0	0
2021	4	22	Liv	0	208.2	0	0
2021	5	18	Netflix	1126.58	0	224.76	0
2021	5	19	Hotstar	0	179.4	0	0
2021	5	22	Prime	208.2	0	9.6	0
2021	5	23	Voot	0	3553.59	0	0
2021	5	24	Youtube	0	0	0	0
2021	5	25	Sony	0	541.72	0	0
				TotChargesRevenue View
YEAR	MONTH	ACCOUNTID	NAME	BEGINNING_OF_PERIOD_TOTALREVENUE	NEW_TOTALREVENUE	EXPANSION_TOTALREVENUE	CONTRACTION_TOTALREVENUE
2018	8	15	jio	0	116.38	0	0
2018	8	30	Liv	0	1195.5	0	0
2018	8	35	Netflix	0	38.62	0	0
2018	8	41	Hotstar	0	100	0	0
2018	8	43	Prime	0	100	0	0
2018	8	58	Voot	0	3540	0	0
2018	8	117	Youtube	0	87.39	0	0
			Sony

[v-agajavelly]

Hi @Gopal_PV ,

Just following up to see if you had a chance to try reshaping your snowflake views into a star schema as outlined above. Were you able to set up the date and account dimensions and link everything up cleanly in the model?

Let us know how it's going or if you'd like any sample M-code, DAX formulas, or help troubleshooting specific parts. Happy to assist further.

Regards,
Akhil.

[v-csrikanth]

Hi @Gopal_PV 
Thanks for reaching out to the Fabric community.
To get all of your Snowflake views working together smoothly in Power BI, you can reshape them into a classic star schema:

Create a proper Date dimension:

• In Power Query, point at your Calendar Date view.
• Rename the date column to DateKey (type “Date”) and keep only the extra fields you actually need (Year, Month, MonthName, DayOfWeek, etc.).
• Load that table and, on the Modeling ribbon, mark it as your Date table.

Build one Account lookup table:

• Pull in each of your metric views (Units, Screening, Subscription, Payments, AAS, TotChargesRevenue).
• In each query, remove all columns except ACCOUNTID and NAME.
• Append them all together, remove duplicates, and call the result DimAccount.

Turn each view into its own fact table:

• In the Units query (and repeat for the others), add a custom column:

DateKey = #date([YEAR], [MONTH], 1)

• Hide or delete the original YEAR/MONTH columns if you like, and rename your measure columns (e.g. NewUnits, ExpansionUnits).
• Load each one into the model under a new name (FactUnits, FactScreening, etc.).

Hook up your relationships:

• In the diagram view, drag DimDate[DateKey] onto each fact table’s DateKey (one-to-many, single-direction).
• Then link DimAccount[ACCOUNTID] to each fact table’s ACCOUNTID.
• Every relationship should flow from dimension → fact, not the other way around.

Build your visuals off that star:

• Always use fields from your dimensions (Year, Month, Account Name) on your axes, slicers, or filters.
• Pull your measures (for example SUM(FactUnits[NewUnits])) straight from the fact tables into charts or cards.

With this pattern you’ll avoid accidental inner joins, keep every row of your metrics, and be able to slice by date or by account without lookup failures. Let me know if you’d like any sample M-code or DAX formulas to get it working!

Thanks 
Cheri Srikanth
CST_Community Team

[speedramps]

It is best pratice to convert year and month to a date key

eg date = 01 / month / year

Then merge all the similare fact tables into one multi-purpose fact table 

Please click thumbs up and [accept solution]

[Gopal_PV]

Hi @speedramps 

Thank you for your reply. But My views are having multiple sql logics are included with business conditions. I cannot merge. Can you please help me with what is the relationship i need to give between the views table in power bi model. 
If you can help me same kind visual represent would great help for me.

Thank you for your time