Introduction – Part 2
Part 1 of this post focused on the first category of how the Analysis Services Multidimensional (MD) duplicate attribute key error can arise. It reflects the perspective of an atomic attribute – an attribute having no attribute relationships other than with the dimension key attribute.
This post focuses on the second category of this error, which can arise when an attribute does have attribute relationships besides the (required) one with the dimension key attribute.
As is well known, creating attribute relationships is a best practice in Analysis Services MD for improving query performance. The most common reason attribute relationships are created is to support a natural hierarchy – so your data model has to have one for this to arise. The next most common reason is to support attribute properties, such as a sort order – i.e. when the…
Historical reporting is common enough, but what are some ways to slice through your historical data in SQL Server Analysis Services (SSAS) Tabular? Tracking and including historical data or Slowly Changing Dimensions (SCDs) is common enough in data warehousing, and Business Intelligence as a whole, but putting it into an easily-digested form is always a new set of issues.
In this post, I will walk through some strategies we’ve used for integrating historical data into reporting and analytics solutions with SSAS Tabular, as well as some ways you can restrict this information to give your users a cleaner experience.
With bidirectional filtering in SQL Server Analysis Services (SSAS) Tabular 2016, it’s easier than ever to build many-to-many relationships into your model. But what are some ways to avoid trouble when building them? This post covers two topics: (1) a scenario that can cause Tabular to match completely unrelated groups across a many-to-many relationship, and (2) some strategies for automating your bridge tables (including where they might need some brains!). If you’re already comfortable with this topic, and just want to see some DAX, feel free to skip right to the calculated bridge tables.
Otherwise, let’s say you’ve built up your many-to-many relationship based on a bridge or relationship table someone generated in the database, and have measures based on one or both of the tables connected by the bridge. When looking at the results, you see that a number of the results…
Defining relationships is central to data analytics, but how can you use SQL Server Analysis Services (SSAS) Tabular to keep them from being overlooked? Often, you can run into the situation where you really just need to say what is missing from a data set or report. Usually, you can work around this conflict of expectations versus reality by building a factless fact. This type of fact table is really just a list of relationships, where the “fact” is really the existence of the row.
Typically, this type of table is built directly into a data warehouse, and helps to materialize calculations that would otherwise be too expensive or awkward to calculate. Think about the steps that you might need to take in order to highlight gaps in data (such as a student missing class), if you were not provided a tidy checklist of…
Introduction – Part 1
The most common and dreaded error that may occur when processing a dimension in Analysis Services Multidimensional (MD) is undoubtedly “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: …. “ ‘Common’ because of both the poor data integrity frequently found in cube data sources, and improper modeling of the data in MD. ‘Dreaded’ because a number of distinct situations all give rise to this error and it can be difficult to diagnose which one is the actual problem. There is enough to explore around this error that this is Part 1. There will be a Part 2 subsequently.
The full error message looks like “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘TABLENAME’, Column: ‘COLUMN’, Value: ‘VALUE’. The attribute is ‘ATTRIBUTE’.” TABLENAME will…
It is no secret that Microsoft is moving away from the original “Multidimensional” (MD) flavor of Analysis Services, instead focusing all efforts on the new “Tabular” (Tab) flavor. This drives the standard advice that Tab should be the basis of most new cube projects. Another idea driving this is that for 60-70% of projects either technology likely will work fine – so it is prudent to go with the newer one which is much earlier in its lifecycle.
However, there are many differences between the two technologies. In particular cases, one or the other is clearly the best fit, but without understanding these differences it is difficult to even assess whether your project is in the 60-70% where it doesn’t matter, or the 30-40% where it may. Even if you would elect Tabular regardless to stay earlier in the product lifecycle, you should be sure you understand the tradeoffs.
Since MD is…
I’ll be giving a presentation on the somewhat unlikely subject of the title at the next NYC SQL Saturday at Microsoft HQ on May 30, 2015. It will be a largely non-technical, non-demo presentation about accounting and bookkeeping concepts developers are likely to encounter if they have to interact with financial recordkeeping applications, particularly in data warehousing and business intelligence contexts. I will also offer reasons why learning about this (in more depth than is possible in this session) represents great career development for the average developer.
There will be one tech hook wherein I will demonstrate how Microsoft’s Analysis Services directly supports some of the mechanisms of bookkeeping.
The all-day event is free (except for lunch) and has a host of valuable sessions on a wide variety of mostly technical SQL Server topics. Here is the link: http://www.sqlsaturday.com/380/EventHome.aspx
This is Part 2 of Performance Point – the Good and the Bad – Part 1, which can be found at http://blog.tallan.com/2012/12/16/performance-point-the-good-and-the-bad/
That article focused on the good features of Performance Point (PP) out-of-the-box (OOTB). This one focuses on the not-so-good features, or lack thereof – and some tips for dealling with some of these issues. Given its focus, it is somewhat more technical and requiring of familiarity with the product than Part 1.
This posting presumes the reader has at least a basic understanding of what Microsoft’s Performance Point (PP) offering is, at least at the level of wanting to assess whether it is appropriate for your requirements. Basic familiarity with Microsoft’s Analysis Services is also required to get the most out of the observations I provide. If you have that background, this two-part posting should prove helpful in making an assessment of whether PP might suit your requirements, in whole or in part.
In the month of July we’re going to be holding a FREE, half-day, hands-on workshop on MDX and Analysis Services around the country.
If you’re in the Los Angeles, New York City or Hartford, CT areas, come join us. We’ll be deconstructing real-world examples and in the process explaining important things to consider in how Analysis Services handles certain expressions.
8:30AM – 9:00AM Registration & Breakfast
9:00AM – 12:00PM Hands-On Workshop
Wed. 07/15/2009 – Anaheim, CA – Click Here to Register
Wed. 07/15/2009 – Burbank, CA – Click Here to Register
Tue. 07/28/2009 – New York, NY – Click Here to Register
Wed. 07/29/2009 – Bloomfield, CT – Click Here to Register
Here are more details about the event:
Tallan and New Horizons are pleased to present a Complimentary Half-Day, Hands-On Workshop: Real-World SQL Server Analysis Services and MDX
Overview of the Workshop
MDX is the universal query language for OLAP databases, just like…