August 17, 2009 at 3:07 pm
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.
August 17, 2009 at 3:33 pm
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
August 17, 2009 at 3:58 pm
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.
😎
August 17, 2009 at 8:07 pm
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
Change is inevitable... Change for the better is not.
August 17, 2009 at 11:42 pm
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.
😎
August 18, 2009 at 12:09 am
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
August 18, 2009 at 6:51 am
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
August 18, 2009 at 7:23 am
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.
😎
August 19, 2009 at 7:58 am
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.
[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner
August 19, 2009 at 8:53 am
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?
August 19, 2009 at 9:06 am
Nope. You could, but it's not a great idea. The reason this is difficult is that 3 million of anything in one report is too big to use. You can't find anything, it's too fat to move around and it beats up your server for any other users.
You create a report with a parameter called CustomerKey, you don't provide a default value and you don't allow it to be null. Put something helpful in the prompt field, like "Enter a Customer Key (eg 987654)" and when they go to run it they'll only get a blank report with that text at the top next to an empty box where they have to type something. It's not super helpful, but most users love trading helpful for direct, simple and fast.
EDIT: if they don't have a way to get the customer key then you might have to whip something up, maybe a kind of search deal rather than a simple list, but the initial question sounded like that number was available.
[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner
August 19, 2009 at 9:13 am
A key piece of OLAP structure that you're missing (or at least not referring to) are hierarchies. You would normally present the user with hierarchies that enable them to logically descend from 'All' to a single specific node (be it customer, product etc). Sure, you can have 3MM lowest level members in your hierarchy, we often have signifcantly more than this, but you also need to find and presnt logical levels within hierarchies to allow the user to navigate these items.
As has been mentioned above, I doubt anyone can honestly say that they can 'analyze' (I use this lightly in this context) 3MM customers at the customer level, but they can definitely analyze the groupings of these customers, and then can analyze individual customers but not all 3MM at one time, at the customer level.
So, go back to your data and your data consuming clients, and determine what items are candidates for building some hierarchies. Keep in mind, you can have more than one (e.g. By customer types; By customer region; By Salesperson etc). Also be aware that *without* some attribute hierarchies (where the assumption is that you'll build User Hierarchies based on these), your cube performance may suffer as you've provided SSAS with no candidates for aggregations and like hierarchies, aggregations are a pretty fundamental component of a solid OLAP solution.
HTH,
Steve.
August 19, 2009 at 9:22 am
Utterly agree. I was aiming in that direction but this was much a more useful approach.
[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner
August 19, 2009 at 9:29 am
Cris,
I giggled at the thought of the drop down list. You are right I think a simple report where they input the customer number is spot on as a starting point, it will get people talking.
Steve,
I agree if there where hierarchies to make the navigation in Olap reasonable or if there was some way of understanding which customers are being targeted it would make a cube possible.
Ells.
😎
August 19, 2009 at 10:04 am
Another option to consider in connection with the hierarchies is building a drill-through report. In your cube, the hierarchy would only go down to a given level (say city) and if you want further details, you could right-click and get a report with the details for that particular member.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply