can any one sugest Quering AS2005 Cube to generate CSV or Excel in SISS 2005
i have a mdx query that runs fine in SQLserver 2005 but when use OLEDN source in SSIS and connect to Flatfile source i am getting OLEDB exception is there any work arround i have SQL Server 2005 SP2 on Windows 2003
Any help is really appreciated
thanks in advance
Hi all,
I'm trying to create a RS report that will use an AS2005 cube as a datasource. I did try to use the query wizard from reporting services to build the mdx statement but I'm having a hard time with it... How could I use the following MDX statement in a reporting services report?? Do you have to use the query builder?
Code Snippet
WITH SET [Last 5 Months] AS
{TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(2),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(3),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(4),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(5)}
MEMBER [Date].[Calendar].[AVG 5 months] AS
AVG([Last 5 Months])
MEMBER [Date].[Calendar].[Last Mth Var.] AS
(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1) - AVG([Last 5 Months]))
MEMBER [Date].[Calendar].[YTD] AS
AGGREGATE(YTD(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1)))
SELECT
{[Last 5 Months],[AVG 5 Months],[Last Mth Var.],[YTD]} ON COLUMNS,
NONEMPTY([Product].[Category].Members) ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount]
It's probably very simple to acheive but for some reason I can't get this to work properly...
I did a lot of RS against relational DB but reporting out of a cube is new to me.
TIA,
Eric
Greetings,
Here's what I need to do. I need to programmatically create offline cubes for multiple customers. Using VS2005 (C#) and SQL Server 2000, I am able to do this using the CREATE GLOBAL CUBE command. However, I need to create cubes from SQL Server 2005 as we have upgraded and the CREATE GLOBAL CUBE command does not work as it did before. I've search and found examples using AMO, but they create a server cube. I already have a server cube.
I need to be able to create filtered offline cubes from the existing server cube. I've also read much on XMLA, but that doesn't do it for me either. The code below creates an empty offline cube. However, is there a way to use this to create a cube with data?
I am creating this in a windows application.
Server server = new Server();
server.Connect(@"Data Source=C:MyLocalCube.cub");
server.Disconnect();
Can anyone help? This is driving me nuts!
Although I can create the local cube, the cube that is created is very large because it brings over all accounts for the employee. In AS2K, it filtered automatically and only returned accounts that had revenue, which is what I'm trying to do using AS2005. I've read some of the responses about using XMLA and AMO, but I am not sure how to use these within the VS2005 windows application that I use to create and distribute the local cubes. Does anyone have a good example of how to use XMLA, AMO or anything else that would allow me to programmatically create and filter offline cubes using C#?
For instance, this code DOES create an empty cube. But how would I go about getting data into this cube?
Server server = new Server();
server.Connect(@"Data Source=c:MyLocalCube.cub");
server.Disconnect();
Hola
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
Amundo
Hi guys
I have built several cubes. However I would need to create local cubes from the built cubes in Analysis Services. As a result I need to work out the DDL for my existing cubes. However the cube contains many mdx calculated members. I was wondering if it would be easier to simply generate the DDL from my existing cube in Analysis Services.
Does anyone know any third party tools that I can use to reverse engineer the DDL from the built cubes in Analysis Services ?
Thanks
Tom
Hi all,
Im currently using SQL server 2005 Management Studio. I need to export files from SQL database to Access database.
But I dun think SISS is installed. Can anyone advise where to look for the file to install? Thks in advance.
Wee
Hi
I have Excel 2003 and need a little guidance regarding how to make a connection to my cube.
I tried the Pivot Wizard but do not see any option which allows me to connect to my cube.
Kindly assist. Many TIA.
We have a large user community that have created a number of MS EXCEL spreadsheets using MS OLAP Cubes as their input. Every month, once the cubes have been updated, the users can "refresh" their spreadsheets. These spreadsheets range from simple to complex, depending on the user's skill set and task to be performed.
Here is the problem... We are in the process of moving the cubes to a new server and the cube names are changing due to new Naming Standards. What can we do to minimize the impact to our user community?
Thank you all for your input
Lets say i have one analysis server project on server A . I have made an excel sheet on desktop B in which i have inserted a pivot table which datasource is Server A's cube . I then have save this worksheet as webpage and save it on Server A. When i run this webpage on server A its working fine with but as soon as when i run this webpage on other PC it give me the error
"The query could not be processed:
The data provider didn't supply any further error information."
what could be the reason .. Please help me i am going mad....
Hi guys,
I want to connect through a pivot table in excel to an OLAP cube I created in SSAS but it gives connection error. But I can connect to tables, databases in SSMS through pivot tables in excel. Any ideas what might be the problem? Some settings maybe etc
can I generate excel from stored procedure?
View 2 Replies View RelatedWhen I try to create an Offline cube from Excel 2007 I get the following error message. This used to work but I cannot figure out what to loo for.
Code Snippet
Microsoft OLE DB Provider for Analysis Services 2005 :
OLE DB error: OLE DB or ODBC error: XML for Analysis parser: The 'CreatedTimestamp' read-only element at line 1,
column 38747 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was
ignored.; XML for Analysis parser: The 'LastSchemaUpdate' read-only element at line 1, column 38803 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was
ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 4554
(namespace http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 17325 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 57387 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 60047 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 62847 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 65497 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 72718 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 75425 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.;
Errors in the metadata manager. The attribute hierarchy for the Month attribute cannot be created because a
hierarchy with the same ID or name already exists..
Can somebody advice my on what to look for? Thx!
The attribute hierarchy for the Month attribute cannot be created because a hierarchy with the same ID or name already exists. ----- There is no other Month?
Regards,
Dirk
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?
So I use Excel 2010 connect to a cube I have built. Â Then I change some values in the cube via my ETL and re-process the cube. Â Then I verify that record is NOT there in the fact table - check!
However, when I refresh the worksheet where the pivot table is pulling data from the cube, but that old record wont go away!
Just realized my cube data source on the dev server, was in fact still configured with my local workstation name. Once I updated that, processed the cube all was well.
I want to create an excel file with .xls by using VB script.
Because i want to create a DTS package for our requirement. DTS accepts excel files which has .xls extension only.
I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.
Any help on this would certainly be appreciated.
Thanks in advance.
I am trying to write a query to exclude a set of particular users from our Users Table where they have particular domain names
We want to exclude all users that have a domain of @dnb.com & @test.com.
Can anyone suggest anything?
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?
I have connected to SQL SERVICES ANALYSIS SERVICE database through excel and when I observed that value of the date attribute is displayed as ######## in the excel for 1/1/1753.
I am able to see the value 1/1/1753 in the Cube browser but not able to see the vale in the excel.
how to replace this value with blank in the excel.
Generate an html-excel file with any table. Does not need Excel to generate , because does not use Excel automation.
http://www.databasejournal.com/scripts/article.php/3300831
What object do I reference to use SSIS from Excel. I want to generate a flat file definition based on Excel. I have a lot of fields to import and I don't feel like creating them as flat file columns. I have a few tables and I get the source file format from the vendor in an Excel format. What I would like to do is generate a flat file connection in an empty package using VBA.
View 4 Replies View Related
Hello all,
I'm new to SSIS and have a question about the best way to generate multiple Excel files with my current package design. I have a stored procedure that I run from an Execute SQL Task in a foreach loop container, and it generates results as appropriate for each of the parameters it loops through.
What's the best way to take the result set from each of those executions and generate an Excel file from each set? How do I map that result set variable to be the input for creating a new Excel file? Is this best done as a script task somehow?
Should I not be using SSIS for this task? I thought I would just create a package and schedule it to run daily with SQL Agent, and it would autogenerate the Excel files as needed.
Thanks for your advice!
Hello, i´m inserting values in a table defined as above,
/********
create table SERVICE_TYPE (
PK_TYPE numeric(8) not null IDENTITY,
TYPE nvarchar(32) not null unique,
DESCRIPTION nvarchar(256) null default(''),
USER_VISIBLE numeric(1)not null,
constraint PK_SERVICE_TYPE primary key (PK_TYPE)
)
go
**********/
these values are inserted throug ODBC with VC++,
in the case of the DESCRIPTION Column, i´m sending null strings CString(""). this value will be filled after.
What happens is that, when i query the database to show the records in this table, the database shows, for example:
PK_TYPE TYPE DESCRIPTION USER_VISIBLE
--------- ------ ------------------- -------------
1 1 (null) 1
i dont want to show the "(null)" value in the column DESCRIPTION, but a null string like ""
can anyone help me , thanks
Cristovão
Hi!
I created linked server to Oracle 8.0.5 on ODBC data source. When I run query like:
select * from linked_server_name..Schema_name.Table_name
it hangs up
I read in Books Online that the syntax is:
linked_server_name.catalog.schema.object_name
What is the _catalog_?
And also, is there a way to run PASS THROUGH QUERY against linked server, but not using OPENQUERY?
Hi,
There is one manual process done by my friend. The process is " In query analyser, she runs a sql query and the resultset of Sql query, she copies and pastes into a excel workbook".
I wanted to write a SSIS package to automate it and providing a UI, in which she can give connection details and paste the SQL query and the package will in turn generate excel workbook in the target path". I thought of trying ExecuteSQL task, but the output resultset of ExecuteSQL task cannot be mapped dynamically to excel source(correct me if I am wrong). If I use Dataflow task, then also whether dynamic mapping of SQL output to Excel destination input is possible?
Can you please tell me the best approach for achieving the above functionality?
Thanks,
Venkataraman R
How do I create dimensions that exists in multiple cubes with different facttables. The dimensions must have the same name.
In AS2000 you separated them as shared dimensions and private dimensions.
How do I manage this in AS2005?
In short, we have started deploying Office 2007 to our users and Excel is currently the only client we use to interact with our AS2005 cubes.
A few users have reported issues (which I've verified), but the explanation in the KB article doesn't make any sense to me. These reports were originally developed in Excel 2003 and when opening them up in Excel 2007, we'll see a message saying that Excel found unreadable content in the .xls file and after clicking 'Yes' to recover contents of the workbook, we then receive a message that a PivotTable report was discarded due to integrity problems. If I opened up this report in Excel 2003, I don't receive these errors or messages.
Per the KB's explanation (http://support.microsoft.com/default.aspx/kb/929766):
This issue occurs if the following conditions are true: €¢The workbook contains a PivotTable that uses key performance indicators (KPIs).€¢The KPIs are created in the Analysis Services Business Intelligence Development Studio.€¢One or more of the KPIs have an expression in the Current Time Member property.
Now, we are running 2005 Standard Edition with no SP, but will be deploying SP2 in a few days. Our cubes do not have any KPIs defined. Can I even define KPIs if we are only running Standard Edition?
Any thoughts?
I'm new here so I'm not sure if this is the correct spot to post this question but here goes.
In the cube design view under the Calculations tab I have several calculated members that I have created. Each of these are dollar amount fields for which I have defined a Format String property as follows:
Format String: "$#,##0;($#,##0);;N/A"
This format correctly handles my positive value, Negative value and my NULL value the way I want. However I need to deloy this cube on one of my UK severs and I don't want the $ but instead I would like the pound sign without having to recreate this entire project to do so. I have tried the following syntax but it is incorrect.
Format String "Currency;;;N/A"
Can someone help me with the correct way to code the property so that I can get my currecy symbol based on the regional setting as well as handle my NULL values.
Thanks
I want to export the data into multiple sheets with same template, all the worksheets have to split dynamically with specific Sheet Name and template also copied to all other sheets
For Example:
Sheet Name: Guru
Name Age
Guru        24
Sheet Name: Johnson
Name Age
Johnson     32
it goes on......
We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
I am trying to schedule a SISS package by creating a new job but it is giving me error when the job executes.the error i find when i checked the error log it was the following error
Unable to find index entry in index ID 1, of table 2073058421, in database 'msdb'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support. [SQLSTATE HY000] (ConnExecuteCachableOp)
Any help would be appriciated
Hi All,
Can anyone tell me how i can do Pre Post Command in SSIS. Even If i want to Manipulate the File before running the Task with sum input values or Want to Do Post Command after Running to Task