|
Lots of companies are comparing the SSAS Tabular vs Multi-dimensional Model. Honestly, there are tons and tons of articles already written about the differences between the two (so i don’t plan on doing that). Case in Point: http://www.sqlchick.com/entries/2012/3/4/decisions-powerpivot-ssas-tabular-or-ssas-multidimensional-m.html
I recently worked in an environment where we were looking at using data-mining algorithms for product affinity analysis, linear regression and customer analysis for generating customer mailing lists.
Unfortunately, way back, the decision had been made to go with Tabular model, to take advantage of the the InMemory and speed of development benefits. Now, with tabular in place, requests starts coming for analysis that requires the construction of data-mining structures akin to what is in Multi-dimensional. Unfortunately, that functionality doesn’t exist in Tabular yet, so we can’t do the analysis unless we go Multi-dimensional.
Fortunately, this is not a show-stopper requirement for us but just something to be aware off.
Summary: Currently, tabular model does not support data mining structures required for product affinity analysis, linear regression, customer predictive analysis, e.t.c. I’m not sure if Microsoft plans on building such functionalities into Tabular model going forward, but I just wanted to point it out incase some people are not yet aware of this short coming for Tabular.
|
|
|
|
|
I am sick to the back teeth of encoding issues with MySQL. I have Googled the issue and tried every suggestion I can find, and am still plagued with insert errors such as:
ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.18]Incorrect string value: '\xEF\x82\xA7\x09Se...' for column '[column name]' at row 1
Please.. don't bother suggesting ways round this, I give up on it. My question, to anyone else who has experienced this, is simply this: does PostgreSQL suffer this same issue, or at least have a solution that works?
|
|
|
|
|
It will have different issues.
Your best bet is to make sure you install it to use Unicode. But watch out, PostGreSQL uses UTF8 while dotnet uses UTF16 internally. So you might still encounter odd issues.
MySQL supports UTF16 from version 5.5 IIRC.
Why do you use odbc instead of Connector/NET?
|
|
|
|
|
It was installed with utf8_geberal_ci as the default. With the later versions of MySQL supporting utf8mb4_general_ci I've tried that too - but to no avail.
Why ODBC? Habit, I guess. Would Connector/NET make a difference?
|
|
|
|
|
Wombaticus wrote: Would Connector/NET make a difference?
Mostly performance, and a lot of it.
Have you read this[^]?
|
|
|
|
|
Hi All,
I have the following query that is created by MS Access while creating report. But the query has lots of inner queries and joins. Can anybody please help in making this big query simple and by removing all the possible brackets and make it clean. Any type is very helpful. This query really taking a lot of time to understand it. Any link, tool name or way to solve it any help is very much helpful. Thanks in advance.
Here is the query.
SELECT so.EntityId, s.SponsorId, so.OrderId, so.ProgramId, so.ProgramYear, s.SponsorNbr,
s.SponsorNme, s.VendorNbr, s.MailFdpCde, dbo.Reference.RefCde, dbo.Reference.ExtCde, dbo.FDPSponsorApp.CurrentInd,
dbo.DeliveryPeriod.BegDte, dbo.DeliveryPeriod.EndDte, dbo.vwRef_fdpprogram.RefDsc, dbo.vwRef_fdpprogram.RefCde AS ProgramCde, dbo.Product.ProductCde,
dbo.Product.ShortDsc as ProductDsc, sod.QtyInv AS QtyRcv, dbo.Product.NetPackWt,
(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS UnitCost,
[QtyInv]*(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS ValueRcvd,
[NetPackWt]*[QtyInv] AS [Total Pounds], dbo.Contact.FirstNme, dbo.Contact.LastNme, dbo.Address.Addr1, dbo.Address.Addr2,
dbo.Address.City, dbo.Address.State, dbo.Address.ZipCde, dbo_Reference_1.RefDsc AS DeliveryType, so.DeliveryTypeId,
dbo.RefYearDefaults.DefValue AS BrownBoxFee, dbo_RefYearDefaults_1.DefValue AS DirectDiversionFee, dbo.Product.NetPackWt, s.VendorNbr,
s.FedEmpIdNbr
FROM dbo.RefYearDefaults AS dbo_RefYearDefaults_1
INNER JOIN (dbo.RefYearDefaults
INNER JOIN ((dbo.Reference
INNER JOIN ((dbo.Contact
INNER JOIN (dbo.Address
INNER JOIN (dbo.ProductCost
INNER JOIN (dbo.Sponsor s
INNER JOIN (dbo.FDPSponsorApp
INNER JOIN ((((dbo.FDPEntity
INNER JOIN dbo.SponsorOrder so ON dbo.FDPEntity.EntityId = so.LocationId)
INNER JOIN dbo.SponsorOrderDetail sod ON so.OrderId = sod.OrderId)
INNER JOIN dbo.Product ON sod.ProductId = dbo.Product.ProductId)
INNER JOIN dbo.DeliveryPeriod ON (so.DeliveryPeriodId = dbo.DeliveryPeriod.DeliveryPeriodId)
AND (so.ProgramYear = dbo.DeliveryPeriod.ProgramYear)) ON (dbo.FDPSponsorApp.EntityId =
so.EntityId) AND (dbo.FDPSponsorApp.ProgramYear = so.ProgramYear)) ON s.SponsorId = dbo.FDPSponsorApp.SponsorId) ON
(dbo.ProductCost.ProgramYear = dbo.FDPSponsorApp.ProgramYear) AND (dbo.ProductCost.ProductId = dbo.Product.ProductId)) ON dbo.Address.AddrId =
dbo.FDPSponsorApp.MailAddrId) ON dbo.Contact.ContactId = dbo.FDPSponsorApp.FdpCtcId) INNER JOIN dbo.vwRef_fdpprogram ON so.ProgramId =
dbo.vwRef_fdpprogram.RefId) ON dbo.Reference.RefId = s.SFMSObjectTypeId) INNER JOIN dbo.Reference AS dbo_Reference_1 ON so.DeliveryTypeId =
dbo_Reference_1.RefId) ON dbo.RefYearDefaults.ProgramYear = so.ProgramYear) ON dbo_RefYearDefaults_1.ProgramYear = so.ProgramYear
WHERE (((so.ProgramYear)= 2014) AND --so.EntityId = @SponsorEntityId AND
((dbo.FDPSponsorApp.CurrentInd)=1) AND ((sod.QtyInv)>0) AND ((dbo.RefYearDefaults.DefNme)='BrownBoxFee')
AND ((dbo_RefYearDefaults_1.DefNme)='DirectDiversionFee')) ORDER BY so.EntityId, s.SponsorNme, so.OrderId, Product.ShortDsc
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
What a bloody awful construct - I doubt there is any tool to change the structure other than manually coding it.
As you will know the data structure you should be able to identify the joins in there and move them to a more supportable format.
INNER JOIN TableName as Alias ON A.Field = B.field
OR
Move the entire database to SQL Server where there are tools to help build and tune the query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes we moved everything in to SQL Server. But this query is left, the person who created this query for the report is not there any more in the organization.
I am trying my head out to create the same query without lot of brackets. Can you please help me if you have any idea.
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
I would start from scratch using the SQL Server view builder to get the table in and the joins. SSMS may create something similar but it may create the joins in a non nested structure which is what you are looking for to make it a supportable syntax.
If nothing else works then winkle out the table hierarchy from the syntax and build it manually by putting the main transaction table into the from and manually adding te rest of the table required.
I would also consider creating some views to simplify the structure EG your SponsorOrderDetail could be turned into a view to include the product and sponsor details required for the query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well you only need to unwind and indent your parentheses to make it more readable.
Then keep in mind that :
SELECT *
FROM TableA
JOIN
(
TableB Join TableC On TableB.ID = TableC.ID
)
ON TableA.ID = TableB.IDIs the same as:
SELECT *
FROM TableB
JOIN TableC
ON TableB.ID = TableC.ID
JOIN TableA
ON TableA.ID = TableB.IDSo your query could quickly be simplified to this:
SELECT
so.EntityId,
s.SponsorId,
so.OrderId,
so.ProgramId,
so.ProgramYear,
s.SponsorNbr,
s.SponsorNme,
s.VendorNbr,
s.MailFdpCde,
dbo.Reference.RefCde,
dbo.Reference.ExtCde,
dbo.FDPSponsorApp.CurrentInd,
dbo.DeliveryPeriod.BegDte,
dbo.DeliveryPeriod.EndDte,
dbo.vwRef_fdpprogram.RefDsc,
dbo.vwRef_fdpprogram.RefCde AS ProgramCde,
dbo.Product.ProductCde,
dbo.Product.ShortDsc as ProductDsc,
sod.QtyInv AS QtyRcv,
dbo.Product.NetPackWt,
CASE
WHEN dbo.ProductCost.FixedCost IS NULL OR dbo.ProductCost.EntDrawInd= 653 THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0)
END AS UnitCost,
CASE
WHEN dbo.ProductCost.FixedCost IS NULL OR dbo.ProductCost.EntDrawInd= 653 THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0)
END * [QtyInv] AS ValueRcvd,
[NetPackWt]*[QtyInv] AS [Total Pounds],
dbo.Contact.FirstNme,
dbo.Contact.LastNme,
dbo.Address.Addr1,
dbo.Address.Addr2,
dbo.Address.City,
dbo.Address.State,
dbo.Address.ZipCde,
dbo_Reference_1.RefDsc AS DeliveryType,
so.DeliveryTypeId,
dbo.RefYearDefaults.DefValue AS BrownBoxFee,
dbo_RefYearDefaults_1.DefValue AS DirectDiversionFee,
dbo.Product.NetPackWt,
s.VendorNbr,
s.FedEmpIdNbr
FROM
bo.FDPEntity
JOIN dbo.SponsorOrder so
ON dbo.FDPEntity.EntityId = so.LocationId
JOIN dbo.SponsorOrderDetail sod
ON so.OrderId = sod.OrderId
JOIN dbo.Product
ON sod.ProductId = dbo.Product.ProductId
JOIN dbo.DeliveryPeriod
ON so.DeliveryPeriodId = dbo.DeliveryPeriod.DeliveryPeriodId
AND so.ProgramYear = dbo.DeliveryPeriod.ProgramYear
JOIN dbo.FDPSponsorApp
ON dbo.FDPSponsorApp.EntityId = so.EntityId
AND dbo.FDPSponsorApp.ProgramYear = so.ProgramYear
JOIN dbo.Sponsor s
ON s.SponsorId = dbo.FDPSponsorApp.SponsorId
JOIN dbo.ProductCost
ON dbo.ProductCost.ProgramYear = dbo.FDPSponsorApp.ProgramYear
AND dbo.ProductCost.ProductId = dbo.Product.ProductId
JOIN dbo.Address
ON dbo.Address.AddrId = dbo.FDPSponsorApp.MailAddrId
JOIN dbo.Contact
ON dbo.Contact.ContactId = dbo.FDPSponsorApp.FdpCtcId
JOIN dbo.vwRef_fdpprogram
ON so.ProgramId = dbo.vwRef_fdpprogram.RefId
JOIN dbo.Reference
ON dbo.Reference.RefId = s.SFMSObjectTypeId
JOIN dbo.Reference AS dbo_Reference_1
ON so.DeliveryTypeId = dbo_Reference_1.RefId
JOIN dbo.RefYearDefaults
ON dbo.RefYearDefaults.ProgramYear = so.ProgramYear
JOIN dbo.RefYearDefaults AS dbo_RefYearDefaults_1
ON dbo_RefYearDefaults_1.ProgramYear = so.ProgramYear
WHERE so.ProgramYear= 2014
AND dbo.FDPSponsorApp.CurrentInd=1
AND sod.QtyInv > 0
AND dbo.RefYearDefaults.DefNme='BrownBoxFee'
AND dbo_RefYearDefaults_1.DefNme='DirectDiversionFee'
ORDER BY so.EntityId, s.SponsorNme, so.OrderId, Product.ShortDscNote that since the parentheses are gone the optimizer isn't forced to do the joins in a certain order anymore and might therefore be much faster.
|
|
|
|
|
Did you use a tool for that or manually code it?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Both, We have a homebrewed tool for formatting, the conversion of the joins I made by hand.
|
|
|
|
|
Awesome thanks a lot. I cant forget this help. Thank you thank you thank you very much.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
|
Actually this query isn't done by me but the person who did it wrote it with a tool.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
indian143 wrote: wrote it with a tool.
Oh I knew that, no one I know will write joins like that. I was interested in whether Jorgen hand coded the changes, he did and deserves your up vote.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Audience on my site is approximately 2-10 users online, database size 170MB, request 5-20/s. Does anyone advise me please, why the tempdb nonstop read / write to HDD for about 2-4MB/sec. Why, or what you need to set or to my tempdb. Thank you.
|
|
|
|
|
The usage of tempdb is highly depend on the queries you run - if you use, for instance, a lot of temporary tables (even local one) it will all go to tempdb...
Please read here to understand the usage of tempdb: http://msdn.microsoft.com/en-us/library/ms190768.aspx[^]
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Hi all,
I am in desperate need of help please.
I have created a stored procedure with a while loop using a hard coded date 31-12-2014 to compare against current date produced in a while loop. I need to make the hard coded date reflect every two years i.e. 31-12-2016, 31-12-2018 etc.
How can the date be automatic instead of hard coded please?
i.e While (currdate <= '31-12-2014')
Second issue, how do I make the stored procedure to run every two years as well?
SQL Agent Job doesn't have a yearly frequency at all.
Can someone please offer an example coding and suggestions to those two issues which I will be grateful please.
Thank You!
|
|
|
|
|
Use mod on the year component to determine even years, check that the year component is the same in curredate.
or
Create table to hold the event dates
A 2 year event cycle is very ambitious, a lot can happen in that cycle.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Could you kindly explain please, I am not sure about the mod with year component? I am rather saying I don't quite understand please.
I still don't know how to get the second issue to call the stored procs every two years as SQL Agent Job doesn't seem to offer the solution.
|
|
|
|
|
A simple search for TSQL Mod would have given you the information. However this may help, this year return 0, next year 1.
SELECT DATEPART(YEAR,GETDATE()) % 2
SELECT DATEPART(YEAR,DATEADD(YEAR,1,GETDATE())) % 2
As for the job not offering year - you are building a test method so a proc will run under only defined circumstances, so run the job every month, if it does not meet your criteria then let it wait for next month.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Does this look efficient and OK?
DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime
SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1)
SET @GetHour = datepart(hour, GETDATE()) -- returns hour
--Get Today date to compare to the last day of December in current year
IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT(@LastDayOfYear, 'yyyy-MM-dd')) AND @GetHour >= 20
BEGIN
--If Equal - then increase the next 2 years
SET @NextNewDate = FORMAT(DATEADD(YEAR, 2, @LastDayOfYear), 'yyyy-MM-dd HH:mm:ss')
END
ELSE
SET @NextNewDate = FORMAT(@LastDayOfYear, 'yyyy-MM-dd HH:mm:ss')
PRINT @NextNewDate
WHILE (CONVERT(DATE,@currDate, 101) <= @NextNewDate)
BEGIN
Second Issue - "so run the job every month, if it does not meet your criteria then let it wait for next month."
How do you make it meet your criteria i.e. if it falls on 31st December of each year?
modified 28-May-14 4:49am.
|
|
|
|
|
If you want to excecute your query on every 31st December add a below condition
IF (MONTH(GETDATE()) = 12 AND DAY(GETDATE()) = 31)
BEGIN
END
|
|
|
|
|
Hi Again
"so run the job every month, if it does not meet your criteria then let it wait for next month."
Would it mean I have to somehow implement a code within the Job Step List in the SQL Agent Job?
Many Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
