I have a .rdl file that was exported out of ProClarity's Desktop Professional 6.1 using their RS plug-in. I uploaded the file into Report Manager and when I execute it, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'Three_Month_Funding_Trend'.
Unable to recognize the requested property ID 'ReturnCellProperties'.
Does anyone have any idea what this is referring to? Does it have something to do with my configuration, connection or the report definition? Other reports such as DBMS based reports work fine.
Hello,Can I import an OLTP (Reltional DB) as a Data Source into SQL ServerAnalysis Services 2005 and then use the Cube Wizard and the new DataSource View feature to create the OLAP model ?Or do I have to first design an OLAP Data Warehouse with a Star Schemaand then import this DW as a Data Source into my Analysis ServicesProject.With SQL Server 2000 , OLAP would be the way to go..but with SQLServer 2005 , it seems as though the wizard and data source viewfeatures do half the work for you.I have an OLTP DB and am not sure which route I should take ! Anysuggestions / input would be much appreciated.Thanks in Advance...RegardsRusszee
Here is the scenario: I have a report in SSRS 2005 that uses an OLE DB connection to send a MDX query to an AS 2000 cube. The report has been working fine for weeks. When the report ran today, it returned the row and column metadata, but not the cell values.
I ran the MDX query separately in the SSRS query editor pane, and it returns all values properly. I was also able to perform the query using ProClarity, so it appears that the issue is between the result set and SSRS.
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
When I use SSIS for extract data from ssas, that means,I use mdx query.
then random error occured.
Hope some one can understand my poor English....
And the Error Info show below.
Code Snippet
Error: 0xC0202009 at Data Flow Task - For Individual User Tech Points, OLE DB Source 1 1 [31]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.". Error: 0xC004701A at Data Flow Task - For Individual User Tech Points, DTS.Pipeline: component "OLE DB Source 1 1" (31) failed the pre-execute phase and returned error code 0xC0202009.
Our OLAP environment involves an ETL/Data Warehouse/Data Mart server and a cube publisher server. We would like to learn how to automate the Archival/Restore of OLAP databases. We are currently doing it manually though OLAP Manager. Any help would be appreciated. Thanks. James.
-- James E. Bothamley Senior Database Administrator Dave & Buster's, Inc. 2481 Manana Dallas, TX 75220
Work Phone (214) 904-2296 email jbothaml@DaveAndBusters.Com
"Once in a while you can get shown the light in the strangest of places if you look at it right"
On the subject of Data Warehouses, Data Cubes & OLAP….I would like to speak frankly about Data Warehouses, Data Cubes andOLAP (on-line analytical processing). Has it dawned on anyone elsethat these buzz words were created by some geek who decided to take astab at marketing? Knowing that to the backwoods manager who knowslittle of technology that new innovative names for old concepts wouldhelp to sale their products.I mean seriously, what is the story here? In a nut shell, and pleasestop me if you disagree, but isn’t a data warehouse simply adatabase? Can’t you do everything on a conventional databaselike SQL Server, Oracle or DB2 that you can do on these newproprietary Data Warehouse constructs? I mean who are they trying tofool?Take a look, for instance, at Data Cubes. Who hasn’t noticedthe striking similarity between data cubes and views used in all themore robust databases? Also, what about OLAP? OLAP is nothing morethan a report generator. There’s nothing you can do with thesemillion dollar price tagged Data Warehouse total solution packagesthat I can’t do with SQL Server, Oracle or DB2…for thatmatter Microsoft Access.As an example some sales people for Metadata Corporation has the VicePresident of I.T. in Nashville, for Healthspring, sold on their totalsolution data respository which is such a scam. All they had to dowas throw a couple of buzzwords at him and they have him hypnotized.Personally, I feel that these kinds of marketing practices undermineour industry. It helps to unravel what little standards orconsistency we have. What do you guys think?Stuart
HI, i am new to OLAP in Sql Server 2005, bascially i have read through lots of article on this OLAP and basically i get to know how it works and function theorically, but i could not find an article to show me step by step on how to create the cube and how to query the data from the cube. Would very much appreciated if somebody out there can explain to me in details how to i create cube from sql server 2005 as i totally no idea at all. Though i can view the sample cude in sql server 2005, but i dunno how to create it from my relational database. I need it very urgent as needed by my job functionality. Hope to get some explanation from you guys asap. Just assume i am totally new and please guide me step by step to create a normal and simple cube and query from the cube. I just need to create simple prototype. Thanks alot yea guys :)
Some of my calculated members look like "######" in Excel and I believe it is because of divide by zero errors. I have tried right clicking on the table, selecting "Table Options" and "For error, values, show:" I have enter 0. This is not working. Does anybody know how to get rid of this.
All of my client boxes connect nicely to MS OLAP Server except one which fails with the following message: "Cannot connect to the registry on the server computer (servername)" where servername is the name of the server on which OLAP server resides.
I am starting using OLAP service. The first problem the first day I use it.
I setup a system DSN for OLAP under ODBC source. I use SQL driver to create a data source to a SQL server. I am using SQL SA login account. Then I go into OLAP manager create database. Then in the library I set up a data source using the DSN I created before.In the General Tab I use OLE DB for ODBC provider. After this I try to create a new cube using the wizard. I got error the message that the SQL login is invalid. I double check it is right login account. I failed several times. Then I tried directly create data source under library and not using the DSN I created before. The first time it failed again has the same message. Then the second time when I create data source I use NT authentication instead SQL login. Now everything went fine. I was able to create a cube. But I still wondering what is preventing using SQL login account when creating data source.
Hello,If I wrote the next ebay (yes I know, yawn-snore) and I had a databasewith 5 million auction items in it, what would be a really goodstrategy to get a search done very quickly? Would it involvesomething called OLAP and/or "data mining"? The only technology I amfamiliar with is simply SQL Server databases with stored procedures.I think I'd be guessing correctly and say that this technology simplywouldn't be fast enough *on it's own* to do super fast queries againstmassive amounts of data.Any insights would be of great interest. Thanks.-Frameworker.
I have an OLAP server and would like to use my Chart FX software without having to purchase the OLAP extensions on the server due to budget restraints (ouch).
I've heard that it is possible (although limited) to attach toan OLAP cube using SQL select statements (not MDX).
Basically, I would like to pull the OLAP data in the relational sense.
Is this possible? If so, are any good articles on this subject?
I'm new to OLAP and would like to transition slowly.
The drillthrough in my cube is working fine except for the cases where the dimension member is null. For example I have the dimension PRODUCT (dim) - PROD_TYPE_CD (name = PROD_TYPE_CD || '-' || PROD_TYPE_NM) - PROD_CD -PROD_NM
So for if the data is like the following where the PROD_TYPE_CD is null : - (name = -FOOD) - 123 - bread
The drillthough is not displaying any data no matter I select the PROD_TYPE_CD, PROD_CD or PROD_NM in the dimension drop down altough if query the DB directly I found data to display in the drillthrough Any ideas why this is happening and how to solve it.
I prepared an OLAP cube for the report data source in the SSAS 2005. The OLAP cube consists of more than 20 dimensions and several measure groups. I then created the subset/view of the OLAP cube using the "Prepective" function and limit to not more than 7 dimensions on each of the subset. How do I reference the OLAP cube subset as the data source when developing the report in the report designer. Furthermore what is the advantage of creating multiple smaller OLAP cubes with less dimensions comparing to one big OLAP cube with several subset/view attached to it. Thanks.
After reading some threads on this forum about accessing OLAP data from SSIS via an OLE DB Source component (and trying it myself and getting that "pcrsstore.cpp line 325" error), I have worked around the problem by using a Script Task component.
This calls a .NET assembly I wrote that uses ADOMD.NET to get at the data using an MDX query, and then actually posts an event to a Notification Services instance via a stored proc.
Writing, deploying, and maintaining a .NET assembly is more than I wanted to do, but it was worth the effort since now I know a little about extending SSIS when I need to.
But what I really wanted to do was the following, all from within the core components of SSIS:
Import my OLTP data into my relational warehouse (already done using SSIS) Process the cube (already done using SSIS) Access the cube via MDX and post an event to Notification Services It's this last step that needed some special work because of SSIS's apparent limitation accessing cube data (although posting the event to NS would be easy because SSIS can call a stored proc on the NS application database). So I'm hoping that a future release or service pack of SSIS will give us some components to run MDX and get the OLAP data into the pipeline as if it were from any other source. Thanks for reading, and if anyone has any suggestions on a better way to achieve what I need please let me know! -Larry
I am using data flow task.And data flow source uses ole db for olap 9.0 to connect my ssas. sql comment is my access mode. A mdx query extracts data. Data flow destination is sql server table. Error said Data Flow Task: OLE DB Source [579]: The output "OLE DB Source Output" (589) references an external data type that cannot be mapped to a Data Flow task data type. I guess it is a implicit data type convertion problem. But how to solve it???
We have a live OLTP database for which we create full backups every week and differential backups every day. Recently we added an OLAP database, which we need to update daily with changes from the live database.
This is the process we are planning to use. 1. Restore last full OLTP backup. 2. Apply the last differential OLTP backup. At this point we should have a replica of the live OLTP database. 3. Update OLAP database based on the OLTP replica database. 4. Delete the OLTP replica database.
Two questions. 1. If different from the process above, how is this OLTP-to-OLAP transformation typically done in the industry? 2. What is the best way to implement this process with SQL Server 2005?
I would like to know if Reporting Services 2005 is a good tool for making reporting activities on SQL Server 2005 DW and also using OLAP (Analysis Services 2005).
I have created a DTS package which should process an Incremental Update OLAP Cube ... however it is generating the following error message ... HELP has anyone seen this before ?
I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code.
Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information.
Error Code -1071607685 Error Column 0 Error Desc No status is available.
The only thing I can find for the above error code is
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out.
I'm working with PivotTable on Excel 2000 which is connected to an OLAP server (from SQL Server 7 installation). The pivot is intended to analyze Sales during April 2001. Yesterday I found out that OLAP/Excel returned/displayed inconsistent data. The 'April Total' value is NOT equal to the 'Quarter 2 Total' (I already inspected the underlying database and sure that there is absolutely NO data for months after April 2001). The value for 'April Total' is the correct one. I'm not sure whether the problem resides on the OLAP Server or Excel (pivot) itself. For ones who like to help me I would be glad to supply you with the screenshots (just email me). Please help.
I've generated some mining models against an OLAP data source (dimension). However, when I go to generate a lift chart, it seems that the only data source that can be used for input is the data source view (the relational database). Is that right? Or is there something I'm missing here.
I was figuring I'd use one slice to train the model, then another slice to test the accuracy of the results. But right now it's looking like I can't do that.
1. SQL Server Data Warehouse 2. OLAP CUBE in Analysis Services
My question is - If my SQL Server Data Warehouse is changed (Having Append Data) - Is that My OLAP Cube will have the Append Data?
It's possible, my OLAP Cube always having Append Data if my Data Warehouse is changed? If yes, how to do it without re-deploy and re-process my Analysis Services Project.
1. Data Warehouse 2. OLAP CUBE in Analysis Services
My question is - If my Data Warehouse is changed (Having Append Data) - My OLAP Cube will have the Append Data?
It's possible, my OLAP Cube always having Append Data if my Data Warehouse is changed? If yes, how to do it without re-deploy and re-process my Analysis Services Project....
We are getting symptoms of a 30 second timeout irrespective of what we do, but only on certain machines. Can anyone shed any light on how the connection timeout can be set, as there seems to be a factor overiding any settings we set?
We are working in Excel (2003) connecting to an Analysis Services 2005 cube through a website https://Datasource connection. When we edit the connection string that the file is using (either by forcing use of an exterior .ODC file and editing the settings in there, or by converting the xls spreadsheet into an xml spreadsheet and manually editing the connection string in there) we get no joy: after 30 seconds the error appears:
"Either a connection cannot be made to the [my server address], or Analysis services is not running on the computer specified".
This error is itself rubbish: If a smaller query (one that takes < 30 seconds) is run in the same Excel file and Pivot Table, it connects fine. What really doesn't make sense is that the exact same file works on some machines and not others. These machines are all inside the same network (and the machines that work have been proven to work on a number of other networks). What's more there is no pattern to the machine capabilities - some newer / faster machines get the error. These machines (that work and do not) have excel 2003 SP3. (Another of the ones that works has Excel 2007).
Changing the connection string to include "Timeout=300" or "Connect Timeout=300" makes no difference, and "Command Timeout" causes a different error so that it wont even attempt to run the query (as opposed to collapsing after 30 seconds of normal progress). Similarly in the .ODV the tags <ODC:CommandTimeout>, <CommandTimeout>, <ODC:Connect Timeout>, <ODC:Command Timeout>, <meta name=CommandTimeout content=1000> etc. have no effect (the file still gets the 30 seconds error rather than just giving up so no way of working out correct syntax)
The only pattern we can see, and we can't fix this, is that the machines that work have Retail versions of excel, and the ones that do not have corporately licensed versions of excel. We cannot see any difference between them in the Help >Abouts, but thought it might be worth mentioning.
Any help would be fantastically appreciated, we are out of ideas. Thanks kindly
