Best practice tips for multi-level flow design for PowerBI?

Hi all,

I have a data-set with relationships that is best described with the following example:

When a user installs an app, a unique user-id is generated.

Each time the user uses the app, a unique session-id is generated.

For each session of the user, every screen the user visits is recorded together with some other information.

So the raw data could look something like so:

User	Session	Screen
Alpha_123	Ses_1	f1
Alpha_123	Ses_1	f2
Alpha_123	Ses_1	b1
Alpha_123	Ses_1	b2
Alpha_123	Ses_1	e1
Alpha_123	Ses_2	f1
Alpha_123	Ses_2	e1

In the data-set, all information beside what I showed in the table above is initially on the "Screen"-level, information could for instance be time of day, date etc.

Now, I have made a flow where, in PowerBI, I have the opportunity to view information both on a user level (For instance how many purchases did a specific user make), a session level (For instance at which time of the day does most session take place) and at screen level (For instance, showing how a user moved through screens with how long time spend on each screen).

My problem is that the current model was made in many iterations and is therefor quite fragile, so my question is:

What are the best practices, when designing a flow for multiple levels (here three) where detail levels of all three levels must be conserved?

My initial idea was to simply split my input file into 3, and then export 3 tables to PowerBI.

I would like to hear your thoughts, perhabs explained with a couple of examples.

BR Mikkel 🙂

Workflow

Datasets

Input

Output

Common Use Cases

Best Practices

Power BI

Accepted answers

danilang

Hi @miqlliot

I would suggest moving as much of the data prep/analysis as possible into Alteryx. As an example, this workflow calculates summary data and appends the results to each input row while retaining the detail of the input data

The results look like this

Every input row has the summary data appended to it. In the screen shot above you can see the User summary data and some of the Session summary. The workflow produces some Screen level summary data as well. Within Power BI, you have the aggregate data available at the row level, so it's a question of displaying the fields you need based on grouping level you want to display. If you're at the session level, filter for a distinct list of session IDs and display the Sessionxxx fields.

This method moves the calculations for known groupings out of Power BI and the resultant data set can be visualized in almost any tool. Since you still have the original data, you can also build any custom transformations in the visualization platform as well.

Dan

MultiLevelSummary.yxmd

All comments

danilang

Hi @miqlliot

The results look like this

Dan

MultiLevelSummary.yxmd

miqlliot

Hi @danilang,

Thank you so much for the detailed answer, it looks exactly like the type of help I was looking for.

Let me ask some clarifying questions to make sure I understand exactly what you mean, so I can improve how I think about Alteryx.

What you are essentially proposing is to keep everything "in one big table", instead of splitting streams into user level, session level and screen level.

What I see happening when you do this, is that you will have some columns (for instance purchases) where there will be a lot of "Null" fields.
Similarly, you will have some columns (for instance UserTotalPurchases) where there will be a lot of duplicated values.

Here of course, one would have to be careful only to use "UserTotalPurchases" together with the "User" field, and not the "Session" field for instance; I see you have made this quite obvious with naming conventions, giving everything relating to the "Screen"-level no prefix, everything related to the "Session"-level a "Session" prefix and everything related to the "User"-level a "User" prefix.

Do you think this is better than for instance splitting it into three separate tables? so the user-table only has user level data, session level only has session level data etc. Or is it better to keep it all in one big master-table? What do you see as pros/cons here?

Again, thank you so much for the knowledge sharing 🙂

BR Mikkel

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280