Please tell me how to create Data Source and Data Source View in Report Model Project for OLAP Cube.Whats the provider to use while creating Data Source in Report Model Project to make connection to OLAP Cube.
I am trying to log the processing time details so that we can identify bottlenecks. My SSIS package has a bunch of OLAP processing tasks. In the Event Handler (onPreExecute and onPostExecute events), I am trying to capture the start and end time for each OLAP processing task by using an "Execute SQL task". In the event handler, I have a conditional expression that checks the following:
@SourceName != @[User::Expression1]
where Expression1 is a variable that contains the value of "Execute SQL Task". This expression I thought would be true only for OLAP processing tasks which btw never fire the OnPreExecute or OnPostExecute events. What am I doing wrong?
Hello, I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports. I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view.
All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View.
Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata. Is this possible? If so how do I get the connection string?
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?
We are working on a business intelligence solution and are breaking our heads on something that is too critical for us to proceed any further and support our stance. Please help. The case is as follows.
SELECT [Month] ,[Total Sales] FROM [TryYas].[dbo].[Yearly]
Month Total Sales Jan 1000
Feb 200 SELECT [Month] ,[Sale] FROM [TryYas].[dbo].[Monthly]
Month Sale
Jan 100 Jan 300 Jan 400 Feb 100 Jan 50 Feb 60 Feb 40 Jan 150 Feb 100
Say there are two tables like the ones above.. We need to know if a report model can be built so that we can build reports where the details from the first table will be shown and when the user clicks on the Month (Say Jan) details for Jan Should be pulled from the second table, something like a drilldown would also be fine.
Now, with our understanding here makes us feel that it can't be done with a report model but is possible with Report Desinger. We need to know if it is possible to have such an implementation with a report model when a cube is built or not even with a cube. Any inputs, suggestion, explanations, references, code samples, etc.. etc.. would be of GREAT HELP.
I am trying to deploy my report model project to remote server which is our QA environment using Visual Studio. In my Project Property I have set the Traget Data source folder and model folder and URL. But I am getting this error any idea??? =================================== A connection could not be made to the report server "ReportServer URL for my remote server" . (Microsoft Semantic Model Designer) =================================== Client found response content type of '', but expected 'text/xml'. The request failed with an empty response. (Microsoft.ReportingServices.SemanticQueryDesign) ------------------------------ Program Location: at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods() at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname) at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String Item) at Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.GetItemType(String item) at Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.CheckAuthorized() at Microsoft.ReportingServices.ModelDesigner.Project.ModelClientManager.GetCredentials(String url) at Microsoft.ReportingServices.ModelDesigner.Project.ModelProjectDeployer.PrepareDeploy()
What kind of permission/role do I need to have inorder to deploy report model project on to the server. I am able to deploy same project on my local machine using visual studio.
I desperately need to setup the AD-HOC reporting on SQL server 2005. I think one of the templates that I need is in Business Intelligent Project Templets Reporting Model Template in Visual Studio Templates.
But I do not see it in there. Can some one please let me know where can I get the Reporting Model Template, or a link to where I can download or how to create it.
I have an existing project with the data source and report folder with working reports. I need to create a model for other department members to use Report Builder, not Business Intelligence Developer Studio, to develop their reports from. How do I add a data source view and report model folder to the project so I can then create a view and model?
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?
I am receiving the following error when I run the report builder query. I am able to successfully select the dimensions and measures that I am intrested in but when I run the report I get the error message.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type. ---------------------------- Semantic query execution failed. ---------------------------- Query execution failed for data set 'dataSet'. ---------------------------- An error has occurred during report processing.
I am testing this against both Adventureworks standard and enterprise cubes. Additionality I saw a post in this forum http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368962&SiteID=1 that seemed related but I am using reporting services in sharepoint integrated mode so I am having a hard time trying to troubleshoot this.
Is there any existing software/trick/hack to create a Report Project from the DataSources and Folders/Reports on a Report Server, i.e. the inverse of Deployment?
Would there be anybody interested in such a thing, and, if not, why not?
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in creating a cascading parameter. The following scenario: I have a dimension "Period" as Paremeter. The Period dimension is cascaded to year- quarter- month. So, I would like to create a cascading parameter for period as year - quarter - month.
What we'd like to do is programmatically generate and maintain a report model based on how the individual install is configured. I've found some documentation that makes me believe this is possible, but I'm hoping someone can nudge me in the right direction. The ReportingServices2005 class has several model related methods (CreateModel) and I found the .xsd for the semantic model XML file (http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd), but no real documentation on how to put together a Report Model document from scratch. Looking at the files generated from BIDS is somewhat overwhelming and I have no idea where I'd pull most of the information.
Finally, I want to create Report Model(.smdl) file without Report Model Designer.
I want to Implement CreateModel Method to create new model, any source code sample..??
Any help that can be provided would be greatly appreciated. Thanks!
I've created a data source that uses the OLE DB Provider for Teradata to connect to an existing Teradata database. Works fine.
I then use this data source within my project to create a Data Source View. Again, all works well.
However, the final step - creating a Report Model, bombs out in the wizard at the Completing the Wizard step with the following error: "[NCR][Teradata Database] Syntax error, expected something like a name between the 'SET' keyword and the 'TRANSACTION' keyword." (I elected for the wizard to only create entities for all tables, and create attributes).
If I try to create a Model manually, I get the following error when trying to build: "The Entity '<EntityName>' does not have any IdentifyingAttributes. Entity must have at least one IdentifyingAttribute."
Still fairly new to SSRS, so I'm hoping the issue is just a loose nut behind the keyboard Does anyone know what I'm doing wrong in trying to set this up?
I'm trying to create a few report models in Visual Studio to demonstrate the Report Builder tool and I'm encountering an issue when I try to generate a Report Model.
I get the error message "Specified Method Is Not Supported" when I click Finish on the Report Model wizard. The error detail is:
=================================== Specified method is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.CreateSqlDsvStatisticsProvider(IDbConnection connection) at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.BuildModel()
Steps to reproduce: 1. Create a new Report Model project in Visual Studio 2005 against SQL Server 2005 2. Create a new data source. The data source was tested and works correctly 3. Create a new data source view. The data source view was also tested (clicked on Explore Data) and I was able to look at the data in the view 4. Right-click on Report Models and select "Add New Report Model" 5. Click next at the welcome screen 6. Select the data source view created in #3 7. Leave the selected options for report model generation rules for creating the data source view (Create Entities for all Tables, Create Count Aggregates, Create Attributes, Create Attributes for auto-increment columns, Create date variations, Create numeric aggregates, Create date aggregates, Create roles were all selected) 8. Click next 9. Select Update Model Statistics Before Generating (default option) 10. Click next 11. Click Run
Once I click run it processes for approx 3 seconds and then returns the error above.
If anyone could help me I'd appreciate it. Microsoft hasn't published any details on the namespace and I'm not sure why I'd be getting the error I am.
I have found that in the autogenerated model attributes are missing for those fields that have relations to other tables. At first, it may look reasonable since a user can still get down to the field's value through the relation/related table. However, if the relation's key fields is the only thing the user wants to display, then going down to the related table is an overkill.
I can add an attribute manually and bind it to the key field(s). Is there an option in the autogeneration process to do it automatically? The only post I've found so far suggests to do everything manually (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1152575&SiteID=1). Is this the only way?
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate. Thanks, Sarah
What I can do for obtain the information of a cube OLAP in Analysis Services and obtain in Excel, they are in different machine, The Excel do not have Analysis Service.
I am having a problem creating an Integration Services package which executes an MDX query and place the results in a local DB.
I am using an OLE connection to connect to cube. However when I run the package I get the following error ....
[OLE DB Source [175]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid. AND [DTS.Pipeline] Error: component "OLE DB Source" (175) failed the pre-execute phase and returned error code 0xC0202025.
Is it possible to use a DMX query as an action in an OLAP cube? So when someone clicked on a product when browsing the cube, it called my DMX query with a product parameter, and the query would return a listing of associated products? I'm not asking how to do the query, I already have that, but just wondering if it's possible to even do that. Thanks!
Is there a way to create an ETL package that gets data from a flat file, puts it in a fact table and then it creates a cube based on the data without user intervention? So the package will generate automatically a cube that can be used in SSAS?
Can the reporing services read the cube definitions (backcolor,forecolor etc.)? the MDX contanins the " CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS " clause.
I'm new to datamining and have gather some knowledge by following the tutorial that comes with sql server 2005. I have following created a association mining model and tested it by using various DMX queries. I works quite satisfactory.
But I would like to use this mining model on a cube. Ie a calculated measure that would show top 1 recommendation for my customer portfolio. My knowlede reaches to the extent that you need to feed data by using the OpenQuery or OpenRowset queries. I would like to "stick" my model to the cube on some way and the extract values in the same manner as filtering criterias in stead of manually generating DMX queries.
I have googled a bit and searched MSDN without luck. Maybe I'm looking at this all wrong.
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
Does anyone know an easy way to automatically (ie each morning) refresh a cube without having to log in to the OLAP manager and use the interface? Ideally I want to be able to schedule a job to run each morning so that the Sales & Marketing people arrive to fresh data in their Excel 2000 Pivot tables that are connected to the cube.
Any suggestions gratefully received. Regards, John Thorpe