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?
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?
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?
I have a big problem to solve: I use MsSqlServer 2000, Analysis service. I have cubes and virtual cubes. I have a dimension of consult, they have a hierarchy that change every month. for example: In june the consult "John" is in D11, in September he is on D14,... Hierarchie: D10 -> D11, D12, D13 - > C1, C2.. For this reason when i want analysis The consult John in a year(12 months) my result is wrong. With this changes in the hierarchie my analysis become wrong! I thought in the end of every month store the cube in a local cube, like a picture of every consult, with his sales e others measures. With this i can analysis the consult in a specific month rightly, but i need the anlysis in a year. And in a year i would have 12 local cubes, then i could acess them and do a right analysis. How could i access 12 local cubes? Can i create a virtual cube with this 12 local cubes? Is there another solution for this problem?
I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.
I am trying to create a DTS Package which will run a SQL query and export the results to an Excel file. I would like to the name of the excel to be "dynamic". What I would like is for the name to be ChronicDownSiteReport - mmddyy.xls. The mmddyy is the date which the package is executed. How can I do this? Also, I want this package to be excuted at 1am every Sunday Morning. I have attempted to schedule this to run, but when I come to work on Monday, the excel file is not present and the email, which is sent telling me that the file was created is not in my mailbox.
Hi, In my application, i have two package, parent package and child package. the parent package is executing child package using a Execute Package Task. "Execute Out Of Process" property of Execute Package Task is set to TRUE. means the child package will be run in separate process not in the process of Parent package. this was working fine, but at a particular client location. its failing the error is "not able to load child package". for me it seems some setting on server restricting to create separate process for child package execution. when "Execute Out Of Process" property of Execute Package Task is set to FALSE. its working fine.
can anyone help what could cause its failure with property set to TRUE.
Hi. 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.
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.
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
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.
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 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.
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?
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. "
i need to transfer the data from allbase (old database) to sql server 2005 .
moreover the structure is different say in allbase we may have 10 tables but now we want more tables .and some tables may have more fields than the previous table's fields for ex: productdigit (previous table name) have 2 fields and present table productline have 4 fields (while using export/import wizard those are set to null but we dont want to set null).
and i dont have knowledge on ssis now i am learning it. but i could not understand how to use those items in toolbox and how to specify datasource for odbc and all.
and please help me to create custom component if needed.
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
I'm getting a connection and then loosing my connection upon refreshing the browser with this script connecting to MSSQL using php, when trying the following:
PHP Code:
$connection = mssql_connect("127.0.0.1","test","") or die("Could not connect mssql db on " .$config['dbhost']);
mssql_select_db("dbName") or die("Could not select database " ."dbName");
Are their other ways to see more error handling in connecting to MSSQL
I have created an SSRS report, but the data does not refresh. I am using the SSRS Report Buidler to create reports and when I run the query in the dataset, the results return as expected. However, when I run the report itself, the dataset appears old.
Hi, I have many buttons in Access ADP that trigger feeding a table with different data. Let's call the table tempTable1. I have 600 buttons that feed the make table with all kinds of data:
Code SnippetSELECT * INTO tempTable1 From AnyDataSourceSPViewTable
. There is another menu bar button that only opens
tempTable1. The result for tempTable1 is always correct when you use Query Analyzer. However Access ADP recognizes the change in the table structure when the connection is refreshed. Is there is any way that we can automate the connection refresh procedure or an easier way to get the desired results?
Am using Access 2003 connected to SQL 2005 and trigering the events via VBA.
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???
I am learning SSIS and I am working through the Wrox book on 2005 SSIS. When I try to Import the files from the AdventureWorks2000 database I cannot see the Human Resources files. I was able to go through the wizard once and see the files and select them and proceed, but now I cannot see them. Please keep in mind I am a newby to this. What should I check for or what am I doing wrong?
Is there anyone out there that can help me - please! I'm about to put my head through a wall.
I used to be able to create SSIS packages in VS 05. It's been a while since I've done it, but when I went in the other day, it would let me - all I can do is get errors. It tells me an "error prevented the view from loading" when it opens in control flow (blank packages and ones I created before all this started) and I get this error :
"Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (System.Windows.Forms)"
If I go over to the Data Flow tab and there are no errors until I click "Click here to add a new Data Flow task", then I get this :
"Object reference not set to an instance of an object. (Microsoft.DataTransformationServices.Design)"
So... here's what I've tried.
Uninstalled and reinstalled VS 05 and SP1 (this was before I realized the problem was with SQL).
Uninstalled and reinstalled Office 2003 Web Tools.
Made sure all the MSXMLs were registered.
Uninstalled and reinstalled all SQL Servers and Tools/Components.
Made sure all SQL Services were running under Local Account
Made sure Integrated Services service was running.
Applied all SQL patches.
I cannot get this to work and I need to be able to create packages. The only other step I can think of is wiping my computer and I don't want to have to do that.
Please please can anyone help me?? I've been scouring the internet and working on this for 1 day and 1/2.
I am 100% new to SQL. I have used Access for ever. I need to know how to take a Access 2003 database and covert it over to SQL 2005. I have no idea how to complete this. I was told to do a DTS package but I do not see any options for this.
I have been posting to the Data Presentation Controls forum for about a month regarding a problem I've been dealing with.
http://forums.asp.net/thread/1223055.aspx
What it boils down to is that on a button click event, I was updating some records, then re-executing a SELECT statement to get the records back out and rebind my DataGrids. This was happening too quickly and the data was not being updated in time before the SELECT was executed. So my grids would still display "old" data.
How do I get SQL Server to commit the UPDATE before my C# code continues?
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.
