<%@ Page Title="Dynameomics - MDX Queries" Language="C#" MasterPageFile="~/MasterPages/Dynameomics_Nav.master" AutoEventWireup="true" CodeFile="MDXQueries.aspx.cs" Inherits="DBDescription_MDXQueries" %> <% string sqlTitle = String.Empty; string mdxStatement = String.Empty; switch(Convert.ToInt32(Session["CurrentMDXQuery"])) { //------------------------------------------------------------------------------------------------ //SQL STATEMENT 1 case 1: sqlTitle = "Report back a pseudo pdb file for a given time frame"; mdxStatement = @" WITH MEMBER [Measures].[atm_type] as '[id].[atom name].membervalue' MEMBER [Measures].[res_type] as '[id].residue.membervalue' MEMBER [Measures].[res_num] as '[id].[residue number].membervalue' MEMBER [Measures].[atm_num] as '[id].[Hierarchy].membervalue' MEMBER [Measures].[atom] as (""ATOM"") SELECT {Measures. [atom], [atm_num], [atm_type], [res_type], [res_num], [x Coord], [y Coord], [z Coord]} on 0, Descendants([ID].[Hierarchy].[structure].[1a2p-1], 2) on 1 FROM [dynameomics] WHERE ([Time].[3], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8]);"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 2 case 2: sqlTitle = "Distance between two chosen atoms over a simulation"; mdxStatement = @" WITH MEMBER [ID].[Hierarchy].[ATM1] as '[ID].[Hierarchy].[structure].[1a2p-1].[1].[5]' MEMBER [ID].[Hierarchy].[ATM2] as '[ID].[Hierarchy].[structure].[1a2p-1].[2].[21]' MEMBER [Measures].[distance] as ' ( (([ID].[Hierarchy].[ATM2],[Measures].[x Coord])-([ID].[Hierarchy].[ATM1], [Measures].[x Coord]))^2 + (([ID].[Hierarchy].[ATM2],[Measures].[y Coord])-([ID].[Hierarchy].[ATM1], [Measures].[y Coord]))^2 + (([ID].[Hierarchy].[ATM2],[Measures].[z Coord])-([ID].[Hierarchy].[ATM1], [Measures].[z Coord]))^2 ) ^ 0.5' SELECT [Measures].[distance] on 0, [time].[1]:[time].[20000] on 1 FROM [dynameomics] WHERE ([Run].[1], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8]);"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 3 case 3: sqlTitle = "Fraction of native contacts and CA RMSD"; mdxStatement = @" WITH MEMBER measures.set1 AS ([time].[0], MEASURES.[Native]) MEMBER measures.[frac_nat] as '[native] / [set1]' SELECT {[run].[2]}*{[frac_nat], [rmsd]} on 0, NON EMPTY{time.members} on 1 FROM [dynameomics] WHERE ( [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8], [ID].[Hierarchy].[structure].[1a2p-1]);"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 4 case 4: sqlTitle = "List all proline residues in all proteins in the database and show their Phi Psi values"; mdxStatement = @" WITH SET STR AS [ID].[structure] SET pro as filter(descendants([ID].[hierarchy], 2), [ID].[hierarchy].currentmember.properties( ""Residue"" )=""PRO"") SELECT non empty STR *pro on 1, [run].[1]* {[phi],[psi]} on 0 FROM [dynameomics] WHERE ( [Time].[0], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8]);"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 5 case 5: sqlTitle = "CA Contacts with 10 angstrom cutoff"; mdxStatement = @" WITH SET res1 AS ' Filter(Descendants([ID].[Hierarchy].[structure].[1a2p-1], 2) , [ID].[Hierarchy].CurrentMember.Properties(""Atom Name"")=""CA"" )' SET res2 AS ' Filter(Descendants([sekret ID].[Hierarchy].[structure].[1a2p-1], 2) , [sekret ID].[Hierarchy].CurrentMember.Properties(""Atom Name"")=""CA"" )' MEMBER [Measures].[resnum2] as '[Sekret id].[residue number].membervalue' MEMBER [Measures].[resnum1] as '[ID].[residue number].membervalue' MEMBER [Measures].[distance] as ' ( ((res1.Item( Rank([sekret ID].[Hierarchy].CurrentMember,res2) - 1),[Measures].[x Coord]) -([ID].[Hierarchy].CurrentMember,[Measures].[x Coord]))^2 + ((res1.Item( Rank([sekret ID].[Hierarchy].CurrentMember,res2) - 1),[Measures].[y Coord]) -([ID].[Hierarchy].CurrentMember,[Measures].[y Coord]))^2 + ((res1.Item( Rank([sekret ID].[Hierarchy].CurrentMember,res2) - 1),[Measures].[z Coord]) -([ID].[Hierarchy].CurrentMember,[Measures].[z Coord]))^2 ) ^ 0.5' SELECT filter (res1 * res2, [Measures].distance < 10 and [Measures].distance <> 0) on rows, {MEASURES.[resnum1],MEASURES.[resnum2], MEASURES.[distance]} on columns FROM [dynameomics] WHERE ( [Time].[1], [Run].[1], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8] );"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 6 case 6: sqlTitle = "List all Phi Psi angles from a protein at a given time frame"; mdxStatement = @" WITH MEMBER [Measures].[res_num] as '[id].[residue number].membervalue' SELECT {Measures. [res_num],[phi], [psi]} on 0, Descendants([ID].[Hierarchy].[structure].[1a2p-1], 1) on 1 FROM [dynameomics] WHERE ([Time].[1], [Run].[1], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8]);"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 7 case 7: sqlTitle = "Find a sequence of amino acid residues from the entire database and report back a the phi psi angles at a given time frame"; mdxStatement = @" WITH Member [measures].[r1] as ""GLN"" Member [measures].[r2] as ""ALA"" Member [measures].[r3] as ""LEU"" Member [measures].[r4] as ""GLY"" Member [measures].[r5] as ""TRP"" Member [measures].[r6] as ""VAL"" SET STR AS [ID].[structure].[structure] SET res1 as filter(descendants([ID].[hierarchy], 2), [ID].[hierarchy].currentmember.properties( ""Residue"" )=[r1] and [ID].[hierarchy].nextmember.properties( ""Residue"" )=[r2] and [ID].[hierarchy].nextmember.nextmember.properties( ""Residue"" )=[r3] and [ID].[hierarchy].nextmember.nextmember.nextmember.properties( ""Residue"" )=[r4] and [ID].[hierarchy].nextmember.nextmember.nextmember.nextmember.properties( ""Residue"" )=[r5] and [ID].[hierarchy].nextmember.nextmember.nextmember.nextmember.nextmember.properties( ""Residue"" )=[r6]) SET res2 as filter(descendants([ID].[hierarchy], 2), [ID].[hierarchy].currentmember.properties( ""Residue"" )=[r2] and [ID].[hierarchy].nextmember.properties( ""Residue"" )=[r3] and [ID].[hierarchy].nextmember.nextmember.properties( ""Residue"" )=[r4] and [ID].[hierarchy].nextmember.nextmember.nextmember.properties( ""Residue"" )=[r5] and [ID].[hierarchy].nextmember.nextmember.nextmember.nextmember.properties( ""Residue"" )=[r6] and [ID].[hierarchy].prevmember.properties( ""Residue"" )=[r1]) SET res3 as filter(descendants([ID].[hierarchy], 2), [ID].[hierarchy].currentmember.properties( ""Residue"" )=[r3] and [ID].[hierarchy].nextmember.properties( ""Residue"" )=[r4] and [ID].[hierarchy].nextmember.nextmember.properties( ""Residue"" )=[r5] and [ID].[hierarchy].nextmember.nextmember.nextmember.properties( ""Residue"" )=[r6] and [ID].[hierarchy].prevmember.prevmember.properties( ""Residue"" )=[r1] and [ID].[hierarchy].prevmember.properties( ""Residue"" )=[r2] ) SET res4 as filter(descendants([ID].[hierarchy], 2), [ID].[hierarchy].currentmember.properties( ""Residue"" )=[r4] and [ID].[hierarchy].nextmember.properties( ""Residue"" )=[r5] and [ID].[hierarchy].nextmember.nextmember.properties( ""Residue"" )=[r6] and [ID].[hierarchy].prevmember.prevmember.prevmember.properties( ""Residue"" )=[r1] and [ID].[hierarchy].prevmember.prevmember.properties( ""Residue"" )=[r2] and [ID].[hierarchy].prevmember.properties( ""Residue"" )=[r3]) SET res5 as filter(descendants([ID].[hierarchy], 2), [ID].[hierarchy].currentmember.properties( ""Residue"" )=[r5] and [ID].[hierarchy].nextmember.properties( ""Residue"" )=[r6] and [ID].[hierarchy].prevmember.prevmember.prevmember.prevmember.properties( ""Residue"" )=[r1] and [ID].[hierarchy].prevmember.prevmember.prevmember.properties( ""Residue"" )=[r2] and [ID].[hierarchy].prevmember.prevmember.properties( ""Residue"" )=[r3] and [ID].[hierarchy].prevmember.properties( ""Residue"" )=[r4]) SET res6 as filter(descendants([ID].[hierarchy], 2), [ID].[hierarchy].currentmember.properties( ""Residue"" )=[r6] and [ID].[hierarchy].prevmember.prevmember.prevmember.prevmember.prevmember.properties( ""Residue"" )=[r1] and [ID].[hierarchy].prevmember.prevmember.prevmember.prevmember.properties( ""Residue"" )=[r2] and [ID].[hierarchy].prevmember.prevmember.prevmember.properties( ""Residue"" )=[r3] and [ID].[hierarchy].prevmember.prevmember.properties( ""Residue"" )=[r4] and [ID].[hierarchy].prevmember.properties( ""Residue"" )=[r5]) SELECT non empty STR *{res1, res2, res3, res4, res5, res6} on 1, [run].[1]* {[phi],[psi]} on 0 FROM [dynameomics] WHERE ( [Time].[0], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8);"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 8 case 8: sqlTitle = "Heavy atom contact count"; mdxStatement = @" WITH Set TheTimes as ' {[Time].[1]:[Time].[5]} ' Member measures.AtomName as ' [ID].[Hierarchy].CurrentMember.Properties(""Atom Name"") ' Member measures.Sekret_AtomName as ' [Sekret_ID].[Hierarchy].CurrentMember.Properties(""Atom Name"") ' Member measures.Resnum as ' [ID].[Hierarchy].CurrentMember.Properties(""Residue Number"") ' Member measures.Sekret_Resnum as ' [Sekret_ID].[Hierarchy].CurrentMember.Properties(""Residue Number"") ' SET res1 as ' Descendants([ID].[Hierarchy].[Structure].[1a2pA0], [ID].[Hierarchy].[Atom Number]) ' SET res2 as ' Descendants([Sekret_ID].[Hierarchy].[Structure].[1a2pA0], [Sekret_ID].[Hierarchy].[Atom Number]) ' MEMBER [ID].[Hierarchy].[Other Atom] as ' res1.Item( Rank([Sekret_ID].[Hierarchy].CurrentMember,res2) - 1) ' set CAs as ' Filter(Res1,[ID].[Hierarchy].Properties( ""Atom Name"" )=""CA"") ' set Sekret_CAs as ' Filter(Res2,[Sekret_ID].[Hierarchy].Properties( ""Atom Name"" )=""CA"") ' //when the distance is calculated we do not square root the resultant value (to save computation) //hence all distance cutoffs are squared the desired value e.g. 100 is 10, 29.16 is 5.4 and 21.16 is 4.6) set [NeighborCAs] as ' Filter ( TheTimes * CAs * Sekret_CAs, [Measures].[distance] < 100 and measures.Resnum < measures.Sekret_Resnum) ' set [NeighborResidues] as ' Generate({NeighborCAs}, {([time].[time].Currentmember, [ID].[Hierarchy].CurrentMember.Parent, [Sekret_ID].[Hierarchy].CurrentMember.Parent)}) ' Set [TestForContact] as ' Generate({NeighborResidues}, {([time].[time].Currentmember, Filter([ID].[Hierarchy].CurrentMember.Children, [ID].[Hierarchy].Properties( ""Atom Type"" )), -- Property excludes hydrogen Filter([Sekret_ID].[Hierarchy].CurrentMember.Children, [Sekret_ID].[Hierarchy].Properties( ""Atom Type"" )) -- Property Excludes hydrogen )}) ' // the final STRTOVALUE allows you to set a cut off for the distance of the residues considered from the start // residue, currently set at 11. The line can be removed if you wish. Set [AtomsInContact] as ' Filter ({TestForContact}, distance <= iif(left([ID].[Hierarchy].CurrentMember.Properties(""Atom Name""),1) = ""C"" and left([Sekret_ID].[Hierarchy].CurrentMember.Properties(""Atom Name""),1) = ""C"", 29.16, 21.16) and distance > 0 and STRTOVALUE([Sekret_ID].[Hierarchy].CurrentMember.Properties(""Residue Number"")) - STRTOVALUE([ID].[Hierarchy].CurrentMember.Properties(""Residue Number"")) > 1 and STRTOVALUE([Sekret_ID].[Hierarchy].CurrentMember.Properties(""Residue Number"")) - STRTOVALUE([ID].[Hierarchy].CurrentMember.Properties(""Residue Number"")) < 11) ' MEMBER [Measures].[distance] as ' ( (([ID].[Hierarchy].[Other Atom], [Measures].[x Coord]) -([ID].[Hierarchy].CurrentMember,[Measures].[x Coord]))^2 + (([ID].[Hierarchy].[Other Atom], [Measures].[y Coord]) -([ID].[Hierarchy].CurrentMember,[Measures].[y Coord]))^2 + (([ID].[Hierarchy].[Other Atom], [Measures].[z Coord]) -([ID].[Hierarchy].CurrentMember,[Measures].[z Coord]))^2 ) ' member measures.TimesCount as ' count(Intersect({AtomsInContact}, {TheTimes} * {[ID].[Hierarchy].Currentmember} * {[Sekret_ID].[Hierarchy].Currentmember})) ' select {Resnum, AtomName, Sekret_Resnum, Sekret_AtomName, TimesCount} on 0, Extract(AtomsInContact, [ID].[Hierarchy], [Sekret_ID].[Hierarchy]) on 1 FROM [2006-07-07-dyna] WHERE ( [Run].[1], [Temp].[298], [Conditions].[cs=.4,nbcycl=3,cor=10] );"; break; //------------------------------------------------------------------------------------------------ //SQL STATEMENT 9 case 9: sqlTitle = "Calculate average Phi and Psi angles using circular statistics"; mdxStatement = @" WITH //convert -180to180 to 0to360 degrees member [measures].[conv phi] as iif ([measures].[phi] <0, [measures].[phi]+180, [measures].[phi]) // take the sine of phi in radians Member [Measures].[sin phi] as ' SIN([conv phi]*(PI()/180))' // take the cosine of phi in radians Member [Measures].[cos phi] as ' COS([conv phi]*(PI() /180))' //take the average of the sets defined above MEMBER measures.[mean sin phi] AS 'avg(NONEMPTY([time].[0]:[time].[2], [measures].[phi]), [measures].[sin phi])' MEMBER measures.[mean cos phi] AS 'avg(NONEMPTY([time].[0]:[time].[2], [measures].[phi]), [measures].[cos phi])' //Evaluate if avg cos phi <0 MEMBER measures.[mean phi] AS IIF(measures.[mean cos phi] <0, ((ATN([mean sin phi]/[mean cos phi]))+PI()) , ATN([mean sin phi]/[mean cos phi])) //convert -180to180 to 0to360 degrees member [measures].[conv psi] as iif ([measures].[psi] <0, [measures].[psi]+180, [measures].[psi]) //the same as before for psi Member [Measures].[sin psi] as SIN([conv psi]*(PI() /180)) Member [Measures].[cos psi] as ' COS([conv psi]*(PI() /180))' MEMBER measures.[mean sin psi] AS 'avg(NONEMPTY([time].[0]:[time].[2], [measures].[psi]), [measures].[sin psi])' MEMBER measures.[mean cos psi] AS 'avg(NONEMPTY([time].[0]:[time].[2], [measures].[psi]), [measures].[cos psi])' MEMBER measures.[mean psi] AS IIF(measures.[mean cos psi] <0, ((ATN([mean sin psi]/[mean cos psi]))+PI()) , ATN([mean sin psi]/[mean cos psi])) //convert radians back to degrees MEMBER measures.[deg mean phi] AS ([mean phi]*(180/PI())) MEMBER measures.[deg mean psi] AS ([mean psi]*(180/PI())) //convert back to -180to180 from 0to360 member measures.[true mean phi] AS iif(measures.[phi] <0, measures.[deg mean phi]-180, measures.[deg mean phi]) member measures.[true mean psi] AS iif(measures.[psi] <0, measures.[deg mean psi]-180, measures.[deg mean psi]) SELECT {[mean sin phi], [mean cos phi],[true mean phi], [mean sin psi], [mean cos psi], [true mean psi]} on 0, Descendants([ID].[Hierarchy].[structure].[11as-1], 1) on 1 FROM [dynameomics] WHERE ([Run].[1], [time].[0]:[time].[2], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8]);"; break; //------------------------------------------------------------------------------------------------ //DEFAULT STATEMENT default: sqlTitle = "Report back a pseudo pdb file for a given time frame"; mdxStatement = @" WITH MEMBER [Measures].[atm_type] as '[id].[atom name].membervalue' MEMBER [Measures].[res_type] as '[id].residue.membervalue' MEMBER [Measures].[res_num] as '[id].[residue number].membervalue' MEMBER [Measures].[atm_num] as '[id].[Hierarchy].membervalue' MEMBER [Measures].[atom] as (""ATOM"") SELECT {Measures. [atom], [atm_num], [atm_type], [res_type], [res_num], [x Coord], [y Coord], [z Coord]} on 0, Descendants([ID].[Hierarchy].[structure].[1a2p-1], 2) on 1 FROM [dynameomics] WHERE ([Time].[3], [Temp].[498], [Conditions].[cs=.4,nbcycl=3,cor=8]);"; break; } %> Previous Query    Next Query

<%= sqlTitle%>

<%= QueryTransform.MDX_Transform(mdxStatement) %>