Database

Data Mining Structure not Available in SSAS Tabular Model

3-Jun-14 22:16

I'm pretty sure MySQL isn't supporting outer implicit joins, only inner implicit joins.
You'll have to use an Ansi Join instead.

Wrong is evil and must be defeated. - Jeff Ello[^]

FLouis382-Jun-14 7:14

FLouis38

2-Jun-14 7:14

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.

Ready to give up on MySQL - is PostgreSQL better?

Wombaticus1-Jun-14 23:09

Wombaticus

1-Jun-14 23:09

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?

Re: Ready to give up on MySQL - is PostgreSQL better?

Jörgen Andersson1-Jun-14 23:37

Re: Ready to give up on MySQL - is PostgreSQL better?

1-Jun-14 23:37

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?

Wrong is evil and must be defeated. - Jeff Ello[^]

Wombaticus1-Jun-14 23:43

Wombaticus

1-Jun-14 23:43

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?

Re: Ready to give up on MySQL - is PostgreSQL better?

Jörgen Andersson2-Jun-14 0:08

A query with lot of inner queries

2-Jun-14 0:08

Wombaticus wrote:
Would Connector/NET make a difference?

Mostly performance, and a lot of it.

Have you read this[^]?

Wrong is evil and must be defeated. - Jeff Ello[^]

indian14329-May-14 13:41

29-May-14 13:41

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

Mycroft Holmes29-May-14 14:02

29-May-14 14:02

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.

SQL

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

indian14329-May-14 14:09

29-May-14 14:09

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

Mycroft Holmes29-May-14 15:08

29-May-14 15:08

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

Jörgen Andersson29-May-14 23:20

29-May-14 23:20

Well you only need to unwind and indent your parentheses to make it more readable.
Then keep in mind that :

SQL

SELECT  *
FROM    TableA
JOIN   
    (
    TableB  Join   TableC On TableB.ID = TableC.ID
    )
    ON  TableA.ID = TableB.ID

Is the same as:

SQL

SELECT  *
FROM    TableB
JOIN    TableC
    ON  TableB.ID = TableC.ID
JOIN    TableA
    ON  TableA.ID = TableB.ID

So your query could quickly be simplified to this:

SQL

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.ShortDsc

Note 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.

Wrong is evil and must be defeated. - Jeff Ello[^]

Mycroft Holmes30-May-14 2:50

30-May-14 2:50

Did you use a tool for that or manually code it?

Never underestimate the power of human stupidity
RAH

Jörgen Andersson30-May-14 4:15

30-May-14 4:15

Both, We have a homebrewed tool for formatting, the conversion of the joins I made by hand.

Wrong is evil and must be defeated. - Jeff Ello[^]

indian14330-May-14 10:08

30-May-14 10:08

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

Jörgen Andersson30-May-14 10:24

30-May-14 10:24

You're welcome.

Wrong is evil and must be defeated. - Jeff Ello[^]

indian14330-May-14 10:05

30-May-14 10:05

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

Mycroft Holmes30-May-14 13:46