March 31, 2010 at 1:48 am
Hi all,
I am totally new to the SQL server BI tools. As our client needs to analyze the data and to view the data through SSRS.
Database used : MS Dynamics NAV
server : SQL Server 2005
In the NAV database you can create the companies.The hiearchy is like this
Database
|
Companies
|
Tables
|
Forms
ex: If the database consist of 3 companies as X,Y,Z, when i viewed in SQL server management studio, it shows as
dbo_x.customer
dbo_y.customer
dbo_z.customer
It stores as separte tables.
The scenario is i have to analyze the data to find the top sales order and top customers for all the companies as well to the individual companies and to display it as a report.
If i use the UNIONALL queries to combine the companies it's taking too much of time.
Whether i need to use the SSIS.....If i need means how should i have to proceed????
The database consist of enormous data. In SSIS can i use the control flow task to connect the OLE DB and where should i have to get this output????
I need the method to proceed further . can anyone help me out ???
Waiting for your valuable suggest
March 31, 2010 at 10:25 pm
waiting for your valuable suggestions.....
March 31, 2010 at 11:28 pm
Please provide more info. We need sample queries that you have tested as well as table structure. In order to test, we would also need test data provided.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 10:27 pm
Hi,
Thanks for your response.I'll explain it with an example:
NAV2009 is a database.It consist of 3 companies say A,B,C. The table is same for these companies ex: Customer is the table which consist of customer details of these three companies.
In SQL server Management studio, if i view the NAV2009 database,it shows like this
dbo.A.Customer
dbo.B.Customer
dbo.C.Customer
so i wrote the query like this
select * from [NAV2009].[A].[Customer] as X
union all
select * from [NAV2009]..[Customer] as Y
unionall
select * from [NAV2009].[C].[Customer] as Z
After executing this query i get a combination of all the records from the three companies...
when the data is more in each company, its taking more time due to Unionall....
Anyother alternate solution to solve this problem?????
The client needs to analyze this database with a combination of all companies as well as individual companies and to make available on the SSRS too???
Canany one help me out????
April 3, 2010 at 1:58 am
Some of your query response time is going to depend on indexing strategies employed. Some of it will depend on the actual structure of the query (i.e. the use of select * over designating column names and how many columns) and the final factor will be in amount of data.
If all data must be returned, you are a bit hampered there. Thus look at your query structure and index scheme.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 3, 2010 at 7:24 am
You can optimize the query by using local partitioned views. These use check constraints to eliminate unneeded partitions at compile time.
I must confess that I dislike NAV fairly intensely due to its crazy database structure 😉
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply