March 2, 2015 at 5:44 pm
Hi
Hopefully someone can provide some assistance. I've a DAX measure query and/or a DW structural issue. (I looked around for a Tabular Module Area and didn't see on, so if I've placed this in the wrong forum, please let me know.)
I'm designing a new warehouse, so I have control to change structure if required which shall be reported on via a Tabular cube. The business process being molded relates to providing funding (money) to Clients and who then draw down on it.
The main points of the process are as follows:-
• A Client applies for funding (money)
• If the submission is approved a Funding Project is created and Offered. Whilst this process contains numerous conditions and the key part of this process are the Date the Offer is made and the value (money) of the offer.
• The Client, will either Accept or reject the Offer. I'm currently only dealing with Accepted Offers. The key parts of this are the Date of Acceptance and the Value. The Accepted Value may be different than the Offered value.
• The Client can now Claim (Draw down against the Accepted Value), numerous times until the balance is 0. The Client submits a Claim, which then becomes a Payment Request.
• Many payment requests can make up a Claim and each Payment Request will have a Payment Request Date and Value (never exceeding the claim value).
• Any payment request can in turn be made into multiple Payments each with their own respective Payment Date and value.
Note: Multiple Payment Request and Payments against a Claim are based on numerous business related factors.
As can be seen, Offered and Accepted information are single entities tied to a Project. A Project can have many claims, which can have many Payment Request and many Payments.
My Users want to be able to find all Projects Offered in Year Y and Approved in Year X and show all other information Account Information (whats been claimed, requested, paid). Which works fine as I get this as it’s a 1 to many relationship.
The problem I'm trying to solve is when slicing the other way around. Show all Projects that have a Payment in Year Y or Payment Request or Claim in Year Y. Or a Payment in Year Y and a Claim in Year X. They want to see all account information, but when I add Offered or Accepted Values it shows for all Projects. This make sense as it’s a many to one relationship this time around but not the results I want. To me, I think I need to somehow filter out the unique Project ID, based on the Many Slicers and filter out the Sum formula of Approval and Offered Amts in the Fact Project Table, based on how i would achieve the result via tSQL. But I'm really not sure.
I've structured the data two different way, but I haven't had any successful. If anyone can offer guidance I would be most great full.
Many thanks
Attempt 1:
Dimension Client: Client ID, Name, Address
Dimension Project: Project ID, Project Name, Offer Name, Accepted Name
Dimension Activities: Activity ID, Activity Type (where Activity Type indicates Claim, Payment Request, Payment)
Date Dimensions are: Approval Date, Offered Date, Claim Date, Payment Request Date, Payment Date
Fact Table Project: Client ID, Project ID, Approval Date ID, Offered Date ID (as these are all on a one to one basis(
Fact Table Activities: Client ID, Project ID, Approval Date ID, Offered Date ID, Activity ID, Activity Date ID, Activity Amt
Attempt 2:
Dimension Client: Client ID, Name, Address
Dimension Project: Project ID, Project Name, Offer Name, Accepted Name
Dimension Claim: Claim ID, Claim Status
Dimension Payment Request: Payment Request ID, Payment Request Status
Dimension Payment: PaymentID, Payment Status
Date Dimensions are: Approval Date, Offered Date, Claim Date, Payment Request Date, Payment Date
Fact Table Project: Client ID, Project ID, Approval Date ID, Offered Date ID, Approval Amt, Offered Amt (as these are all on a one to one basis)
Fact Table Claim: Client ID, Project ID, Approval Date ID, Offered Date ID, Claim ID, Claim Date ID, Claim Ref, Claim Amt
Fact Table Payment Request: Client ID, Project ID, Approval Date ID, Offered Date ID, Claim ID, Claim Date ID, Payment Request ID, Payment Request Date ID, Payment Request Ref, Payment Request Amt
Fact Table Payment: Client ID, Project ID, Approval Date ID, Offered Date ID, Activity ID, Claim Date ID, Claim Date ID, Payment Request ID, Payment Request Date ID, Payment ID, Payment Date ID, Payment Ref, Payment Amt
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply