Facts with different grain from different sources but related

Hi,
I have a situation which I am trying to resolve but not able to
Technologies Using : SQL Server and Analytics Services (SSAS)

Background
I have a flat file which is from the enterprise peoplesoft system and has the followings
GL Account, Company code, total amount

The oltp system sends data into the enterprise ware house and information is processed and stored in a data mart. Here is the logical structure of the information

Company Dim
===========
Comp Key, company code, branch code

Transaction Dim
============
Trans key, Amount Code, Transaction type, Description

Fact
==========
Comp key, trans key, transaction amount

Translation of the peoplesoft file dimension
------------------------------------------
each gl account can be mapped to multiple transactions (amount code + transaction type) in the transaction dimension
each company code/description is mapped to a company code + branch code in the Company dimension


Problem
Now I need to merge the information from both the peoplesoft and enterprise data mart to create another model. GL amount is at company and account level, the transaction amount is at a different level. Business want to drill down from the peoplesoft system and find the corresponding transactions to find the posted difference


Gl company, gl account, total gl amount, total transaction amount
when clicked on gl company/account, drill down to multiple branch , transaction and amount codes and show the individual transaction amount

please let me know how do I do the data model.

I assume you have dates as well. A file containing GL Account, Company code, total amount is meaningless without some date context.

If you need to tie transaction amounts to an account, your transaction facts need an account dimension, otherwise there isn't any way you can match transactions to the totals you have.

Yes, the gl file has the accounting period. Amount from the other transaction system will be summed to this accounting period.
yes, I do agree that my transaction fact need an account dimension. However the transactions are reported at a lower grain and what posted transactions in the GL file is at the higher grain. I do have a mapping table in my system that maps each transaction codes to a higher gl account. My issue is how do I design the model?


Account dimension
===============
level 1, level 2
--------------
gl account key1, null
gl account key2, transaction code 1
gl account key3, transaction code 2

One fact table:
===============================================
gl account, gl amount, transaction amount
1, 100, 0
2, 0, 50
3, 0, 50