April 10, 2009 at 1:35 pm
I have a fact table that has the following columns.
Company(parent) eg: 3COM
Sub company(Child) eg: 3COM Inc, 3COM PLC etc....
There are close to 1.7 million rows in the fact table. Its literally impossible to pull all company names because the connection timesout or the number of rows exceeds the limit of pivots in Excel 2003. Is there any property or any way to group the companies based on the alphabet letter so that the end user can pull only companies that start with a specific letter of the alphabet?
Thanks!
Sanju.
April 10, 2009 at 2:00 pm
1. Time out : I thought your table need to Index tunning. If your Database version is 2005, I recommend that use the indexed view.
If you give more detail table structure and index in currently, I'll make the SQL Script, too.
2. Limit Row on Excel : Excel 2003 only support around 65000 rows, but Excel 2007 support almost unlimited.
3. Searching Alphabet : You can make query in this way.
DECLARE @abc CHAR(1)
SET @abc = 'A'
SELECT * FROM [your table] WHERE (COMPANY_NAME >=@ABC AND COMPANY_NAME< CHAR(ASCII(@ABC) + 1))
I thought you can make it complete with more algorithm to solve case senstive and numbers.
Thank you.
Hang Deok Cho.
hyemang@gmail.com
DBA
April 10, 2009 at 2:59 pm
or, you could add a new field to your view over the dimension table (assuming you're using views) where you do a LEFT(Customer_name, 1) AS Customer_First_Letter which will give you the first letter of each customer record. You then use this attribute in a user hierarchy above the Customer level.
If you're not using views, you could just add a calculated field in the data source view in the project, and do the same thing (ie LEFT(...... ) and bring it in as per above.
Note, you may want to set the customer level as not displayed so that your users don't try to drag it in to a pivot table, that will fail.
Steve.
April 12, 2009 at 6:03 am
Steve,
I've have no idea about MDX so don't know how the calculated field should look like. If you could build that for me. Moreover I don't see the LEFT function listed anywhere in the functions list. Would appreciate if you could also explain how to use views in the datasource view.
Thanks!
Sanju.
April 12, 2009 at 7:55 am
Hi, it's not MDX just TSQL (assuming a sQL server backend/data source). If you look in Books online for LEFT you'll find severl examples. TO use this in the data source view, right click the table (in the DSV) and select new calculated field,and then add your tsql (e.g. left(Myfield, 1) ).
then use this new field as an attribute in the dimension, and then also as a level in a user heirarchy.
hth,
Steve.
April 12, 2009 at 10:28 am
Steve,
Thanks a tonne!!!!!!! It worked. Today morning when I read ur initial response I thought I need to put in an entire select statement with Left(...) etc into a Named Calculation field and that threw me an error. I took me a while to realise that it makes no sense having the entire select statement there. That lead me to think that probably u meant a calculation field wherein (from what i know) MDX is used. Now I know that only the expression goes into the Named Calculation field.
Thanks again!
Sanju.
April 12, 2009 at 10:30 am
Thanks for the resply! That sure works but I needed a much simple solution and I think I got it. Thanks anyways!!!!!!!!!!!!
April 12, 2009 at 10:33 am
The last response from me was for hyemang!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply