LIVE CONNECTIONS - HOW TO MAKE THE MOST OF THEM- USE VIRTUAL TABLES AND REPORT LEVEL MEASURES

[MimiKlimberg]

I work in a company with a fairly complex live connection data model.  In theory, it is great, as you have access to a lot of data, but the reality is far different.  

1) You cannot see the model

2) It has so much data it is hard to get what you want.  

3) Hard to see and understand what relates to what 

IT IS VERY FUSTRATING . . .  And there is no good help for the user who is not creating the model, who does not control the model, but who has to make the reports for the users. 

I think the key is

VIRTUAL TALBES and 

REPORT LEVEL MEASURES 

Can someone point to resources to help those in this situation. 

How best to subset. . . .How best to work with it . . 

It is very difficult. At this point, I have often ended up reverting back to the relational database and then use ODATA. At least, I know what data I have. I don't have to deal with all the data back to 1999. I see the relationships. I create the relationships.  

How can I manage this data set without importing. 

How can I begin to actually make good use of it. 

TOO MUCH of a good thing equals a MESS.

150 TABLES 

with Data going back to 1999

with many MANY TO MANY relationships

MANY non RELATIONSHIPS

I need guidance.  There must be many in this situation. Can someone address this at a fundemental level, creating practices that address the issues associated with this situation. 

I don't know what to do - maybe create a few cross filtered virtual tables.  Could I then write Dax Report level measures against those tables. I don't know but I am looking for an approach that will fundementally change and make it easier to create a report writing using a live connection to a complex model.

[bcdobbs]

I agree with @parry2k it sounds like issues with the model.

I did wonder what version of power bi you're using and what the the architecture you're connecting to is. On newer versions of power bi since they brought on support for composite models you can now see tables and relationships in model view. That however might just be for datasets and azure as.

If you can upgrade power bi desktop and find the model view works I find creating a new view tab in it, dropping a single fact table in and then right clicking to show related tables let's me see the star schema just for that fact table which helps understand what filters what.

[bcdobbs]

Actually thinking about it if you're using live connection you won't be able to see the model. Is connecting with direct query an option?

[parry2k]

@MimiKlimberg not sure what to say, it is more of an issue with the people who are designing the model and not following the best practices. It is like someone giving you a SQL table with one column and separate values by comma and then you try to figure out how to gleen the information out.

I see a fundamental issue with the datasets (ofcourse model) which are making it hard to use, and I'm not sure if it is worth the efforts to use these datasets if these are not production-ready.

I work with many clients where we have fairly complex and large models, and we have solid datasets and super-users/report developers use these datasets to develop reports, and everything works as expected and ofcourse sometimes there are measures which are not part of the model and user can add report specific measures but fundamental of modeling is complete in all respect, taking care of the relationship, performance, etc.

Sorry not a great of answers but I see the problem lies somewhere which you already identified and I feel it is not ready to be used even with some workaround.

Just my 2cents. All the best!