April 4, 2011 at 2:07 pm
Stefan Krzywicki (4/4/2011)
That is one of the areas I'm looking for guidance. What kinds of patterns should I be looking for? I don't think they currently do detect it other than catching someone in the act. Someone ringing up the same order 10 times in an hour when that isn't usually rung up 10 times in an hour? Someone ringing up an unusually large sale? Someone ringing up 1000 ketchup packets as a side? Someone ringing up an unusually small sale?
Ah, you're looking to establish a baseline parameter set *first*, then find outlyers. What's the term for that, pattern exceptions? I'd have to go bounce around the web a bit.
General practice is first to establish the baseline, then look for outlyers slowly, widening the rules until you find ones that work for you. It's an iterative business process to decide what should be flagged, and the rulesets can get rediculous. Mind you, you're building a ruleset for each type of check (ie: comparing different orders vs. reviewing a single order) for exceptional activity.
I'm not sure SSAS/Cubes are where you want to go with this until you figure out the direct data logic. Some of it, sure, it'll make sense to cube. Other components won't.
Statistical Analysis is probably your best bet when you start doing this. Note, most of outlier statistics is for the purpose of excluding them when trying to find the common ground... you want the opposite, but the concepts are the same. Some quick items of interest (don't get too deep into the wiki article, you'll care about the concept, not single datapoint mathmatics).
http://en.wikipedia.org/wiki/Outlier
Rather dry but short and to the point: http://www.itl.nist.gov/div898/handbook/eda/section3/eda35h.htm
Another random article that brought up a point or two I didn't see in the above: http://www.statisticaloutsourcingservices.com/Outlier2.pdf
They're pretty much off the top of google, but they'll give you an idea of where you're going here. First you need to determine how to find your baseline(s)... then finding the outliers is much, much simpler.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 2:33 pm
One of the first things you will need to do is to set up comparisons between sales, orders, and store volume.
For example,
Stores over 10k in volume:
Sell 5k of fries, order 6k of fries from the supplier.
Stores over 20k in volume:
Sell 8k of fries, order 9k of fries from the supplier.
Both ABC and XYZ are 20K stores
ABC sells 8k of fries, and orders 9k of fries from the supplier
Store XYZ, however, sells 5k of fries and orders 12k of fries from the supplier.
This indicates that they have an issue someplace!
There are significant number of these baselines that can be examined, especially in a retail setting. To throw a few out:
Average amount per sale
Average numbers of each item sold
Profit vs. the number of employees.
The number of comparisons you can make is only limited by the data you track. This is not only useful for shrinkage, but also marketing, training, qaulty control, and customer service.
Talk to someone from WalMart. They have numbers that analyze everything!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 4, 2011 at 2:41 pm
Well, it looks like I (finally) got a QotD question that didn't have everyone up in arms over it... whew!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 4, 2011 at 2:49 pm
WayneS (4/4/2011)
Well, it looks like I (finally) got a QotD question that didn't have everyone up in arms over it... whew!
Well, if you want, we can certainly nitpick it :hehe:
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 4, 2011 at 2:57 pm
Craig Farrell (4/4/2011)
Stefan Krzywicki (4/4/2011)
That is one of the areas I'm looking for guidance. What kinds of patterns should I be looking for? I don't think they currently do detect it other than catching someone in the act. Someone ringing up the same order 10 times in an hour when that isn't usually rung up 10 times in an hour? Someone ringing up an unusually large sale? Someone ringing up 1000 ketchup packets as a side? Someone ringing up an unusually small sale?Ah, you're looking to establish a baseline parameter set *first*, then find outlyers. What's the term for that, pattern exceptions? I'd have to go bounce around the web a bit.
General practice is first to establish the baseline, then look for outlyers slowly, widening the rules until you find ones that work for you. It's an iterative business process to decide what should be flagged, and the rulesets can get rediculous. Mind you, you're building a ruleset for each type of check (ie: comparing different orders vs. reviewing a single order) for exceptional activity.
I'm not sure SSAS/Cubes are where you want to go with this until you figure out the direct data logic. Some of it, sure, it'll make sense to cube. Other components won't.
Statistical Analysis is probably your best bet when you start doing this. Note, most of outlier statistics is for the purpose of excluding them when trying to find the common ground... you want the opposite, but the concepts are the same. Some quick items of interest (don't get too deep into the wiki article, you'll care about the concept, not single datapoint mathmatics).
http://en.wikipedia.org/wiki/Outlier
Rather dry but short and to the point: http://www.itl.nist.gov/div898/handbook/eda/section3/eda35h.htm
Another random article that brought up a point or two I didn't see in the above: http://www.statisticaloutsourcingservices.com/Outlier2.pdf
They're pretty much off the top of google, but they'll give you an idea of where you're going here. First you need to determine how to find your baseline(s)... then finding the outliers is much, much simpler.
Great! Thanks for the links.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 4, 2011 at 2:59 pm
Michael L John (4/4/2011)
One of the first things you will need to do is to set up comparisons between sales, orders, and store volume.For example,
Stores over 10k in volume:
Sell 5k of fries, order 6k of fries from the supplier.
Stores over 20k in volume:
Sell 8k of fries, order 9k of fries from the supplier.
Both ABC and XYZ are 20K stores
ABC sells 8k of fries, and orders 9k of fries from the supplier
Store XYZ, however, sells 5k of fries and orders 12k of fries from the supplier.
This indicates that they have an issue someplace!
There are significant number of these baselines that can be examined, especially in a retail setting. To throw a few out:
Average amount per sale
Average numbers of each item sold
Profit vs. the number of employees.
The number of comparisons you can make is only limited by the data you track. This is not only useful for shrinkage, but also marketing, training, qaulty control, and customer service.
Talk to someone from WalMart. They have numbers that analyze everything!
Thanks, those are some good examples and will widen my data pool.
I used to work for Dunn & Bradstreet on a data analysis tool, but this is a different kind of analysis.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 4, 2011 at 3:00 pm
I like Craig's advice, but Michael's is probably where I'd start. Usually shrinkage affects inventory, so I'd compare ordering with sales and look for discrepancies. One of the things you want to do is compare sales v orders v profitability. Have an accountant or manager see if the numbers are in line with each other. Then start looking for pattern deviations.
You can't just use history because someone might have been taking a five finger discount for years, and your historical data would not be correct.
April 4, 2011 at 3:04 pm
Steve Jones - SSC Editor (4/4/2011)
I like Craig's advice, but Michael's is probably where I'd start. Usually shrinkage affects inventory, so I'd compare ordering with sales and look for discrepancies. One of the things you want to do is compare sales v orders v profitability. Have an accountant or manager see if the numbers are in line with each other. Then start looking for pattern deviations.You can't just use history because someone might have been taking a five finger discount for years, and your historical data would not be correct.
Fortunately I don't have to start producing data right away. I can take the time to put together a list of types of analysis and go over it with the interested parties so I'll probably mention both of these approaches and more with some specific examples for our business.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 4, 2011 at 3:55 pm
Steve Jones - SSC Editor (4/4/2011)
I like Craig's advice, but Michael's is probably where I'd start. Usually shrinkage affects inventory, so I'd compare ordering with sales and look for discrepancies. One of the things you want to do is compare sales v orders v profitability. Have an accountant or manager see if the numbers are in line with each other. Then start looking for pattern deviations.You can't just use history because someone might have been taking a five finger discount for years, and your historical data would not be correct.
Steve's correct. I was trying to give you the research start point rather then specific items to check on. He's right on hindsight. Start with details and working out the broader picture is how I learned those basics as well and will probably serve you better.
So with that in mind, here's some ideas:
Store level:
- Amount of item sold since between times ordered. Ignore theoretical 'existing inventory'. That's rarely right anyway.
- Amount of profit vs. amount of expected profit. This you'll probably want in regional groupings. You're not looking at any store week to week, you want to see what this week to last week's percentages was across all the stores, and see if anyone missed the boat. There may be exceptions here to your exceptions. (Did I mention these can get complex?)
- Exception sale differences. When a manager or stocker position is NOT on duty, does inventory ordered (especially useful in just in time locations, like a Walgreens) match closer to sales? Recheck history when a blip comes up on a specific person. Note: This is not a be all/end all. IE: We had wondered who was eating the damned toiletpaper... It was the janitor. The manager had told him to grab it off the shelves if the employee bathroom was out. *facepalm*. I really don't want to know what everyone in the store did that week.
Per sale level:
- Create a sale history.
- - Find an average number per item per sale. Give it a 20% delta allowance and find your outliers. You may need to categorize, or even subcategorize, the allowance here.
- - Find the reasonable maximum CASH PURCHASE sale amount. Look for oversized numbers. It's easy to void one or two items of a large sale after the fact. This won't indicate a problem, it's merely an indication to glance and doublecheck.
Per Employee Level:
- Get an average sale/day history. Give it an allowable difference, and then check on it. The guy running the photo shop/pharmacy is going to have a major difference in volume from the person who usually runs the front register.
Why all these items? Well, honestly, when I did this we were more concerned about cash to pocket issues then stock issues. Most of your stock indicators are going to come from noticing higher then expected inventory orders while certain people are in (or not in) a store. The problem is noone ever has a per day inventory count, it's just too hard to keep except on the high end goods, and everyone loses a box of something behind the rack now and then.
I wish you luck in this... It's not fun, and there's no way to not feel dirty when you're finished doing it. :rolleyes:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 4:03 pm
CirquedeSQLeil (4/4/2011)
WayneS (4/4/2011)
Well, it looks like I (finally) got a QotD question that didn't have everyone up in arms over it... whew!Well, if you want, we can certainly nitpick it :hehe:
Congratulations Wayne, fully understand how you feel. But if you want I most certainly can join Jason in moaning and groaning about asking a question on a product that has not yet been fully released for manufacturing.... or I can make a deal with you
in return please do not pick on my next question coming up I think on April 12th or 13th
April 4, 2011 at 4:56 pm
Gianluca Sartori (3/31/2011)
Stefan Krzywicki (3/31/2011)
If they could both retire, that'd be one thing.I think I will have to wait 40 years more for that, at best. They keep raising the retirement minimum age every year.
Well, it will be nice to work on SQL Server 2051. 😀
No that year it will be SQL Server 2048 R2...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2011 at 4:57 pm
Roy Ernest (3/31/2011)
Out of Curiosity, isnt 90K US or 80K Euro the normal salary for a Sr. DBA?
Definitely depends on region AND size of the company. I'm looking now and I'm seeing everything from 65k-90k. I haven't seen anything in 6 figures.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2011 at 5:03 pm
Chris Morris-439714 (4/1/2011)
Congratulations to Paul White, who's joined the elite through hard work and outstanding contributions to the community. Well done Paul, very well deserved.
+1 many times over...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2011 at 5:04 pm
Jack Corbett (4/4/2011)
Roy Ernest (3/31/2011)
Out of Curiosity, isnt 90K US or 80K Euro the normal salary for a Sr. DBA?Definitely depends on region AND size of the company. I'm looking now and I'm seeing everything from 65k-90k. I haven't seen anything in 6 figures.
How far are you willing to move? I have heard of some stuff in NY, Seattle, Las Vegas and Ut.
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 4, 2011 at 5:35 pm
Craig Farrell (4/4/2011)
Steve Jones - SSC Editor (4/4/2011)
I like Craig's advice, but Michael's is probably where I'd start. Usually shrinkage affects inventory, so I'd compare ordering with sales and look for discrepancies. One of the things you want to do is compare sales v orders v profitability. Have an accountant or manager see if the numbers are in line with each other. Then start looking for pattern deviations.You can't just use history because someone might have been taking a five finger discount for years, and your historical data would not be correct.
Steve's correct. I was trying to give you the research start point rather then specific items to check on. He's right on hindsight. Start with details and working out the broader picture is how I learned those basics as well and will probably serve you better.
So with that in mind, here's some ideas:
Store level:
- Amount of item sold since between times ordered. Ignore theoretical 'existing inventory'. That's rarely right anyway.
- Amount of profit vs. amount of expected profit. This you'll probably want in regional groupings. You're not looking at any store week to week, you want to see what this week to last week's percentages was across all the stores, and see if anyone missed the boat. There may be exceptions here to your exceptions. (Did I mention these can get complex?)
- Exception sale differences. When a manager or stocker position is NOT on duty, does inventory ordered (especially useful in just in time locations, like a Walgreens) match closer to sales? Recheck history when a blip comes up on a specific person. Note: This is not a be all/end all. IE: We had wondered who was eating the damned toiletpaper... It was the janitor. The manager had told him to grab it off the shelves if the employee bathroom was out. *facepalm*. I really don't want to know what everyone in the store did that week.
Per sale level:
- Create a sale history.
- - Find an average number per item per sale. Give it a 20% delta allowance and find your outliers. You may need to categorize, or even subcategorize, the allowance here.
- - Find the reasonable maximum CASH PURCHASE sale amount. Look for oversized numbers. It's easy to void one or two items of a large sale after the fact. This won't indicate a problem, it's merely an indication to glance and doublecheck.
Per Employee Level:
- Get an average sale/day history. Give it an allowable difference, and then check on it. The guy running the photo shop/pharmacy is going to have a major difference in volume from the person who usually runs the front register.
Why all these items? Well, honestly, when I did this we were more concerned about cash to pocket issues then stock issues. Most of your stock indicators are going to come from noticing higher then expected inventory orders while certain people are in (or not in) a store. The problem is noone ever has a per day inventory count, it's just too hard to keep except on the high end goods, and everyone loses a box of something behind the rack now and then.
I wish you luck in this... It's not fun, and there's no way to not feel dirty when you're finished doing it. :rolleyes:
Thanks, those are some great ideas to consider and will likely form part of one of the methodologies we end up using. When I worked for Dunn & Bradstreet, the analysis was to let large chains see what % of their supplier's business they were and use that information to squeeze better deals out of the suppliers. Felt pretty dirty at that job too. : -\
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 25,351 through 25,365 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply