|
As Damian suggested, storing the calc values is technically incorrect but may be valid for performance reasons, this will depend greatly on your usage of the data.
I would probably set up 2 structures, the current data in a normal relational database using views to supply the calcs and a data warehouse with a properly designed cube to handle the historical data. This has been done many times, I'd be surprised if there is not a case study out there on the exact structure you are proposing.
|
|
|
|
|
Your longest time series will have slighly more than 12,000 datapoints assuming you download data from 1970, which is google's start date.
Your dataset using option 2 should be at most 1 GB per 2,000 stock ID assuming you are only loading data from a single source and chose the right data types. So if you have 16+ GB of memory, the entire database might reside in memory and you would have associate performance pickups.
Assuming you do not have that powerful of a machine (otherwise, your firm might already have this data), then using a combination of option 1 and 2 might be best for performance of your application. You store historical data in option 2 format and precompute the data in option 1 format for the next business day. I am assuming that you are not incorporating in live data right now. The precomputed data will not take up much space and can be overwritten each night or as needed (e.g. you could add a time stamp to option 1's table. If the time stamp is too old, then you call a function to fill the table before returning results.)
modified on Sunday, November 22, 2009 11:26 PM
|
|
|
|
|
I am trying to create a TFS report which requires MDX. I am making a report that requires a calculated member. I am not 100% sure how this is done. The member i am trying to do is a difference calculation.
For example:
Table
Sept 1 Sept 2 Sept 3
Actual 0 32 58
Remaining 163 140 132
Difference 0 9 50
The calculation for the difference is as follows:
Actual Effort for that day - (Work remaining previous day - Work remaining that day)
32 - (163 - 140) = 9
Can someone help please? 
|
|
|
|
|
Without any table definition of columns, it's hard to guess at what to provide. As a suggestion, though you may need to join the table on itself in order to have access to "today's" values and "yesterday's" values. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I have installed Business Intelligence Development from SQL Server 2008 to use to create TFS Reports In Visual Studio 2008. When I add a new report a query builder window opens. This is where Measures and other information to get the data is done. This is where I am trying to create the Calculated member. Any suggestions??
|
|
|
|
|
Hi all,
I want to export my data from a SQL-Server table to an Excel-File.
The following code shows my routine.
oConnection.ConnectionString = "Provider=SQLOLEDB; Server=Servername; database=ABC; Trusted_Connection=yes; User ID=ABC_MAN; Password=secretABC";
oConnection.Open();
oCmd.Connection = oConnection;
oCmd.CommandTimeout = 60;
oCmd.CommandText = "SELECT id AS NO, name AS NAME INTO [Excel 8.0;DATABASE=Test.xls] FROM MyDataTable WHERE typ=1";
oCmd.ExecuteNonQuery();
But instead of an exel file the string creates a new table with the name Excel 8.0;DATABASE=Test.xls
Where is my fault ? Different examples has shown that this should work 
Thanks
Frank
|
|
|
|
|
The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.
Tutorial[^]
|
|
|
|
|
Thank you,
I know what SQL normally does ...
but I think you don't know what I am expecting from this query ...
Thank
Frank
|
|
|
|
|
|
It looks like you want something like this:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Email FROM [Sheet1$]')
SELECT Name, Email FROM tblnames
or
1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
I'm not sure of the correct syntax since I haven't used it before.
I found it at after searching for "sql server export to excel [Excel 8.0;DATABASE=Test.xls]"
Scott
|
|
|
|
|
Thank you,
have tried your suggestions, but got some OLEDB-unknown errors, what ever that meens.
(But I think,(not sure) the disadvantage is that the Excel-table must already exist)
But thanks alot
Frank
|
|
|
|
|
|
Hi Richard,
it's an interesting link - I will convert the code to C# - will take some minutes 
I will let you know the result.
Tnx
Frank
|
|
|
|
|
Try this
Create an Excel file named testing having the headers same as that of table columns and use this query
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
To export data from Excel to new SQL Server table,
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
To export data from Excel to existing SQL Server table,
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
Obtained from SQL Server Forums - Export to Excel
Hope this helps.
Niladri Biswas
|
|
|
|
|
Thank you,
I also found this example, but the examples assume, that an Excel file already exists.
But I remember, 5 years ago, I could generate a new file with the sql-sting above (within VB6) without an additional action.
Why can't I do that today - or how can I do that today ?
Tnx for your time
Frank
|
|
|
|
|
if u dont want to use openrowset or bcp then best way is
use string builder and then response.write with
response.AppendHeader("Content-Type", "application/vnd.ms-excel");
here you can give file name n storage location too.
Reasons are not Important but Results are Important.
Swati Tripathi
modified on Friday, November 20, 2009 3:05 AM
|
|
|
|
|
I have an internet cafe software uses SQL Express 2005 and I am creating a setup package using InstalShield 2008. My plan is to save the MDF and LDF files in my folder not Microsoft SQL Server folder. My folder is C:\Program Files\Jassim Rahma\TICS\Data but InstallShield fails to complete. When contacting InstallShield support they said it's due to folder permission! and they advise to use the SQLACCOUNT.
I want to know how can I pass the SQLACCOUNT in InstallShield if my software will be used in different OS, XP and higher?
|
|
|
|
|
You're running into UAC issues, they are assuming your SQLACCOUNT has permissions to the program files folders which are locked down unless you are running as admin. I suggest you look into putting your data files into the application data under the user account section (there is an ALLUsers account I am sure), after all this is what it is designed for 
|
|
|
|
|
Hi,
My application requires certain reporting queries to be totally dynamically determined from application level. What I have done [rather, am doing] is build the query as indicated from UI and store it as a SP in the database.
The confusion is, say, someone is running the reporting SP, [which takes around 30 secs time to get executed and start returning data], and at the same time someone else trigerred an Alter on the SP.
I do not want any one to get an exception, not even a timeout. So what I have planned is to have a list of the dynamic SP's in DB table, and indicate by a flag, whether it is in use, or not, also whether it is further callable or not. So we can effectively give proper messages instead of exception, if any [I actually do not know, whether there will be an exception]
So, my questions are:
1. What happens when someone calls for an Alter script on a SP while it is running
2. What if, due to some problem, like a connection drop or something, the running query stops executing. As the flags are shared, I cannot run these on a transaction. Essentially, I do not know what happens when the connection over which the query is invoked gets dropped midway in execution. Does the query stop executing, raising an error, or does it complete the execution, and stop silently?
Thanks in advance
|
|
|
|
|
Why not just trap the error returned if the SP is running when you try and do the alter, then wait x seconds and retry? Naturally you would need to put some sort of check in to prevent endless loops. This would also cover connectiosn dropping etc.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I dont like your design. My advice to you is dont put the dynamic query in a stored procedure instead put it in your application business layer. you are using dynamic sql, so you are not going to make use of the query plan caching etc [its going to be recompiled]. This being said, if you insist on your design, create a table that keep records of whats running. When you get a request of changing an sp, query that table and check if the sp is running. if the sp is running report back a friendly error.
|
|
|
|
|
I'll back up Bassam, your design sucks (following the theme in the Lounge). ALTERing a stored proc to meet these requirements is nuts. Change your design to generate the SQL in the business layer and exec the sql string in your proc (I hate this as you are left open to sql injection) or parameterise EVERY option the user can select and pass in the data as parameters and build your select in the proc.
|
|
|
|
|
Respected,
how we Convert selected table Data into Tally .Please help.
Thanks.
|
|
|
|
|
|
Tally is the most famous accounting software in India.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
