This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(131.20000000000002, 11%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAJ%3C/text%3E%3C/svg%3E)
Hi All,
In my cube i have a location dimension. It has the following attributes
LocationKey>EntityKey>UnitKey>LocationName>City>State.
LocationKey is unique.
The Issue is there is a city with the same name under two different states. When i browse and want to see the amount paid, it is showing one state's city's amount under a different state's city. Please advice.
Thanks
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
There's more to the problem than meets the eye.
If the > symbols denote attribute relationships, then the attribute keys already distinguish the two cities from different states, else the dimension wouldn't process with a duplicate key error. Then to see amounts under the wrong city, the error has to be in the fact-dimension mappings, which is also quite hard to achieve if the fact is at location key granularity, which is should be since you didn't mention non-standard granularity...
Thanks @Alexei Stoyanovsky Yes Location key is the granularity level, In the location dimension I have the attribute relations ship as follows
LocationName>City>State
Location Name - Source column is LocationKey and Named Column is Location Name.
In the DEV it is showing the exact STATE CITY and COMPANY
When I run the query against the source I get the correct results but when i browse the cube for the same entity I get another STATE and CITY (Same name) gets displayed.
select distinct dl.LocationKey,de.EntityKey,fb.UnitKey,dl.LocationName
,de.EntityName,dl.LocationStateCode,dl.LocationCityName
,sum(fb.Amount) over(partition by dl.locationstatecode,locationcityname) as amnt
from FactBilling fb
left outer join DimEntity de
on fb.EntityKey=de.EntityKey
left outer join DimLocation dl
on fb.LocationKey=dl.LocationKey
inner join dimtime dt
on fb.BilledDateTimeKey=dt.TimeKey
where de.EntityName like '%tide%'
and dt.CalendarYear=2020
order by 1
Result
But when I process the CUBE, please see the highlighted RED Part, PA is showing as well, even thought the ENTITY is not related to that STATE
So we're suddenly down to 3 attributes from the original 6, but we've got DEV and COMPANY... curiouser and curiouser...
What then are the key column(s) for the City attribute?
Say the city's name is A and the states are X and Y, what does the cube show for (A,X) and (A,Y) instead of the expected amounts AX and AY respectively?
@Alexei Stoyanovsky thanks, Sorry i did not put in all attributes, I will send in detail message with all the attributes.
Thanks
Does this has something to do with RIGID and FLEXIBLE relationship between the attributes?
Thanks
Any help or advice please?
Thanks
Editing new info into an earlier comment isn't exactly convenient for structuring the discussion :(
From the samples it seems that you use LocationCityName as the single key column for the POS City attribute, and then you must have the KeyDuplicate option within ErrorConfiguration set to IgnoreError. PA happened to win over MD as the state into which Easton aggregates, so you don't even have an Easton -> MD pair in the dimension data.
If that's the case, the easiest solution is to add LocationStateCode as the second key column for the POS City attribute. And, I'm yet to see a case where ignoring key duplicate errors is the correct setting.
Hi @Amy Jandreau ,
Welcome to Microsoft Q&A!
Does the data displayed in cube different from that in data source table? First, please check the data is correct in data source table. If it's correct, the issue can be caused by that the relationship setting are incorrect. To find out whether a dimension and measure group are related, we can use the "Dimension Usage" tab of the cube designer. For more information, please refer to Handle wrong results in SSAS for unrelated dimension and measure groups.
Best regards,
Carrin
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
@CarrinWu-MSFT yes the data source is correct when I run SQL Query it gives the right STATE CITY and AMOUNT for that Company. I checked the dimension usage and it is related.