October 20, 2008 at 7:53 am
We have to make a report based on a cube built from a Warehouse DB. We have to place such fields in the report, wich are not in the Cube only in the warehouse db. Reduced my question in a simple example:
ORG_ID (the foreign key) from organisation table is in the cube but we need to show the ORG_NAME (name of the organisation) in the report. If I connect to the cube in the reporting services, how do I get the ORG_NAME based on the ORG_ID in the cube?
Thanks for Your help!
October 20, 2008 at 9:43 pm
i would suggest that the org_name be included in the cube as a dimension. This will make it available to your report.
October 21, 2008 at 1:01 am
Thanks!
This a trivial solution, but not the most effective, ... but the rushest... Every time we extend the table with some new fields, the cube has to be extended with the new dimensions. Otherwise is my example a simplified oneof our problem.
So I please for a solution: Is it possible to connect both databases?
October 21, 2008 at 7:06 am
If you have the org id in the cube already then I would assume you have the dimension that has the related attributes to this id value. Could it possibly be that the attribute you are referencing in the dimension table to slice the data does not have the name column associated with the org name column and is simply just displaying the org id. This is a really simple solution if that is all you need to do. I would assume that you would already have this information in the analysis service database to reference and report on.
If you truly need to incorporate data from both systems then you can take a look at my blog posting where I go through the process of setting up a linked server and actually run MDX scripts to get SSAS data with SQL Server stored procedures. Once you have the data within the stored procedure you will easily be able to perform additional lookups and joins to the additional information you want to report on that is not in the SSAS database. You could also do this in an SSIS package to that you could associate with the SSRS report and I have an example of that also.
Using Reporting Services (SSRS) with SSAS data
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
October 27, 2008 at 3:11 am
This is a very usefull idea.
But: We have a lot of reports, they all should be rebuild.
We had another ideo, but the solution is missed:
We build a Cube ORG_ID and ORG_NAME and 1 as a measure.
How can we connect (link) with MDX these 2 cubes?
Thanks!
October 27, 2008 at 2:14 pm
Still not certain why you would have to create a second cube just to get the ORG_NAME. This seems to me like a simple modification to the Data Source View to include this attribute (named query) or within the source system within a view. Once this is included you can then reference this in your dimension and setup a new attribute or modify the existing attribute, ORG_ID, by setting the name column property appropriately.
To query a different cube within the MDX you can use the LookupCube function. Here are some references - LookupCube (MDX) and LookupCube.
Hope this helps.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
October 28, 2008 at 4:02 am
As I already explained, the ORG_NAME/ORG_ID is only a very short simplification of our problem:
We have a cube, wich rebuild takes some hours in the night and the size is already huge enough.
Otherwise we have some fields in the warehouse DB, wich can be often updated by a user.
Transferring all these fields in the cube:
1. So the changes are visible only the next day.
2. The size of the cube is groving.
3. Extending reports with fields in the warehouse DB needs changes in the cube. (The tables in the warehouse DB are also extended with some fields.)
So my default idea was:
- Accessing cube and warehouse DB from reports.
The solution with stored proc is very usefull for special cases, but for a lot of reports takes a lot of time.
So my second idea was:
- Let make another cube, where such often modified/added fields are included. We modify the cube without troubles and we can rebuild this cube every X minutes in seconds. So we leave the "main" cube unchanged.
It seems to be the simpliest and "cheapest" way to expand the main cube, but I tried to find a solution for a longer development period and for other cases also.
We have already made some test with "LookUpCube", but our only results were to get measures but no dimensions from the requested cube.
Do You have an example wich delivers a dimension from the other cube?
Thanks a lot for Your help!
October 28, 2008 at 7:26 am
Which version of Analysis Services are you using (2000 or 2005)? Performing an update process on a dimension should not take that long and if you are partitioning the measure groups you can definitely reduce the time for processing (split the full process into process data and process index and set the max parallel processes appropriately too -- 1.5x # of CPUs).
The second link I sent you shows an example of returning a dimension member value - LookupCube.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
October 28, 2008 at 9:17 am
Thanks!
We are using 2005.
October 22, 2009 at 3:58 pm
[font="Comic Sans MS"]Thanks for sharing that use of OpenQuery() to get the data from SSAS on your blog Dan. Suddenly mdx doesn't feel like inline sql anymore.[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply