Perspectives Defined In Analysis Cube Not Working In Report Builder
Feb 20, 2007
I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.
Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.
Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.
By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.
Help!!!!
View 3 Replies
ADVERTISEMENT
Apr 16, 2007
I never got an answer on this so I am reposting this issue. Also, please note that I have consistently reproduced this issue on multiple perspectives within and accross multiple cubes on 3 different SQL Server Instances. I have also had no problem with perspectives within Report Models that were not generated from an SSAS Cube.
Specifics:
=========
I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.
Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.
Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.
By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.
View 1 Replies
View Related
Oct 5, 2015
I have been trying to get the ValueR column of the following query through the MDX but instead getting ValueW as output of the MDX
select
exp(Log(sum(MTMROR)+ 1 ))-1 as ValueW,
exp(sum(Log(MTMROR + 1)))-1 as ValueR
from
Temp_Performance
where Rundate in ('2015-03-01','2015-03-02')
MDX written for the above query is
With
Member [Measures].[LogValuePre]
as ([Measures].[MTMROR] + 1)
Member [Measures].[LogValuePre1]
as VBA
[Code] ...
[MTMROR] measure has the aggregate function Sum. What i can get from this behavior is MDX is first aggregating the result and default aggregation function is Sum. When i try to see the value with more granular data by having the date dimensions on the row (un-commenting the date dimension) i get the correct log and exp log values. Its showing the correct value as date dimension is most granular level in the fact table. While trying to get the data at less granular level(Fund level), getting the sum function applied automatically.
If i set AggregateFunction to none in the cube structure, i get null as the output.
How could i apply the log function before the sum function in the [MTMROR] measure?
View 4 Replies
View Related
Jul 11, 2014
I have created one Multidimensional CUBE, while browsing(Analyze in Excel) it in Excel, I am unable to create TimeLine Slicer.It is giving me following error as: "We can't create Timeline for this report because it doesn't have field formatted as Date".
I have Dim_Date having Date as a column of Day level granularity. In PowerPivot, we do mark Dim_Date Table as a 'Date Table'.In the same way, do we have to set anything here so that Excel will come to the Date format for TimeLine Slicer?
View 7 Replies
View Related
Apr 11, 2006
In a typical Database, there are tables, views, stored Procedures, user Defined Functions.
We could use tables and views in designing the Report Model.
We cannot use Stored Procedures, I tried that.
Please could somebody tell me if we could use User Defined Functions?
What if a view is already using a user defined function, is the function automatically includid in the Model Design?
Thank you,
View 11 Replies
View Related
Oct 5, 2015
I have been living in the T-SQL world for years, and am working on my first Tabular model.
I have a fact table that I would like to use for many reports. My idea is that I would use Perspectives in the tabular model to pull in the columns that I need for each purpose. In some cases, I also need to filter out some rows. Is there a way to filter perspectives? The only solution I am coming up with is to create views in SQL Server and import the fact table multiple times for each purpose. That doesn't seem very efficient.
View 2 Replies
View Related
Feb 1, 2007
I have scoured the internet looking for someone who has run into this issue with no such luck!
Product: Reporting Services 2005 Analysis Services 2005
Our users use Report Builder as an ad-hoc report tool. Data sources for Report Builder come from Report Servers and Analysis Servers.
I have run into issues where a user will select a source for their report that is based on an AS cube. When they select an attribute (field) from a selected entity in the Report Builder Explorer and drop it on the canvas, all entities except the one the field was selected from disappear.
For example:
A cube has a fact table called "Annual Income" with dimension of employee and demographics. In Report Builder, the entities would be listed in the left hand Report Builder Explorer window as
Annual Income
Employee
Demographics
If I select attributes from Employee or Annual Income, as usual, you will see the related hierarchies of the remaining entities. However, if I select an attribute from Demographics, then the Annual Income and Employee entities just disappear and all I can see is the Demographics entity and its attributes.
Has anyone run into this and if so, any ideas? This is becoming extremely frustrating. I have deleted and rebuild cubes, individual dimensions, and everything else I can think of.
Your responses are appreciated,
Scott
View 1 Replies
View Related
Jul 18, 2007
Hi,
Is anyone using Report Builder via a Report Model generated from Analysis Services in production?
From what I have seen on the Forum people have done this from books, but it is not clear as to whether they have used it at work, in production. There do appear to be reports of some problems with the integration between the two, and with performance.
The attraction of Report Builder on top of Analysis Services is that we should only need to define measures, security, etc, once in the Cube, not twice, as we would if we had to use Report Builder on top of a relational source, alongside Cubes on Analysis Services.
What I would like to find out is:
1) If there is someone who has deployed Report Builder on top of both a relational data source and Analysis Services. If so, which has been taken up best by the users?
2) How does the navigation in Report Builder work with Cubes. It seems a little bizarre in that you can only drill down, never up. Is there any way around this. Do you need to design the Cube specifically to support the Report Model? If so how?
3) The generation of the report model is easy. However, is there any way to control this, or to edit it later?
4) We have had problems with Report Models on relational sources where changes have invalidated the Report Model and all reports built on it. Can similar problems arise, for example, if you change the Cube and then Re-Generate the Report Model?
5) Are there any other issues around Report Builder. The two I have found so far seem to be Hidden Dimensions showing up, and Report Builder not working if measures are defined as non-aggregatable.
6) Are there any other end-user adhoc reporting products which work better with Analysis Services?
Regards
Paul
View 4 Replies
View Related
Mar 21, 2007
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
Thanks,
View 8 Replies
View Related
Nov 5, 2007
Hi all,
I'm trying to get empty cells from an OLAP cube to display in a report designed using the Report Builder. Of the three report types, only the Table report shows the empty cells. Is there any option/setting that I need to enable to get this to work for the Matrix or Chart reports? Something like the "Show Empty Cells" option that is available in the pivot table used by BI Dev Studio?
Any help appreciated.
View 2 Replies
View Related
Sep 13, 2007
Hi, Running RS2005 (was running RS2000 on same system)
When trying to run Report Builder I am getting the following error;
Report Execution Error
The report might not be valid or the server could not process the data.
An attempt has been made to use a rendering extension that is not registered for this report server.
Anyone come across this error before?
Thanks in advance
Scott
View 4 Replies
View Related
Oct 26, 2007
Hello,
I can use Report Builder on my server. However, when am on the client machine (logged in as myself with Admin rights) I see the Report Builder button. I click it and nothing happens. I do have the 2.0 .NET framework install on the client machine.
When I move the mouse over the button it displays the ReportServer/ReportBuilder/ReportBuilder.application on the status bar at the bottom of the window.
I've tried setting the security on the server, but I've had some problems understanding exactly what I need to set and where.
For example, in this http://msdn2.microsoft.com/en-us/library/ms365173.aspx link it says, "1. Create a role assignment under Home that includes the Report Builder role. An item-level role assignment is necessary for granting access to report models."
Where exactly is Home? Is it the directory, is on the Report Manager? Do I set these role assignments on the directories in IIS? I've tried a bunch of different things, but I feel like I am grasping around in the dark.
On the server, I clicked the "Enable My Reports to support user-owned folders for publishing..." and I can see that on the client machine, but that is all I've been able to accomplish.
I've seen other posts about this, but I haven't been able to nail down this issue.
Thank you for the help.
-Gumbatman
View 4 Replies
View Related
Apr 29, 2015
I am built the cube and processed. The fact table has 2 keys from the same dimension table.
My customers asked me that they want to browse the cube with report filters. The scenario is very strange.
They want to filter on same dimension with different conditions.
For example, they want to use the condition as ProductCode IN (...) and ProductName NOT IN (...)
How can I do this when browsing the cube in excel or any where?
View 2 Replies
View Related
May 28, 2015
I am using SSRS 2008. I have 3 applications specific reports deployed to reporting manager..AAA , BBB and CCC - be my Applications for example.i have 3 different application users. i want to grant access the respective application users to access respective reports.i mean application user A should not be able to access/view reports from BBB and CCC. similarly for B and C application users.HOw this can be implemented ?
View 2 Replies
View Related
Nov 10, 2015
After connected to Analysis Services and following the path below:
Analysis Service - Databases - Cubes
I create the parameters for a custom report after right click on a specific Cube and select Browse to arrive at a view where i can define dimensions and parameters as well as drag and drop measure to the report rows and columns. The left top File bottom only give me a Save Selection or Save ALL option. However, i'm not sure where it saves it to or if it saves the settings for this custom report at all.
I pull this report monthly and it's a pain to recreate all the parameters. How to save this template for future pulls or maybe even incorporate the pull into excel for monthly refresh instead?
View 3 Replies
View Related
Feb 5, 2010
I have created a new SSRS Server and done all of the basic security setup for the site but I am having trouble with client machine access...At first I had an issue with being able to access report manager from any client and ended up having to shut off windows authentication on the client IE settings to get it to work. Now I am trying to run Report Builder from a client machine and it will not download. The client machine is on the same domain and my user id is set as an administrator within SSRS and as a local admin on the 2008 server.
I had the thought that without windows auth in the browser maybe it can't permit me access to the application but if I turn it on I can't get to it to download it. I have set Full Control rights to the folder as well. Most of the info out there is for 2005 and that uses IIS to host the pages where as 2008 does not. Here is the details of the error:
PLATFORM VERSION INFO Windows : 6.1.7600.0 (Win32NT) Common Language Runtime : 2.0.50727.4927 System.Deployment.dll : 2.0.50727.4927 (NetFXspW7.050727-4900) mscorwks.dll : 2.0.50727.4927 (NetFXspW7.050727-4900) dfdll.dll : 2.0.50727.4927 (NetFXspW7.050727-4900) dfshim.dll : 2.0.50727.4927 (NetFXspW7.050727-4900)
[URL] ....
View 2 Replies
View Related
May 19, 2015
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
View 2 Replies
View Related
May 13, 2014
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
View 2 Replies
View Related
Feb 6, 2008
Hi,
When i select datasource in Report Builder, i am able to see all the available DataSources.
Eg: I have selected one datasource from the list and which has 3 tables(table1, table2, table3) associated to that datasource.
when i drag and drop table1 fields to report, i am not able to see the other 2 tables(table2 & table3)
Is there any property or relationship do i need to maintain?
Thanks,
SR.
View 5 Replies
View Related
Mar 13, 2007
Hi, I am able to create reports(.rdl) using Faircom c-treeSQL Server ODBC driver which in turn connects to the Faircom SQL Server.
However I'm facing issues while creating the Report model for the report builder. I would like to know if it is possible to create report models with Faircom SQL Server?
Thanks,
Satish
View 1 Replies
View Related
Feb 7, 2008
Hi,
I was talking to my boss to day and our report request are not very consistant. We always having someone coming back to change something in our report. We were thinking of useing something called the Cube Analysis. Then it give our employees the raw data for them to run any standard query for themself. We have folks that want a report one way, but then they changed their minds and we are creating yet another report 4 or 5 times. what are your thoughts about this type of database?
View 6 Replies
View Related
Jun 21, 2007
I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.
Would appreciate any suggestion for my enlightenment. Thanks
View 1 Replies
View Related
Jun 30, 2007
I setup Hiarchy in the dimentions of my cube, however when I go and look at it via proclarity, I can't see the hiearchy there.
I have one table that has:
Category
Subcategory
Partnumber
And I have the hiearchy set from Subcategory to partnumber.
Then I go into proclarity and limit the category by "my catname" and I still see the 8000 partnumbers in the list.
Any ideas?
View 1 Replies
View Related
Jul 20, 2005
I have a (hopefully typical) problem when it comes to cube design. Westore millions of product records every year, broken down bymonth/quarter. Each product can be assigned to various heirarchialclassification groups etc. The data in an OLTP DB occupies roughly100G for a typical year.We're looking at breaking this out into OLAP to provide faster accessto the data in various configurations and groupings. This is not aproblem, as this is the intended use for Analysis Services.The problem is that we apply projection factors on the product pricesand quantities. This would be ok if it only happened once, however,this happens every quarter (don't ask why). The projection factorschange 4 times a year, and they affect all historical product records.This presents a challenge because to aggregate the data into a usefulconfiguration in the cubes, you throw out the detail data, but thismeans throwing out the price and quantities which are needed to applythe projection.So if you have Product A at $10 and Product B at $20, and roll both upinto Category X, you'll have $30, but you'll lose the ability to applya projection factor of .5 to Product A and .78 to Product B. They'rerolled up.I don't want to regenerate the cubes every 3 months. That's absurd.But we can't live without the ability of projection theprices/quantities on a product level (detail level). So how can thisbe achieved when the other cubes are created at a higher level withless details and sums of the detail data?My initial guess is that we have to update the product data, and thenreaggregate all the other data that is built upon that product data.Is there any other way to apply math to the data on the way out?Thanks in advance!Regards,Zach
View 2 Replies
View Related
May 6, 2015
i want to create cube withe 2 fact tables. is it possible?? if it is could you show me how !!
View 2 Replies
View Related
May 21, 2015
how can use this mdx script in the calculation part of a cube, will i simply dump it in the script form by starting with the 'create member current cube.
[measures].[test]'
select
[measures].[abc] on 0,
[xyz].[xyz].(&0):[xyz].[xyz].(&60) on 1
from
(
select
(tail([month].[month].[month].members,6))on 0
from
[cube])
View 3 Replies
View Related
May 18, 2015
The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.
DimesionKey1
DimensionKey2
DimensionKey3
Measure 1
Measure 2
Measure 3
[code]...
View 4 Replies
View Related
Nov 23, 2015
I know where the data files are etc, but what is the physical structure of a cube like on disk?
Logically we see the data as a star. Is the physical file akin to a star also? Or is a single file a measure group containing all the required member and measure data - thereby eliminating the need for physical join operators.
View 2 Replies
View Related
Oct 14, 2015
I am planning to implement the security in my cube..
Total sales is 1000..
Customer Sales
A 100
B 200
C 300
D 400
Total Sales 1000
When i want to give access to the customer A, he can capabule of viewing only A value 100... But the problem is A is able to see total sales 1000.. How can i restrict the sales a value to 100 in the cube.. Where i have to set the property to fix this issue.....
View 3 Replies
View Related
Nov 22, 2015
Is it correct to say that for each cube you can have only one Fact Table? I am having a funny dispute just now.
According my experience I never built cubes with more than one fact table, if I want take data from more than one table I write a view and I use it as Fact table...but a cube with two or three fact tables? Never tried..
View 3 Replies
View Related
Jun 12, 2006
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
View 17 Replies
View Related
Aug 4, 2015
I have a cube. Its xml is different at some point than its design view. Suppose for some dimension and its attributes, source table is different than what it showing in the properties window for them..
Is this possible? How to read cube xml because there are repeating tags in it. There are two type if dimension tags.. one has only attributed and other has all properties.
View 7 Replies
View Related
Dec 13, 2010
Is it possible to have more than one cube under one SSAS database? For example I have a database "Test" and in this the cube exist is "TestCube", is iit possible to deploy another cube "TestCube2" under the Test databse?
If yes then what is the process to do that, the reason I am asing is there are some common dimensions used n both the cubes and I am not sure what is the best way so that I can use the shared dimension?
View 6 Replies
View Related
