July 13, 2008 at 8:27 pm
I am getting the error shown below when deploying the project:
Errors in the metadata manager. The attribute with ID of 'Branch_District_Team_Engineer attribute', Name of 'Branch_District_Team_Engineer attribute' referred to by the 'Engineer Number_Team Code' aggregation dimension cannot be aggregated from the 'Engineer Number_Team Code' granularity attribute of the measure group.
As you can see it does not mention neither the dimension nor the cube in which the error occurs.
I found the dimension, but cannot find the cube and also the error.
What could be a possible cause to such error and how can I know which cube (measure group) is the problem in?
Thanx
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
July 14, 2008 at 6:42 pm
I figured it out, but have not solved the problem
There is an attribute with a composite key comprised of two fields and I linked the dimentions to the fact table by it.
There is a problem in the actual attribute.
One thing which buggered me was that the SSAS (BIDS) does not say where the problem is. It is quite annoying when a dimension is used differently in a dozen of cubes
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
September 29, 2011 at 1:31 am
Have you got any solution to this problem? I am also facing the same. If got the solution please share.:-)
September 29, 2011 at 5:23 am
I got a sloution.
If you are using aggreagation. Delete that and try to create new one.
September 30, 2011 at 8:04 am
There is an attribute with a composite key comprised of two fields and I linked the dimentions to the fact table by it.
This isn't directly related to your issue, but from a strict design point of view, dimensions should always have a single unique computer generated key, which is then linked to the fact table. The business key should never be used even if a single integer field. Moreso the case when the business key is a composite key. If it is vital to restrict the two fields you are now using as a primary key, then use a unique index.
This will make your calculations faster. Also, if you ever change this dimension to a Type 2 slowly changing dimension, you will able to do so easily. A business key, whether one field or more, would make this change impossible.
March 23, 2018 at 9:54 am
Deshpande.chai's solution worked for me, thanks.
I had changed the key attribute in a date dimension. I then moved all of the Attribute Relationships so that the old key was no longer the root of any attribute relationship chains.
There was a set of aggregations in the cube, that built from the old key, that would no longer build. Deleting all of the aggregations allowed the system to start over.
Ron Kyle's advice is, I believe, not right. The physical joins in the DSV should generally be done with a surrogate key but the key in the Dimension Structure editor can be a business key. if the attribute(s) in the business key is the basis of all of the attribute relationships then SSAS will avoid building an aggregation based on the surrogate key. An aggregation based on the surrogate will never be used by a user.
I needed to change the key from surrogate (DateId) to business key (Date) because I was building a measure that I did not want to aggregate. Setting the AggregateFunction to None on the measure resulted in blanks in the cube browser unless I included all of the surrogate keys in the report. The blank values resulted because the values had to be aggregated from the surrogate key value to the business key value even though there was a one to one relationship between the two attributes.
March 23, 2018 at 10:33 am
Ron Kyle's advice is, I believe, not right.
Then you'd be saying that Ralph Kimball's advice is not right. I'm just quoting him, and over the 15 years I've been designing and working BI systems I have found it sound advice.
I do make one exception. When designing flush and refill ETL systems, you must use a business key. Hopefully employees are not designing these for their companies.
March 27, 2018 at 12:52 am
Firstly, rules usually have exceptions. You have one exception and I would argue that in some cases, e.g. when dealing with dates, there might be other exceptions. Generally, your rule is sound and I probably should have responded with less argument and more reason!
I actually worked out a better way around my situation with SSAS for the problem I was trying to solve.
The problem was that the DWH design (inherited from previous incumbent in post) had a date dimension with separate DateId and Date Value fields. When my non-aggregating measure needed to be calculated SSAS had to aggregate to go from the DateId level in the hierarchy to the Date Value level of the hierarchy.
I have worked out that a better solution is to leave to DateId as the key field (as per Kimball) but then use the Value field in the table as the DateId's valuecolumn see this article for the usage: https://www.mssqltips.com/sqlservertip/3271/sql-server-analysis-server-ssas-keycolumn-vs-namecolumn-vs-valuecolumn/
This design enables the non-aggregating measure to show itself correctly when a user has the datevalue selected.
I think that there are other cases, for example where you have a business key that has no associated history or versioning, then a simple direct use of the business key rather than a surrogate key may make sense if it is compact and if there is a provable performance benefit.
March 28, 2018 at 1:43 pm
I have worked out that a better solution is to leave to DateId as the key field (as per Kimball) but then use the Value field in the table as the DateId's valuecolumn see this article for the usage:
I already do this. I consider it standard. The DateID is an integer which indicates the number of days since Day 1 in the datawarehouse. The DateName is the string value of the date. This allows for other non-date entries, such as "Unknown", "N/A", and "Multiple." The actual date value is in the DateValue field. This allows among other things the activation of the various date related filters in Excel.
To those who prefer the use of smart keys, I would only say that I don't understand why they are necessary. These are not things that the user should ever see. Even when you are looking under the covers, it's easy enough to make the join. It also makes a number of calculations harder, especially when using an accumulated snapshot table. These tables characteristically have multiple dates, and there is often if not always value in measuring the interval between at least some of the dates. It is much easier to calculate the difference between 3542-3498 than 20180403-20180319. The second, of course, would not yield a helpful answer. I have also found the DateID with a Day 1 useful when doing more complicated MDX, such as linear regression. I have not tried it with the "smart keys" as I have had no need. It was complicated enough as it is. But it may well be impossible to be able to calculate linear regression with MDX in these circumstances.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply