SSAS: How can I filter by customer if I have ~ 3 million customers?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » Business Intelligence » SSAS: How can I filter by customer if I have...

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

SSAS: How can I filter by customer if I have ~ 3 million customers?

Expand / Collapse

Author

Message

Posted Monday, August 17, 2009 3:07 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, August 06, 2010 2:38 PM
Points: 79, Visits: 488

I am new to BI, please help.
I have Product dim, Date dim and products that customer buy each day, so I can browse cube to see how many products in each category that I can sale each month.
Now the problem, I have ~ 3 million customers, How can I analyst by customer? I try to put in the area that show drop filter here but it always hang up due to it try to get 3 million records in.
I need to know by CustomerKey, product that he/she buy during period in 2009(each month). Again total customer that I have is ~ 3 million customers.

Please help, if you need more detail please inform me.

Thank you in advance.

Post #772299

Posted Monday, August 17, 2009 3:33 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

The purpose of BI is to give you the BIG PICTURE about your business. If you're looking at individual customers out of ~ 3 million customers, you're down in the weeds, and BI is probably not the correct tool to solve the business problem that you are addressing. Maybe if you told us more about the actual business problem, we might be able to help you find the right tool.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Post #772324

Posted Monday, August 17, 2009 3:58 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 379, Visits: 676

I would probably want to know what question are you really asking the cube? The reason I ask is that I am certain that someone is not coming to you and asking you what customer 985146 is buying this year?

Yes you can analyse by customer type. I think you really need to sit down with the users and ask them questions like '
If you could only have ten reports on this data what do you need?' Business users always ask for everything at the most detailed level. This is really where you need to understand what they want.

Ells.
Cool

Cool

Post #772343

Posted Monday, August 17, 2009 8:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

Ells (8/17/2009)

Business users always ask for everything at the most detailed level.

Heh... they certainly do. We found a way to fix that at my previous company and it saved huge amounts of time because we no longer need to maintain datamarts, universes (BO), cubes, nor create reports. It also saved a huge number of arguments about what they could have access to or not. We gave them the detail... every bloody byte of it. We created a REPORT server and used the SAN to snapshot a full copy of everything from the PRODUCTION server every night at midnight. Oh sure... every once in a while, they needed a little help... but mostly not.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #772395

Posted Monday, August 17, 2009 11:42 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 379, Visits: 676

People are funny old things. The most commonly requested report is the evrything report. I have seen people fill Excel spreadsheets to more columns and rows than you can possibly view. Coming at it from a technical side we are striving for the opposite end. Just need to get that happy middle ground. Once you have achieved the happy spot of what you are going to report be prepared for it to move.

Some times showing people what 3 million rows and n columns looks like helps them to see that you really cant work like that.

Good luck
Mark.

Cool

Cool

Post #772428

Posted Tuesday, August 18, 2009 12:09 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 3:01 AM
Points: 95, Visits: 308

I have to agree with everybody here. Browsing to such a low level is not what a cube is meant for. Rather create a stored proc that queries the data warehouse directly and returns that data. You can't have a drop down with 3 million rows in it, period.

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

Post #772431

Posted Tuesday, August 18, 2009 6:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, August 06, 2010 2:38 PM
Points: 79, Visits: 488

Thank for all answer, yes they need to know customer 'XXXXXX' what product that he/she buy separate by each month for 1 -3 years and how much for each month.

As I state before, I am new to BI. now I know it is impossible to filter by 3 million customers.
Reply by William, I think it is very good idea.
I will try to use MDX and SSRS for this purpose.

Thank everyone again.

James Jarupan

Post #772639

Posted Tuesday, August 18, 2009 7:23 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 379, Visits: 676

I think you need to do a prototype. Even if you do it on paper to get the layout. I cant see someone is going to sit down and look one after another at 3 million customers. If that was my job I would be very depressed.

My approcah would be to try and group the customers but not knowing what they really are looking for I dont know.

Is it looking for customers who have stopped buying a product?
Is it looking for customers who should be buying a product?
customers whop have dropped their spend?

Theres got to be a reason for this because looking at 3 million customers is going to cost a lot of money.

Keep digging is my best advice

Ells.
Cool

Cool

Post #772671

Posted Wednesday, August 19, 2009 7:58 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 24, 2012 10:02 AM
Points: 339, Visits: 639

There's usually a right way, but sometimes there's a simple way too. This sounds more like a reporting problem than an analysis problem, so keep it basic. Consider creating a report for a single customer and require a parameter (without a list) for the CustomerKey. If they want to see 987654 they must already have that number in hand, so let them type it in. Saves on awkward UI problems like blown out dropdowns, runs quickly and isn't sensitive to huge data sets. If that's not what they want then at least you have a starting point for discussion that doesn't take a lot to create.

Are you lost daddy? I asked tenderly.
Shut up he explained.
- Ring Lardner

Post #773529

Geoffrey Doebler

Posted Wednesday, August 19, 2009 8:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:02 PM
Points: 34, Visits: 307

I'm new to SSRS but could you create one report in SSRS that just lists all the customers, then create a sub-report that would give the detail they need?

Post #773598

« Prev Topic | Next Topic »

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse