July 2, 2014 at 2:08 pm
say for example...
sales by brand...week on week comparison?
sales by category this year to date v last year to date?
yadda, yadda -----------
have you considered using a "cube" (SSAS) and linking excel to that
This is interesting what you say....as i see that in my excel output, i will have to pull 1 to 52 weeks aggregations (summation, cumulative, percentiles) for more than 1 KPI's at a time, brand wise, country wise, category wise, manufacturer wise etc. So for each KPI data , i will have to query server database again and again and get each data into excel to do further aggregations, which is not a feasible option.
Yes you are right, its a learning curve i will have to take. i donot know anything about SSAS cubes but i will definitely start googling. If you know of any resources w.r.t excel vba with SASS Cubes, please do share the links.
right now, trying to figure out best approach.
July 2, 2014 at 4:00 pm
I'm giving a completely different approach here.
If you insert a pivot table in excel, you can connect directly to the table and pivot any way you want. Being a pivot table, you can connect to the table even if it has millions of rows without a problem (as long as your aggregations don't surpass the limit of rows/columns).
July 2, 2014 at 11:13 pm
Yes, but cumulative across weeks i.e. Sale_week columns for each Description (SKU) will result in the same number of Description (SKU) rows. So data will still be huge to handle in excel via pivot. thats my concern.
Also, wanted to ask you, does SQL Server handle more than 255 columns? If so, what is the limit?
July 3, 2014 at 7:02 am
sifar786 (7/2/2014)
Also, wanted to ask you, does SQL Server handle more than 255 columns? If so, what is the limit?
http://msdn.microsoft.com/en-us/library/ms143432.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2014 at 7:45 am
sifar786 (7/2/2014)
no one on this forum knows how to use dynamic sql to generate 1 to 52 numbers in pivot column?:ermm:
This forum has the best SQL Server minds on the planet. They are also some of the nicest people you'll ever interact with. Their expert advice is provided free of charge. They do this while performing the jobs they were hired to do that actually pay their bills. I suggest toning it down a few notches since nobody likes to be disrespected or goaded into offering help. If you want assistance with an urgent problem, I suggest you contact Microsoft support at 1-800-426-9400.
July 3, 2014 at 10:03 am
Thanks Sean. 🙂
Right now, going thru the links as per your advice. Also searching for tutorials on learning how to create OLAP Cubes in SSAS and how i can implement them in my report - have very less time to create this report.
July 3, 2014 at 10:23 am
if you have little time to prepare this report and you have never used SSAS...then this might not be the right time to start SSAS
maybe if you provided some data and expected results......then we might be able to help you on your way....
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 3, 2014 at 11:15 am
Thanks J. Livingston 🙂
please let me know if i need to start a separate thread for this or i can state my problem here?
Actually, as a starting point,
1] I needed a job schedule to scan a network folder containing CSV files in this format: DATABASE_COUNTRYNAME_CHARNAME.CSV e.g DATABASE_FRANCE_EURO.CSV
2] Whenever a new CSV comes into the folder (say 1st of every month), i want the SQL Server to scan the folder and run two queries (probably thru a stored procedure) on them to produce 2 resultant tables in a database in SQL Server viz.,
tbl_BrandYearWise & tbl_ItemWeekWise
tbl_ItemWeekWise SQL:
SELECT * INTO tbl_ItemWeekWise
FROM
(
SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
'SPAIN' as [sCOUNTRY], 'EURO' as [sCHAR],
IIf( t2.first_week_on_sale = 1 and t2.weeks_on_sale <=52,
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + (1 + 52 - t2.weeks_on_sale),
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + 1 ) as Sale_Week
FROM [DATABASE_SPAIN_EURO.CSV] as t, ( SELECT t3.[Level],t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description],
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as first_week_on_sale,
max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as last_week_on_sale,
(max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) -
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)))+1 as weeks_on_sale
FROM [DATABASE_SPAIN_EURO.CSV] as t3
WHERE t3.[Sales Value with Innovation] is NOT NULL
and t3.[Sales Value with Innovation] <>0
and t3.[Level]='Item'
GROUP BY t3.[Level], t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description]
) as t2
WHERE
t.[Level] = t2.[Level]
and t.[Category] = t2.[Category]
and t.[Manufacturer] = t2.[Manufacturer]
and t.[Brand] = t2.[Brand]
and t.[Description] = t2.[Description]
and t.[Sales Value with Innovation] is NOT NULL
and t.[Sales Value with Innovation] <>0
and t2.first_week_on_sale >=1
and t2.weeks_on_sale <=52
UNION ALL
SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
'SPAIN' as [sCOUNTRY], 'EURO' as [sCHAR],
IIf( t2.first_week_on_sale = 1 and t2.weeks_on_sale <=52,
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + (1 + 52 - t2.weeks_on_sale),
((((substring(t.[week],3,4) - 2011) * 52) + right(t.[week],2)) - t2.first_week_on_sale) + 1 ) as Sale_Week
FROM [DATABASE_FRANCE_EURO.CSV] as t, ( SELECT t3.[Level],t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description],
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as first_week_on_sale,
max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) as last_week_on_sale,
(max(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)) -
min(((substring(t3.[week],3,4) - 2011) * 52) + right(t3.[week],2)))+1 as weeks_on_sale
FROM [DATABASE_FRANCE_EURO.CSV] as t3
WHERE t3.[Sales Value with Innovation] is NOT NULL
and t3.[Sales Value with Innovation] <>0
and t3.[Level]='Item'
GROUP BY t3.[Level], t3.[Category],t3.[Manufacturer],t3.[Brand],t3.[Description]
) as t2
WHERE
t.[Level] = t2.[Level]
and t.[Category] = t2.[Category]
and t.[Manufacturer] = t2.[Manufacturer]
and t.[Brand] = t2.[Brand]
and t.[Description] = t2.[Description]
and t.[Sales Value with Innovation] is NOT NULL
and t.[Sales Value with Innovation] <>0
and t2.first_week_on_sale >=1
UNION ALL
.....
) AS TEMP
tbl_BrandYearWise SQL
SELECT * INTO tbl_BrandYearWise
FROM
(
SELECT *,
SUBSTRING([Week],3,4) * 1 as iYEAR,
'FRANCE' as [sCOUNTRY],
'EURO' as [sCHAR],
RIGHT([Week],2) AS Sale_Week
FROM [DATABASE_FRANCE_EURO]
WHERE [Sales Value with Innovation] Is Not Null
and [Sales Value with Innovation] <> 0
and Level='Brand'
UNION ALL
SELECT *,
SUBSTRING([Week],3,4) * 1 as iYEAR,
'SPAIN' as [sCOUNTRY],
'GLOBAL' as [sCHAR],
RIGHT([Week],2) AS Sale_Week
FROM [DATABASE_FRANCE_EURO]
WHERE [Sales Value with Innovation] Is Not Null
and [Sales Value with Innovation] <> 0
and Level='Brand'
UNION ALL
....
) AS TEMP
3] Then i need to PIVOT both these tables and then base my report KPI's on these 2 pivoted tables.
just to give you an idea on how the CSV data looks:
LevelWeekCategoryManufacturerBrandDescriptionEANSales Value with InnovationSales Units with InnovationPrice Per ItemImportance Value w/InnovationImportance Units w/InnovationNumeric DistributionWeighted DistributionAverage Number of ItemValueVolumeUnitsSales Value New ManufacturerSales Value New BrandSales Value New Line ExtensionSales Value New PackagingSales Value New SizeSales Value New Product FormSales Value New Style TypeSales Value New Flavour FragrSales Value New ClaimSales Units New ManufacturerSales Units New BrandSales Units New Line ExtensionSales Units New PackagingSales Units New SizeSales Units New Product FormSales Units New Style TypeSales Units New Flavour FragrSales Units New Claim
ItemW 2011 06BISCUITSALL OTHERALL OTHERLA-BTQ-PRVNCL BISCTS-NN-ENRBS 250G X1356530000288050.3491973911.699999814.303350274156971001000.001086956521739130.00127991341635356150.349197392.9249999511.6999998100050.349197390000000011.6999998100000
ItemW 2011 07BISCUITSALL OTHERALL OTHERLA-BTQ-PRVNCL BISCTS-NN-ENRBS 250G X1356530000288064.29240417154.2861602781001000.001358511071865240.00185307305730048164.292404173.751500064.29240417000000001500000
BrandW 2013 30AIR FRESHENERSWERNER & MERTZRAINETTAIR FRESHENERS WERNER & MERTZ RAINETTNULL005.27058285109628000.03781847133757960.102661003595691.484210526315793908.1899414166.73590088741.51000977000000000000000000
BrandW 2013 31AIR FRESHENERSWERNER & MERTZRAINETTAIR FRESHENERS WERNER & MERTZ RAINETTNULL005.24138545164841000.03887488390606340.1061059890399721.460750853242324325.4008789174.27160645825.23999023000000000000000000
everything that starts after column EAN are the KPI - some of which i want to aggregate (cumulative, velocity etc) yearly 52 week window or 156 week window by sCountry, Category, Manufacturer, Brand, Description (SKU).
I think you are right. this would require something like SSAS cubes to store different dimensions and measures.
Hope the above makes sense.
July 4, 2014 at 6:23 am
can you provide some sample data in a readily consumable format for DATABASE_SPAIN_EURO.CSV/DATABASE_FRANCE_EURO.CSV ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 4, 2014 at 6:27 am
Hi,
sure.
Is it possible to send it to you privately to a personal email address?
July 4, 2014 at 6:51 am
sifar786 (7/4/2014)
Hi,sure.
Is it possible to send it to you privately to a personal email address?
no probs...send me a PM ....if I get anywhere I'll obfuscate the details for you if posted on SSC.
I would also start another thread for this part below
1] I needed a job schedule to scan a network folder containing CSV files in this format: DATABASE_COUNTRYNAME_CHARNAME.CSV e.g DATABASE_FRANCE_EURO.CSV
2] Whenever a new CSV comes into the folder (say 1st of every month), i want the SQL Server to scan the folder
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 16, 2014 at 10:04 am
anthonyk653 (7/16/2014)
Hello,I used Long Path Tool software that simply worked for me for Long Path files. It's really helping
Thank you....
This sure sounds like spam to me. It doesn't seem to have any relevance to the question and this exact answer has appeared more than once.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply