SQL Server Performance Forum – Threads Archive

Grouping large data

I have got 3 huge tables
here is the sample data table 1
Companyname id year volume_for_jan vol_for_feb…..vol_dec
abc 1 2000 34 333 ……555
2 2000 33 22 666 table 2
Companyname id year volume_for_jan vol_for_feb…..vol_dec
rrr 44 2001 55 66………….888
24 2001 22 35 454 each table has almost 800000 rows I need to generate a report out of these tables which should be as follows Company name Id Year Vol_for_jan Vol_for_feb………
abc 1 2000 34 33
2 2000 33 22
rrr 44 2001 55 66
………………………………………….. when i try to run the report in business intelligence ,it takes forever to run and doesnt even produce the report at the end… what shall i do????

Did you use indexes in the table?
Do you want to combine the results from two tables? Madhivanan Failing to plan is Planning to fail

Check the indexing of your tables or use Index Tunning wizard —————————————-
http://dineshasanka.blogspot.com/

Use the Index to Improve the Performance of your query. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_30s5.asp

Can you post the long running query code? What is "business intelligence" you mentioned? Some kind of olap or sql client?

I guess "business intelligence" is a report generator like Business Objects, Reporting Services or similar. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis I guess "business intelligence" is a report generator like Business Objects, Reporting Services or similar. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Yes It sounds Crystal Reports [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

BI software creates cubes, an aggregate form of the measures. And usually, they have scheduled refresh run (except for real time). The problem might be indexes/config or how you implement it. May the Almighty God bless us all!
www.empoweredinformation.com

well my problem is i dont have a primary key on my tables..and if i need to insert an auto number field sql doesnt let me do that…
wat shall i do???
i really need to solve this problem.. pls pls help guys

quote:Originally posted by mita well my problem is i dont have a primary key on my tables..and if i need to insert an auto number field sql doesnt let me do that…
wat shall i do???
i really need to solve this problem.. pls pls help guys

Now I understand even less what is the problem you want to be solved. First you said you have long running query, now the problem is that you can’t insert autonumber field.
I asked if it is possible to post the long running query but you haven’t answered. I am afraid we can’t help you unless you are much more specific with your request. Based on your first post it looks like that you need union from two tables that will return more then 1 500 000 rows. Such query would take a lot of time and no optimization is possible. I guess you have some kind of criteria you haven’t mentioned yet. In your second post you are talking about inserting "autonumber field" and I really don’t understand how it is related to your first post. Please clarify what the problem is.

well the reason i talked about inserting an auto number filed was to create a primary key on the table and then create a clustured index on that column.. i want a report in a drill down sort manner where in the first grouping level is the company name,then next level is id,then next level is year and then the volumes(vol_jan,vol_feb etc) i have to export the final report in excel because we send the excel reports to our clients.. so i am not very sure wheather i have to use the business intelligence or not..because the whole purpose of running a query is just to generate a report …so wat shall i do now?? In all my tables,Id field is the common one. as in it has got common values in all the tables.. i hope i am a bit clear this time??
My apologies if i confused u because i have been really trying hard to figure out the way to solve this problem

Mita, I am afraid your question is more about sql server 2005 and business inteligence studio and I have no experience with them. I have some restricted experience with previous version of analysis services but I am far, far from expert in that area. So, I’ll just suggest something that you might have already tried and it is probably obvious to you. My guess that the problem can be solved by defining cube on top of view that is union of yearly tables. Maybe you would recieve more useful answers if you post the question in analysis services forum.

What SQL Server version are you using? Most of the time, primary key should be defined in the table. It helps also on performance but lets go back to your request: the report. If you have a BI software, you can probably use its capability to generate the dimensions and cubes. For instance, in business object, you have to generate the universe, drag and drop the fields from the universe and run the report. The report can then be exported to Excel. Similar steps with Crystal report and Cognos. If you want to old fashion way using Excel and Analysis services, you have to create the dimensions and cubes, and run to generate the aggregates. Once you have done that, you can connect the excel through SQL olap provider and use the Excel pivot table capability to drag-and drop columns. Check the BOL for more information. If you are using Report services, see the web for examples on generating cubes/reports. May the Almighty God bless us all!
www.empoweredinformation.com

hi
thanks for your help..
well i am using sql 2005 only..but i ve got no idea how to use the universe or cubes…
can you give me a quick direction or send me some links so that i can move onto the right path??

start here:http://msdn2.microsoft.com/en-us/ms170246.aspx May the Almighty God bless us all!
www.empoweredinformation.com

Hi thanks for the link.. it did happen to be very useful for me
but i have not been able to completely design a cube with it…
i created a new analysis project and then i added my two tables in the data source view.. but when i try to run the cube wizard, it asks to create the fact table which idont know how to do…
how shall i solve my problem with the analysis services???????????

]]>