Querying Analysis Services 2005 Cube With SSIS
Jan 23, 2006
Hi,
I've been trying to use Analysis Services 2005 Cube as a data source, query it via MDX and then use the data returned elsewhere in SSIS.
However, I've been unable to get this working and can't find any information regarding how this can be done. Surely it should be possible when I can get this working even in Excel?
I've looked in December edition of BOL and no luck - have also sent a feedback to BOL regarding this and have been told that "it should be possible, since there is a way to send SQL queries to AS." However the person I was speaking with knew of no one who had actually tried this scenario and to try posting here.
Any help as to how to get this done would be greatly appreciated.
NR
View 18 Replies
ADVERTISEMENT
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
Jan 16, 2007
Hi everybody,
I have two problems while using a analysis service cube as data source for a reporting service report.
1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:
An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.
How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.
2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:
An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.
As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.
How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?
Thanks,
StSt
View 4 Replies
View Related
Apr 24, 2015
I am trying to configure the reporting for TFS using SQL Server. But I get following error when viewing any report:
So I try to manually process the cube to check if it works. I am following this article: [URL] ....
When I click on GetProcessingStatus and invoke it (with last field set as TRUE) I get following error:
How to resolve this issue and be able to see the reports.
View 5 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
Dec 8, 2005
If there are any AS gurus out there, I could use some help. I've been having some problems with particular AS dimension and cubes and it's driving me crazy! It doesn't matter what I do, nothing seems to work.
Anyway, here's what I'm trying to do. I've got a fairly simple dimension. There is a date stored in the dimension that is formatted as an int. The dimension needs to only display AGE, so I cast the int as a date and do a datediff to get AGE. The dimension builds just fine and I get the results I want. My problem is when I add the dimension to the cube. The cube fails to build and I get an error message - "Data source provider error: The column prefix 'MY TABLE' does not match with a table name or alias used in the query." Basically, AS is telling me that the table that is used for the dimension doesn't exist, even though the fact and the dimension are joined properly and I've validated the structure.
I've run a number of queries in QA on the two tables and everything works fine. No funky data issues. I've run the service packs a few times, but that didn't work either. I've tried making a cube that only has the just fact table and the one dimension table, and it still fails.
Basically, I'm out of ideas. Any help that anyone has is greatly appreciated.
View 5 Replies
View Related
Feb 22, 2006
Hi,
I am having the requirement where I have to use string as measures. Is this possible in Analysis services 2K.
Any help on this will be greatly appreciated
View 23 Replies
View Related
Apr 10, 2008
Hello:
How do I connect to an Analysis Services 2000 cube while in Integration Services? My idea is to execute an MDX query from within a package, and then stage the data to a SS 2005 table for a RS report to query.
Thanks in advance for your suggestions!
Tim
View 3 Replies
View Related
Jul 1, 2004
I'd like to be able to update an Analysis Services cube through a stored proc.
Currently I can:
- Make a DTS package that updates the cube
- run xp_cmdshell which runs dtsrun which runs the DTS package.
That is messy, easily broken, and hard to get good error info when an error occurs. Is there a better route?
View 1 Replies
View Related
Aug 11, 2004
Can I create a cube file, .cub ,without the Microsoft Analysis Services
Pls Guide me as I am new in this field.
Thanks
Loyd
View 3 Replies
View Related
Jan 9, 2004
Hi,
I am processing one cube using Full Process option and it's giving
following error.
Analysis Server Error: Internal error [Object does not exist] '11948' ;
Time:1/8/2004 6:11:11 PM
Error(-2147221421): Internal error (Internal error [Object does not
exist] '11948' ); Time:1/8/2004 6:11:11 PM
Can anyone help me on this.
View 1 Replies
View Related
Sep 5, 2006
hi,
I try to generate ad hoc reporting model for analysis services cube in reporting services.
in Management Studio I connected to reporting services, defined a data source to the analysis services database.
But If i try to generate model i get the error:
"While connection with a data source a mistake has appeared, or the query is invalid for the data source. (rsCannotPrepareQuery)(Report Services SOAP Proxy Source)"
Any ideas as to what may be the problem?
Thanks!
karaman
View 3 Replies
View Related
Feb 7, 2008
Mr. Pearson,
I am currently reading your article titled, "MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist". This article is directly applicable to a problem we are currently trying to solve regarding ragged hierarchies as input parameters.
I have not read the entire article through but am in the process of doing so. Also, I will be trying to implement your solution.
I have one question, will SSRS support multi selection when using a hierarchical picklist? Thanks in advance for any assistance.
View 1 Replies
View Related
Oct 19, 2007
We have set up an IS package to process an AS 2005 database (comprising cube & dimensions, etc) daily, via a SQL Server Agent job on both development and production systems. This has been working fine for months.
A new dimension was added to the cube on the development system - automatic processing via the IS package continued without issue. However, when the new dimension was added to the production system the IS package no longer processes the cube correctly. Although all appears ok (and all is present and correct in the logs), no data updates to the cube are made. Only when the cube is manually processed does the cube get updated.
Anyone got any ideas about how to get around this issue? We have created a new IS package, with a single Analysis Services Processing Task, and tried this but get the same outcome.
View 4 Replies
View Related
Oct 17, 2007
Hi,
I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
If not, how can I solve this problem?
Thanks in advance.
View 3 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
Oct 29, 2015
I've deployed a SSIS project (having some packages) on SSIS catalog and I've created a job to invoke the related packages. I'd like to query the SSIS catalog to get the info about the execution of the job to understand if it is completed with success.I've seen the tables internal.executions and internal.operations. Which of these two tables contains the more useful info about the execution job (f.e. the job name, the execution status)?
View 6 Replies
View Related
Feb 5, 2008
Is there any way to deploy reports to a Report Manager in SQL Server Reporting Services 2005 if the reports query SQL Server 2000 databases? Or are only 2005 databases able to deploy to the web?
Also, if I have a report that queries both, can I deploy it to the web on the 2005 Report Manager with a linked server to the 2000 server?
View 4 Replies
View Related
May 6, 2008
I have a DTS package in SQL Server 2000 which brings together data from different sources, including from an Analysis Servcies cube, and compares it.
Usually this runs perfectly, but sometimes I get an unexpected internal error from the Data Pump task which queries the cube - no other information, just the error.
This is usually when I have run the package once (manually from Enterprise Manager), then changed a global variable and run it again.
The cube it is querying is a virtual cube and the measures are calculated cells (NOT calculated members).
We are on SP4 of AS so should be up to date.
Does anyone have any ideas?
Thank you
View 1 Replies
View Related
Oct 11, 2007
Hello, I have a problem when trying to fully process an SSAS database using Integration Services "Analysis Services Processing Task" task. I have 2 of these tasks which are responsible for processing the Dimensions then the Cubes. When I run the package either via the BIDS environment or on the local server from the Integration Services engine, I will get an error after about 20 minutes stating:
"Error: Memory Error: Allocation failure. Not enough storage is available to process this command""Error: Errors in the metadata manager. An error occurred when loading the <cube name> cube from the file \?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataMyWarehouse<cube file>.xml"
The cube name is not specific, it will fail and any of my cubes could be in the error log
If I fully process the AS database using the AS engine (logon to local AS server, right-click AS database and click Process), I get no errors at all, it processes and completes fine. The processing options are identical when I run in AS or via the SSIS "Analysis Services Processing Task" task.
I've searched quite a lot online but no joy, the information I have gleaned from various sites does not directly link SSIS with SSAS processing problems.
When either the AS processing starts via SSAS or SSIS the memory usage of MSMDSRV.exe increases to around 1.4 / 1.5 GB but never goes to 2GB ever, even when the error appears.
I've done the following with no effect.
" Have run via AS and works fine
" No specific cube it fails on
" Have created a Dimension only package, same problem
" Changed the maxmemorylimit
" Changed the connections to localhost
" Memory DOES NOT max out on server
Server Specs:
Windows Server 2003 Standard + Service Pack 2
4GM ram, 2GB paging file
SQL Server 2005 + Service Pack 2
Can anyone help?
Andy
View 2 Replies
View Related
Sep 16, 2007
Hi All,
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
Regards,
Joseph
View 1 Replies
View Related
Nov 11, 2015
Now I have a different constellation: Integration Services run on one server, in version 2014, the Analysis Services instance to process the cube database on runs on another server, version 2012.I tried several different combinations of SSIS version and Analysis Management Objects version, and got several errors while running the process package (e.g. object reference not set to an instance of an object, cannot find AnalyisServices.dll..)
Is this combination 2014/2012 possible at all?I assume the BIDS version has to be for SQL Server 2014, as I want to run SSIS packages on a 2014 server, is that correct? Does it matter at all, can I also deploy 2012 packages?Which version of Analysis Management Objects do I have to use? I assumed I have to use version 11.0 here, because I want to process a 2012 cube?If it is possible to use the "old" 11.0 version of AMO, do I have to do anything so that it can be found by the SSIS package running on the server (it was built on my local computer, there I have all SQL Server versions from 2005 to 2014 installed in parallel), or do I just have to copy it to the appropriate SQL Server folder?
View 3 Replies
View Related
Jul 30, 2007
Hello,
i have a small problem with the data sources in the reporting services, maybe you can clarify the situation... I have a MS CRM 3 solution with the reporting services 2005 installed. From an other vendor we have a arcplan solution on the 2000 analysis services. I would like to get access from the 2005 reporting services to the 2000 cube. Is this possible? Ive read that the SSMS cant connect to the 2000 cubes. Can i connect from the reporting services??
thank you
regards
Andreas
View 3 Replies
View Related
Apr 24, 2008
Hi,
I've created an SSIS package to process cubes on Analysis Services 2005. This works fine.
Now I wish to use the same package to run Cubes on Analysis 2000. I have changed My connection manager to point to this DB. When I test connection it works fine.
The problem lies, when I try to edit the Analysis Service Processing Task. I get an error message saying:
"A connection cannot be made-Make sure the server is running".
I can get into Analysis Manager ok, and see the database there.
Hope someone can point me in the right direction here?
Thanks in advance
View 5 Replies
View Related
Jul 17, 2007
I'm trying to execute an SSIS package from an ASP.NET web page. Using some code from http://msdn2.microsoft.com/en-us/library/ms403355.aspx I have managed to get this working when executing a package that contains a stored procedure, however it does not work when there is an Analysis Services cube to build.
Here is my code for the webservice
Code Snippet
Public Class SCCBuildDW
Inherits System.Web.Services.WebService
' LaunchPackage Method Parameters:
' 1. sourceType: file, sql, dts
' 2. sourceLocation: file system folder, (none), logical folder
' 3. packageName: for file system, ".dtsx" extension is appended
_
Public Function LaunchPackage( _
ByVal sourceType As String, _
ByVal sourceLocation As String, _
ByVal packageName As String) As Integer 'DTSExecResult
Dim packagePath As String
Dim myPackage As Package
Dim integrationServices As New Application
' Combine path and file name.
packagePath = Path.Combine(sourceLocation, packageName)
Select Case sourceType
Case "file"
' Package is stored as a file.
' Add extension if not present.
If String.IsNullOrEmpty(Path.GetExtension(packagePath)) Then
packagePath = String.Concat(packagePath, ".dtsx")
End If
If File.Exists(packagePath) Then
myPackage = integrationServices.LoadPackage(packagePath, Nothing)
Else
Throw New ApplicationException( _
"Invalid file location: " & packagePath)
End If
Case "sql"
' Package is stored in MSDB.
' Combine logical path and package name.
If integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty) Then
myPackage = integrationServices.LoadFromSqlServer( _
packageName, "(local)", String.Empty, String.Empty, Nothing)
Else
Throw New ApplicationException( _
"Invalid package name or location: " & packagePath)
End If
Case "dts"
' Package is managed by SSIS Package Store.
' Default logical paths are File System and MSDB.
If integrationServices.ExistsOnDtsServer(packagePath, ".") Then
myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", Nothing)
Else
Throw New ApplicationException( _
"Invalid package name or location: " & packagePath)
End If
Case Else
Throw New ApplicationException( _
"Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.")
End Select
Return myPackage.Execute()
End Function
End Class
This is the code from my page
Code Snippet
Protected Sub btnBuildDW_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBuildDW.Click
ExecutePackage()
End Sub
Protected Sub ExecutePackage()
Dim launchPackageService As New SCCBuildDW.SCCBuildDW
Dim packageResult As Integer
Try
packageResult = launchPackageService.LaunchPackage("file", "c:ssis", "PackageTest")
Catch ex As Exception
' The type of exception returned by a Web service is:
' System.Web.Services.Protocols.SoapException
lblResult.Text = "The following exception occurred: " & ex.Message
End Try
lblResult.Text = CType(packageResult, PackageExecutionResult).ToString
' Console.ReadKey()
End Sub
Private Enum PackageExecutionResult
PackageSucceeded
PackageFailed
PackageCompleted
PackageWasCancelled
End Enum
I'm using windows authentication and impersonating an identity. The identity has access to the analysis services role but the package still fails to run.
Can anyone offer any help or advice on this?
Thanks
View 7 Replies
View Related
Nov 1, 2007
I have an olap database "A" and SSIS package "P" which process all the dimensions and cubes in "A" olap database.
I created "A1" olap database copy of "A" and made copy of "P" SSIS package as "P1"
I opened "P1" SSIS package and updated olap connection properties "Initial Catalog = A1". A1 is my new olap database.
When I run package "P1" guess what? it processed "A" olap database's cubes and dimensions. Try it, not in production because I did it in production.
View 12 Replies
View Related
May 6, 2008
How to use the "Analysis Services Execute DDL Task" in SSIS to stop or start the Analysis Services.? Many thnaks.
View 5 Replies
View Related
Feb 5, 2008
I use Sql server 2005 standard edition in a 64 bit machine.I tried to create a SSIS project with an Analysis Services Processing Task. In connection Manager, the test connection succeeds but when I try to add the analysis services connection manager it gives following error.-The new connection manager could not be created,Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap). This is a production server and we cannot reproduce this in other servers , so troubleshooting is difficult. This error appears when connecting to other sources like MS excel in Export task of Enteerprise Manager as well. Cubes can be processed manually without any problem.Pls give me a solution.
View 4 Replies
View Related
May 14, 2008
I am trying to execute an SSIS package from a client that contains an Analysis Services Processing Task in the package. The client that does not have SSIS and SSAS installed. We are getting an error
The task "Analysis Services Processing Task" cannot run on this edition of Integration Services. It requires a higher level edition. The same package runs from a server that has both SSIS and SSAS installed. Let me know if someone has come across the same problem.
Thanks
View 1 Replies
View Related
Sep 17, 2015
The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.
Is there any way i can get the Member_key in a set in rows section.
Like
With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube
I want the value as just like
Value
Employees, UniqueName
View 2 Replies
View Related
Apr 22, 2008
Can I have a SQL 2005 database as the datasource for Analysis Services 2000? If Yes, what Driver should I use to connect to SQL 2005?
Prakash.P
The secret to creativity is knowing how to hide your sources!
View 1 Replies
View Related
