Hi all,
Normally I would be using SSAS but our finance department make use of Hyperion Essbase. I was wondering whether it was possible to upload data into an Essbase cube using SSIS in the same way that you can use the Analysis Services processing task? I realise there are no specific task for Essbase, but are there any suggestions about what would be the best way of going about this?
regards
Colin
Can SSIS connect and pull data from an OLAP database hosted by Hyperion's Essbase V 6.X or V7.X? We have a manual process of pulling in stuff and would like to replace this with an automated process. Appreciate any help.
View 3 Replies View RelatedMy company is looking to implement a dashboard solution for Hyperion Essbase 7.1.2. The inclination is towards using Micrsofts BI for doing the same. However we are in admist of deciding on which MS BI products(SSAS or SSRS or SSIS) supports the Essbase 7.1.2 version in terms of connectivity and performance. I know SSRS supports Hyperion system 9BI+ but not sure about the backward compatibility.I would greatly appreciate if GURUS could shed some light into this.
Thank you
My company is looking to implement a dashboard solution for Hyperion Essbase 7.1.2. The inclination is towards using Micrsofts BI for doing the same. However we are in admist of deciding on which MS BI products(SSAS or SSRS or SSIS) supports the Essbase 7.1.2 version in terms of connectivity and performance. I would greatly appreciate if GURUS could shed some light into this.
Thank you
I have a custom essbase application/db source that I wish to connect to from SSRS 2005. I have all the required software installed (APS / SP2 .3050) etc.
I can connect to the datasource in SSRS 2005, but when I'm trying to build the query on the cube, I see under the member properties of all dimensions, the following piece of line
"error occurred retrieving child nodes: null"
Also, when I try to drag my Measures into the query section (or any other dimension for that matter of fact, though that defies logic) I get the following error
Cannot perform cube view operation. OLAP error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query
I am new to using ESSBASE with SSRS 2005, and hence cannot make much sense of the above two errors.
Any insight or a re-direction to similar posts with solutions will be greatly appreciated.
thanks for your time,
!!
Can someone tell me how you can automate the refresh of OLAP Cubes? I just inhereted a data warehouse running on SQL7 and the idea of having to go in and manually refresh the cubes everyday is, well... stupid. I can't believe that they've been doing this for a year. Unfortunately, I'm not familar enough with MSOLAP yet and I can't figure this out. Any tips?
View 2 Replies View Related
We have scenario where we need to create package for refreshing the cubes. Let me explain it breifly.
We are doing ETL process in different ETL tool (not SSIS) and once the process is done, we are inserting in a table where we have a column like date, Completed C as the status. Once we get this information i.e 'Completed C' status and date, we need to refresh the cube like as follows,
1. Previous day cube need to be refreshed daily once
2. Previous week cube need to be refreshed weekly once.
3. Previous month cube need to be refreshed monthly once.
4. Historical cube need to be refreshed daily once.
We have decided all these above operations needs to be done in SSIS. In this case what are the things to be done while creating a package.
1. What are the control flow items to create it in SSIS?
2. Is there any way to have pooling like every 10 minutes to check whether the table has 'Completed C' status and STOP EXECUTE this package?
3. Is there any way to check the date for Previous day cube, Previous week cube, Previous Month cube and Historical cube etc?
Thanks in advance,
Anand Rajagopal
Süleyman writes "I want to transfer databases with cubes and virtual cubes from SQL 2000 Analysis Server to SQL 2005 SQL Express.
1.how i can transfer the cubes?
2.how i can transfer the virtual cubes?
3.Why i see the tables(saw)and the cubes in SQL Managment only under Tables together and nothing under cubes?
Please help me
many thanks
Süleyman from Germany/Frankfurt am Main"
I want to build a data import process with SSIS, sourcing Hyperion Financial Management. Accoring to my knowldge there were a Star Integration Server (Star Analytics acquired by IBM in Feb 2013) doing the extraction job and which could be used in SSIS.
As this product is not available now, how to do this.
Hi,I’m new to SQL Express, but I have created a table and a stored proc to populate it.I have dragged the table into a form so I can view the data in a GridView.I have added a button to add new rows to the table. All the above works fine, except when I hit add, the data gets added, but the GridView doesn’t update and show the new data. Is there some code I can add to the add button that also refreshed the GridView? ThanksMike
View 2 Replies View Related
Hello everybody,
I wrote a stored procedure for SqlServer 2000 and i am using it for paging purpose.
The procedure is as follows :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[OLAP_PagedRows]
(
@SelectFields nVarchar(2000) =NULL,
@GroupByFields nvarchar(1000) =NULL,
@BaseTable varchar(100),
@KeyColumn nvarchar(200)=NULL ,
@JoinTables varchar(500) =NULL,
@ConditionalClause varchar(1000) =NULL,
@Pagesize int = 10,
@PageNumber int =1,
@SortExpression nvarchar(200)=NULL,
@SearchText nvarchar(200)=NULL
)
AS
BEGIN
DECLARE @SQLSTMT NVarchar(4000)
DECLARE @SQLSTMT1 NVarchar(4000)
SET @SQLSTMT1 = ''
--check whether page size is given null or not, if so set to default value
IF @Pagesize IS NULL OR @Pagesize = ''
BEGIN
SET @Pagesize =10
END
--check whether page number is given null or not, if so set to default value
IF @PageNumber IS NULL OR @PageNumber = ''
BEGIN
SET @PageNumber =1
END
--Start constructing the query --
SET @SQLSTMT = 'SELECT '
SET @SQLSTMT1 = 'DECLARE @CountValue INT SELECT @CountValue = count(*) From '+@BaseTable
SET @SQLSTMT = @SQLSTMT + @SelectFields + ' FROM '+@BaseTable
If @JoinTables Is Not Null
BEGIN
SET @SQLSTMT = @SQLSTMT + ' ' +@JoinTables
SET @SQLSTMT1 = @SQLSTMT1 + ' ' +@JoinTables
END
DECLARE @StmtWhereClause nvarchar(500)
SET @StmtWhereClause =''
--------------------- Get where conditional clause
If (@SearchText Is Not Null AND RTRIM(LTRIM(@SearchText))<>'')
BEGIN
SET @StmtWhereClause = @StmtWhereClause + ' WHERE ' + @SearchText
END
If @ConditionalClause Is Not Null AND RTRIM(LTRIM(@ConditionalClause))<>''
BEGIN
IF (@StmtWhereClause <> '')
BEGIN
SET @StmtWhereClause= @StmtWhereClause + 'AND ' +@ConditionalClause
END
ELSE
BEGIN
SET @StmtWhereClause = @StmtWhereClause + ' WHERE ' + @ConditionalClause
END
END
SET @SQLSTMT = @SQLSTMT + @StmtWhereClause
SET @SQLSTMT1 = @SQLSTMT1 + @StmtWhereClause
If @GroupByFields Is Not Null And RTRIM(LTRIM(@GroupByFields))<>''
BEGIN
SET @SQLSTMT = @SQLSTMT + ' Group By ' +@GroupByFields
SET @SQLSTMT1 = @SQLSTMT1 + ' Group By ' +@GroupByFields
END
IF @SortExpression Is Not Null AND RTRIM(LTRIM(@SortExpression))<>''
BEGIN
SET @SortExpression = LTRIM(RTRIM(' Order By '+ @SortExpression))
SET @SQLSTMT = @SQLSTMT +' '+ @SortExpression
SET @SQLSTMT1 = @SQLSTMT1 +' '+ @SortExpression
END
SET @SQLSTMT1= @SQLSTMT1+' SELECT @CountValue As MyRows '
--SELECT @SQLSTMT1
--SELECT @SQLSTMT
DECLARE @StartRow INT
SET @SQLSTMT = ' DECLARE temp_Cursor CURSOR SCROLL FOR '+@SQLSTMT
EXECUTE SP_EXECUTESQL @SQLSTMT
Open temp_Cursor
DECLARE @RowCount INT
SET @RowCount = 1
SET @startRow = (@PageSize * (@PageNumber-1))+@RowCount
--SELECT @startRow as 'Current Row'
WHILE @RowCount <= @PageSize
BEGIN
--Select @StartRow 'as @StartRow'
FETCH ABSOLUTE @startRow From temp_Cursor
SET @RowCount= @RowCount+1
SET @StartRow = @startRow + 1
END
deallocate temp_Cursor
EXECUTE SP_EXECUTESQL @SQLSTMT1
END
It is working fine but I have problem with this kind of paging. I need to load the whole data into the cursor and i have to fetch records. The problem is that my table's contains more than Half a million records in it. If I have to load each time this cursor it will be a very big problem on the server side.
Probably it may not be a best solution, but sqlserver 2000 cannot provide more help than this. If I use sub-query for this like using Top <Number> it adversly effecting the nature of the data retrieval.
One solution that I am thinking is Load cursor once and whenever some updations performed on those tables from which cursor is getting data should be automatically reflect the changes.
Is this possible? Please help me.
Regards
Andy Rogers
Hi,
Recently we upgraded the SQL sever from 32 bit box to 64 bit and we encounterd some weird results like data was not refreshing unless we manually does a manually refresh.
we just ran some migration scripts from another server to the new 64 bit server and script is correct only thing problem is with data refreshing.
Please send me the solution for the problem and let me is there any script that we can refresh the database using tsql command???
Thanks in Advance,
Shiva.
Good day,
I have a SSRS 2000 report that when I view the report data does not refresh until I press the refresh data button in the report. Clearly this can't be right and to expect users to press the refresh button every single time is also rediculous.
HAs anyone had this problem before and know what to do.
Please help.
Thanks
Hello,
I created a slowly changing dimension object and used an OLE DB Source object with a SQL Command to feed it. After all the SCD objects are created, I get a warning about a truncation which might happen.
I adjust the source query with the right converts, so the query uses data types and lengths which match the target table. I update the OLE DB Source with the new query. Column names are unchanged.
Now how do I get this data flow to reflect the new column lengths? Do I have to throw away all objects and recreate the SCD? The refresh button in the SCD object doesn't do it. This is also a problem when adding columns to your dimension table. Because I modify the stuff that the SCD generates, it's VERY teadious to always have to throw it all away and start over again.
Thnx. Jeroen.
Hello,
I am trying to refresh a test database with data from a production database. Both database structures are identical, e.g. constraints, stored procs, PK, etc. I am trying to create a package in SSIS that accomplishes this task and I am having extensive problems. The import export wizard is out of the question because the constaints are not carried over, plus when I try to refresh the data using the import export wizard, it fails on 1 specific table because of a column in that table named "Error code". I think "Error code" is a micrsoft keyword, so it fails on this column. Does anyone know a workaround that I can do to accomplish this simple task, that could be completed in minutes using DTS. I understand that SSIS is not as straight forward as DTS, but this task is something that DBA's do on a regular basis and therefore should not be this difficult.
Any help would be appreciated,
David
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.
Dear All,
Im using VS2008, visual basic and SQL Server express.
General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?
I'm getting strange behavior where the data is not refreshed unless i exit my app and re-enter. In other words, i can run a sql command , the data is apparantly saved (because i get no errors) then if i refresh a data set or do a sql select query the data that i expect to return is not there.
Im fairly new to SQL express (and SQL server generally) so i dont know if its my coding or i need to switch some 'feature'
on/off or not.
I hope thats clear
Also, could someone point me to documentation that explains each parameter in the connection string
Many Thanks
Chris Anderson
My code is:
ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Connection = New SqlConnection
Connection.ConnectionString = ConnectionString
Connection.Open()
'''''''''''''''''the above code is done at the start of my application
'''''''this code below called during application many times
dim sql as string = "my sql string here"
Dim cmd As SqlCommand = Nothing
cmd = New SqlCommand(sql, Connection)
Try
cmd.ExecuteNonQuery()
Catch err As SqlException
MessageBox.Show(err.Message.ToString())
MessageBox.Show(sql, "SQL ERROR: ", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
cmd.Dispose()
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
View 7 Replies View RelatedI need to use cubes in VS 2005 web project. I have no clue how to create cubes or use them in my web page. I am familiar with Reporting Services but not cubes.
thanks in advance!
I am trying to build a report that access data from multiple cubes. Is it possible to join multiple cubes based on their shared dimensions? Or is it possible for report to display data from multiple cubes properly aligned based on the dimension.
Environment:
SQL Server Analysis Services 2005 and Reporting Services 2005
Example:
We have multiple cubes with “Year� as a shared dimension and each one has different Rate info. The cubes have other set of shared dimensions which I am planning to set it up as a parameter. I would like to display the report as
Year
Rate1
Rate2
Rate3…
I really appreciate any suggestion.
Thanks
Haseeb
One of my client requires TDS for all databases. My BI product has reports with OLAP Cube as data source.Â
How about using BitLocker for the HDD where the abf is physically located.
is it possible to use integration services to extract data from essbase?
View 3 Replies View RelatedI need to extract data from SSAS' cubes into a SQL Server table.
I already read examples using Linked server (with openquery), SSIS, etc. However, the result always return as many columns per dimension as levels. I need to extract all members of a dimension in a column. E.g., when excecuting the following MDX query in Adventure Works 2014:
select [Measures].[Sales Amount] on columns,
    Non Empty [Date].[Calendar].members on rows
 from [Adventure Works]
I would like to get this result (MDX query in SSMS), but with keys displayed intead of names:
But I get this (MDX thru openquery or SSIS):
Hi, all here,
I currently have a question about unknown member in cubes (as we know, there is always an additional member called unknown in an OLAP dimension)for data mining. I mean is there any way for us to deal with unknown member in cubes when mining cubes?
Thanks a lot in advance for any guidance and help.
Hi,
I'm am writing a report in SSRS 2005 on a local PC that uses cubes in AS 2000 on another server as a datasource. I am using the Microsoft SQL Server Analysis Services Extension (OLAP9) to connect to the cubes using windows authentication. I can save and preview the report in visual studio and publish the reports on IIS on my local PC with no problems but when I go to view the report in IE, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'dataset'.
Unable to recognize the requested property ID 'ReturnCellProperties'.
Have read various articles on the net with no conclusive explanation.
I have seen another post on this forum that suggests using OLEDB for Analysis Services instead but if I do this it doesn't allow me to use the MDX visual query designer. Creating the report using OLAP9 then changing to OLEDB and pasting in the query code doesn't work so well either.
I went and read http://technet.microsoft.com/en-us/library/ms159219.aspx about data sources supported by SSRS 2005 and it should be ok.
I am using sql server 2005 with the latest service pack 2.
Any ideas?
I need to refresh a test db that is running on the same instance as the prod db. Should I just use DTS or is there a better way?
View 3 Replies View RelatedHi.
I have a datasource that depend on parameter A.
Parameter A get his values from query and have a defualt value.
Parameter B get his default value from query that depend on parameter A.
Now, whan I run the report, parameter A get a value and then parameter B get his value and the
datasource run OK.
But the problem is that when I'm changing the A value (from the value list) - The datasource run fine but Parameter B stay with the old value with out any change.
Does any one know how to solve it.
I'll be happy to give more explanation if it isn't clear enough.
Thanks.
I am wondering if it is possible to have a report generated by RS refresh periodically automatically. This could be realized by inserting a few lines of JavaScript to the report including the reload() function, but I do not know if there is anyway to do such thing.
Thanks in advance for any tip!
Hi All,
I had an interesting problem come up today. I have a report that when you preview on vs or click view report on the report server, the report continually refreshes itself. I cannot see that I have written anything different in this report than any other report.
I saw one other append here on a continually refreshing report but that was linked to a document map and this report has not document map.
Has anyone seen this problem of a continually refreshing report?
It's not a big deal as I figure I will just have to write it from scratch again, but I am interested to see if I can stop it before I re-write it.
Thanks in Advance
Peter
i am running a game from a dedi box .. ms sql..
and every time i restart and put it back up ,,,,it rewinds the database
the only way i can prevent this is if i restart the server
wait a phew hours then put it back up ,,"which makes it the same way it was when i logged off"
i was told that sql database servers refresh every hour.
is there a way i can make it refresh before i restart it so then i can put the server straight back up
if i restart the server back up straight away it does a rewind for some reason.
tech ::
the server does a rewind because it wouldn't have saved the game so to speak - as in updated everyone's characters. So yes waiting is the only way as far as I know to save the game.
I have a report in RS that uses a cube as a data source. I made some changes to a cube in AS 2005 and I am not sure how I can refresh my existing datasets without deleting everything?
Any feedback is greatly appreciated.
Thanks!
Hai,
Im having a report.When i click on one item in Report it navigate to second Report.Here Second report acts as a Child report.Whne i click on refresh button it goes back parent report.Some times i get error
"
Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request. "
Plz help its very imp.
With regards,
Mahender
Hi,
I'm having serious issues trying to refresh a schema in a SQLDatasource. It is hooked to a stored procedure that takes two varchar(39) parameters. The default parameters in this case are '%'. Note I am working in the designer.
If I set it up as a stored proc, I can't even get the 'test query' to run in the builder.
If I set it up as a select statement, ala 'exec <procname> @p1, @p2', the 'test query' will run.
In neither case will hitting refresh schema work. It returns 'Invalid length parameter passed to the substring function'
The stored proc is nothing special, simply returning a select based upon the parameters.
Thanks,
Nick H