Keeping up with (static) Dimension Table when FactTable updates | How do you do it? Best practices?

[Anonymous]

Hey all,

I know the wording of the title is a bit off, and I'll explain why. I have a colleage that created a live report on Power BI Service, which pulls data from one of our system which provides information on Employee hours worked, which client's they've been working on, utilization, which projects, forecasting, capacity, etc. 

So all in all, it's just tracking what clients/projects the consultants are working on. Within the company, there are a subset of clients that have opted in for a aaS offering, meaning that out of the 60 clients we have in the company, 20 of those are managed by my team.

To start, I manually created a dimension spreadsheet which lists all of our clients (20 aaS), and a couple of fields that would be useful for us (which are static) - I created a basic report off of this, but knew that we needed to leverage the live data my colleage is pulling.

Since we recently had this update, I was able to DirectQuery to the dataset from Power BI service to Power BI Desktop. I made sure that the client names were identical, and connected my dimension table (20 distinct clients/manual/static) to the live dataset which has a column 'Client' (60+ clients company wide). Yes, unfortunatly, we're connecting string on string 😞

Everytime our aaS group acquires a new client, I would have to be in the know, and then go and manually add it to our dimClient spreadsheet and fill in whatever fields are there. Is there any decent way of keeping track of when new client poppulates in the factTable (live data)? Better yet, is there an easy way to be able to autopopulate a client from the fact table to my dimension table via a manual trigger? I'm thinking maybe something in Power Automate with a manual trigger?

A drop down menu of all the distinct client names I can choose from, select, then enter fields in Power Automate? I'm just spitting ideas out there, but if anyone has any ideas or insights on a weird situation like this, I'd appreciate it.

If you made this far into my rambling, thank you!

[PaulDBrown]

Ideally you should include a field for team manager at the source. If the Employee works exclusively for each team, you could create measures to group by team manager. If you can actually create tables in Direct Query mode (using DAX or PQ) you could of course create a dimension table including Unique Employees and their manager.

Just out of curiosity, why are you using Direct Query?

[Anonymous]

Thanks for the response. The only reason I'm using Direct Query is because I am not the owner of the dataset - The data is being pulled in via API calls in Python (which was done by my colleague) - So the only way for me to access the updated data, I had to connect to Power BI Service Dataset, which is by default, direct query.

[AlexisOlson]

Just as an FYI, you can connect to a Power BI Service Dataset using import.

Here's an example:

let
    Workspace = "Workspace_Name"
    Dataset = "Dataset_Name"
    DAXQuery = "EVALUATE SUMMARIZECOLUMNS ( DimClient[ClientName], DimClient[ClientID] )"
    Source = AnalysisServices.Database(Workspace, Dataset, [Query=DAXQuery, Implementation="2.0"])
in
    Source

[PaulDBrown]

Just a tought... If each employee is assigned to one team, you could create the dimenaion table for employee-manager in Excel instead of the client sheet. You would only then need to update the employee sheet to cover employee/team changes. Makes sense?

[AlexisOlson]

You could create a DimCustomer table from the LiveData table by taking the distinct customers from that table and then merge in your spreadsheet data to populate the other fields. This will ensure that each customer is in your dimension table even if the other fields haven't yet been populated in the spreadsheet.

[Anonymous]

Yes but if I'm connecting to someone else's dataset via Direct Query - I don't think I can see the data in the Query Editor or data view .. Also - did you mean just joining the tables? If I joined them, how would I manually update those unique fields in the static spreadsheet?

[AlexisOlson · Accepted solution]

Direct Query makes things more complicated but it should still be possible to create a dimension table either as a DAX calculated table--something like SUMMARIZE ( Fact, Fact[Customer] )--or by creating a similar new query in the query editor that links to the same source as the direct query. In the first case, you could do some kind of union or join with your spreadsheet table to get the DimCustomer table to use for the model. Same for the latter; you'd append or merge the two sources together to get a full dimension table.

With any method, you'll need to do a model refresh to update static data additions since you can't DirectQuery a spreadsheet (last I heard anyway).