We are preparing a app and are facing issues while preparing the data model for the same.
Below is the situation we are facing:
We have 6 tables:
1. [ComputerHistory]: This table stores all the computer related details and this is a daily load table, i.e we are loading all the computers and related fields on a daily basis to track the historical changes.
2. [TargetGroupMaster]: This is a Group master table which stores all the groups. A group can have multiple computers in it.
3. [ComputerTargetGroup]: This table stores the relationship between a Group and a Computer.
4. [AllPatch]: This table stores all the patch related details.
5. [ApprovedPatchGroup]: This table stores all the approved patches with Approval Date. A patch gets approved for a target group i.e for all the computers in that target group.
6. [ComputerwiseUpdateStatus]: This table contains the status of Patch and Computer. Once a Patch gets approved it is installed on all the computers, this table contains the same if a Patch is installed on the computer or if it is Not-Applicable for a computer.
Attached is the data model of the same.
Issue we are facing is with respect to TargetGroups, thing is Patch is getting approved for a group but are getting installed on the basis of a Computer which is where we are getting issue and loop is getting formed.
To resolve the issue what we have done is we have created 2 copies of TargetGroupMaster and joined one with ComputerTargetGroup and other with ApprovedPatchGroup.
And due to that we have to create 2 filters on the front end (a. PatchGroup and b. ComputerGroup) having same values and to get the actual numbers users have to select the same group from both the filters.
But users doesn't like this solution and want to have a single filter for Group.
Looking forward for support to resolve the issue.
Thanks for your prompt response. After going through these links only we have decided to have 2 copies of TargetGroupMaster table but it's not working with clients.
If you check the model I have uploaded there's no way we can break the loop as all the links are required to be there that's why we came to the above solution.
Not sure if there's a way I can have a filter in the report which contains data from both the GroupMaster tables or if that's not possible I need a way if I select value from one of the filter automatically same group(s) should be selected in the other filter.
Just to add one more thing that we are using Mashups to present our reports. if this can help resolving the issue.
Is it not possible to combine the tables highlighted in RED in attached image into a single table using either ApplyMap/Join and then refine the data model to have appropriate keys. What are the data considerations, if this cannot be done?
Could you please share sample file, if this solution wont work for you?
Thanks again for your response.
I have merged "ComputerTargetGroup", "ComputerwiseUpdateStatus" & "ApprovedPatchGroup" tables into one and joined ComputerHistory, AllPatch and TargetGroupMaster with this merged table.
Although the volume of data is too much in the merged table but I am able to achieve the result.