If the relational fact table has all the relevant data...why not just connect to that data source and let Tableau "roll up" the data to the higher level Category/Product/Time? Then you can create one view with the high level "paid amount". Another view with the details. And put both views on a dashboard. When a user filters to find the high-level, the detail view will be filtered and you'll get it all, without having to monkey around trying to blend cubes and relational data. I've attached a super quick example using 10.4 and Superstore. Superstore is Excel, but this method would work for any relational database as well.
For export, users can export underlying data from Tableau Desktop (Click on Detail worksheet then go to Worksheet > Export > Crosstab to Excel). Or if you publish to Tableau Server or Tableau Online (Download > Crosstab) they should be able to export to Excel as long as permissions allow.
Hope this helps!
I understand that method, but I would like to try the ssas cube and also
need the detail view for each aggregation.
On Tue, Nov 14, 2017 at 7:40 PM, AB Commendatore <
I'm not sure this is possible... It is possible to join a relational data source to a cube data source. But because the aggregations differ, you may encounter unexpected results such as data duplication - normal with joins. The workaround would be to blend the data. The cube would need to be the primary data source with the relational data source being secondary. In blending, you may also encounter errors due to differences in the granularity of the data. I would recommend experimenting with both options to see if either works for you. If blending does work out, you might consider using action filters to generate the detail views you are looking for. I'm including some reference materials that helps explain some of the considerations and limitations when working with data from cube and relational data sources:
Hope this helps with your journey!