I have a data set that has multiple clients data in one database all kept
seperate by a single client key. I want to be able to query transactions
within the database by a number of dimensions such as supplier and customer
status. The problem is that the supplier sits under the client key as does
the status but I can't "connect" these together in a cube. I can have a
dimension of client/supplier and a dimenasion of client/status but I cant
figure how to make these relate (if possible) so that if I pick a single
client to filter on, the dimensions automatically filters to the suppliers in
that client and the statuses for that client as well as just the transactions
for that client. The only thing way I've figured so far is to use security
roles to limit the view on the two dimensions to the clients data, but this
isn't ideal and is prone to error.

Anyone got any idea's, or am I trying to do something that isnt really
possible and simply need to create seperate data-sets for each client from
the source data?

Here's and example data set:-
[Clients]
Client_Id,Client_Name
[Suppliers]
Client_Id,Supplier_id,Supplier Name
[Statuses]
Client_Id,Status_cd,Status_Desc
[Customers]
Client_Id,Cust_Id,Fore_Name,Surname,status_cd
[Transactions]
Client Key,Supplier_Id,Value,Date,Time,Cust_id

I want to be able to slice/dice the transactions by supplier and status for
example, but for this to be within a single client.