Hi Gurus, i am having an issue with my SSAS Multidimensional Model. My SSAS cube is based on a single oracle table T1. Its a Fact-Dimension table that consists of both measures and dimensions.

Here is what i am doing

1) Create DS(Connection to oracle)
2) Create DSV(Bring in Table T1)
3) Run Cube Wizard
   a) Create 2 measures called Sales Amount, Sales Count fields in Measure Group. The cube created another field called 'T1 Row Count'
   b) Create a Empty Dimension. Pick a Key column(Policy Nbr)
4) Open the Empty Dimension created in 3b) above and drag and drop a dimension called 'State'. Also included a second dimension called 'Policy Nbr' as it asked for a key dimension. Please note that the state dimension is also coming from the same table T1. 
5) Change Dimension processing from 'Custom' to 'Default'
6) Process and Deploy the Model.

Now when i look at the Total sum of (Sales Amount) and Total count of rows, they are exactly matching to the underlying Oracle table. But he issue i am seeing is that when i include the dimension called 'State' then the Sales Amt per state is showing incorrect. A lot of values are going to a new bucket called N/A. Any Advice on how to avoid this?  I chose to Ignore Key Dimension errors when processing the Dimension, is that the reason?

• Edited by konquistador2002 16 hours 11 minutes ago

Found out that the Key Column(Policy Nbr) i am using is not a unique key. So when i am trying to use it in the Cube Dimension Usage, it has more keys in Fact table than in the Dim Table, so its creating a 'N'A' bucket in every Dimension and putting some Sales Amount in that bucket. 

Did i get the issue right?

Any advise on how to fix this? Should i ask the DBA's to add any unique primary key to the table? Or is there any workaround?

Appreciate any help!

Thanks,

Kon

• Edited by konquistador2002 10 hours 52 minutes ago