Nettstedet er foreløpig ikke tilgjengelig Nettstedet du vil besøke er foreløpig ikke tilgjengelig. Dette kan skyldes at nettstedet utvikles, at vedlikehold er i gang eller at nettstedet er stengt på grunn av en annen grunn. Domenesjef, vennligst se kontaktadressen din (e-post) eller kontakt supportloopia for mer informasjon. The web site does not exist. The website you want to visit is not available. Dette kan skyldes side under utvikling, å underkaste grs eller at siden av annen anledning er stngd. Domngare, se din kontaktadresse (e-post) eller kontakt supportloopia. se for mer informasjon. Nettsiden kan ikke være for øyeblikket Nettstedet du vil beske er for øyeblikket ikke tilgjengelig. This may come from since it is under development, maintenance is performed or next to a reason is closed. Domeneier, se din kontaktadresse (e-post) eller kontakt supportloopia. no for mer informasjon. Denne meldingen er også tilgjengelig i. En del av Visma middot Tlf: 46 21-12 82 22 middot Fax: 46 21-12 82 33 middot E-post: infoloopia En del av Vismakoncernen middot Tlf .: 021-12 82 22 middot Faks: 021-12 82 33 middot E-post: infoloopia. seLær om gt hvordan å beregne perioden til dato sum i cognos 10 Søkeresultatstitel: Cognos: Cognos BI 10: Beregning mellom 2 forskjellige søk. Søkeresultatbeskrivelse: 2. oktober 2013. Jeg har Q1 med CT1 tabell for å få nåværende periode avsløre1 (oppsummering). Perioden er basert på 2 spørsmål som er valgt dato (måned), for eksempel: start. Søke resultat tittel: Cognos: Cognos BI 10: Rolling 12 måneder totalt for hver måned av. Søkeresultatbeskrivelse: 24. jul 2014. Vis: Samtaler etter dato. Jeg må beregne rullende 12 måneders telling for hver måned oppført. For det første vil det være nyttig å vite hvilken versjon av Cognos 10 du bruker, hvilke eksakte kvadratdimensjonale data. Dette er kjent som et tidsstandsmåtte - det kan ikke legges over flere tidsperioder. Søke resultat tittel: Bruke periodToDate-funksjonen i IBM Cognos 10 Rapport Søkeresultatbeskrivelse: 13. mai 2011. En beskrivelse av hvordan dimensjonsfunksjonene Periode kan brukes utenfor en tidsdimensjon for å beregne en løpende total mot på. Søkeresultatets tittel: Cognos: Cognos BI 10: Rate Variance ikke samlet korrekt inn. Søkeresultatbeskrivelse: 27 aug 2013. Vis: Samtaler etter dato. 1-6 av 6. Jeg prøver å beregne pris, volum og mix varians mellom 2 perioder. Imidlertid er summen av de nedre nivåene ikke like høye nivåer. Spørsmålet mitt er hvordan beregner jeg bare bladnivåene og har Cognos BI 10.1 samlet de øvre nivåene Søkeresultatets tittel: Cognos: Cognos 8: Salgsrapporter for forrige dag, siste 5 dager og. Søkeresultatbeskrivelse: 18. mars 2011. 10 svar Siste innlegg - 18 desember, 2013 av Arhan. Deretter vil du opprette individuelle beregninger for hver periode måleverdi. Jeg tror det kan være en første gang i månedsdatoen beregning på Cognos siden som. Jeg vil kommentere Phil39s svar at siden du vil SUM disse salget beløp, ikke bruk. Søkeresultatets tittel: Cognos: Cognos 8: Moving-total flyttende gjennomsnittlige funksjoner gir. Søkeresultatbeskrivelse: 7. januar 2009. Vis: Samtaler etter dato. Her er hvordan hjelpefilen beskriver funksjonen for bevegelsestall. Oppdatert den 7. januar 2009 klokka 1: 10 PM av SystemAdmin. Jeg fulgte formelen du delte for å flytte totalt, men hva er rart. Søkeresultatets tittel: Cognos: Cognos BI 10: For å få dynamiske måneder i kvartals slutt. Søkeresultatbeskrivelse: 20. desember 2012. Miljø er Cognos 8.4.1 rapport studio mot MSAS terning. enkelte måneder (3,6,9 amp 12) bare fra månednivå innen dato dimensjon. Hvordan kan jeg automatisere lage en beregning som automatisk. Re: Å dynamisk få måneder i kvarteperioden. total (currentMeasure innen sett generere (.)). Søkeresultatstitel: Cognos: Cognos 8: Hvordan grupperes datofeltet med 1 uke eller 2 uker etc. Søkeresultatbeskrivelse: 11. mai 2011. men hva jeg vil, er å gruppere inspeksjonsdatoen for hver uke og summen av tallet som under. inspeksjonsdato Antall 2011-01-03 4 (uke 1) 2011-01- 10 3 (uke 2). ved å prøve å beregne alle nødvendige perioder basert på datoene i din. Søkeresultatets tittel: Cognos: Cognos Express: Reporter. Slik gjør du kumulativ sum. Søkeresultatbeskrivelse: 6 juni 2012. Cognos Express. Vis: Samtaler etter dato. Det virker fint å vise omsetningen etter selskap og etter måned, men jeg vil ha en kululativ sum. Søkeresultatstitel: IBM Cognos Proven Practice: Bruke Relative Time Categories. Søkeresultatbeskrivelse: 30. august 2011. Egnethet. Veiledningen gjelder for IBM Cognos Report Studio versjon 8.4 og nyere. Figur 10. Slicer member expression editor med uttrykket. Figur 10. Slicermedlem. Hvorfor er dette For å beregne 39ForrigeMonth39 brukes PrevMember-funksjonen. publiseringsdato 08302011. Søkeresultatets tittel: Cognos: Cognos BI 10: Setter inn en total sum fra en annen forespørsel. Søkeresultatbeskrivelse: 21 jan 2013. Cognos BI 10. og produkttap sammenlignet med en rullende 12 date dimensjon. Et eksempel er å beregne alle produkter som har hatt vekst mellom den forrige siste. Jeg ser på hvert produkt, hvis det har vekst mellom de to perioder. legg det til Hvordan får jeg summen av denne spørringen til en kryssstasjon Søkeresultatstitel: Cognos: Cognos 8: Henter data mellom to datoer i rapporten. Søkeresultatbeskrivelse: 5. jan 2011. Jeg har en prompteperiode (Listeboks) som holder datoen i Jan-10 format. Én datapost for å beregne regnskapsårets startdato (april-år). Søkeresultatets tittel: Cognos: Cognos 8: CY og PY måler ikke fungerer riktig. Søkeresultatbeskrivelse: 2 august 2011. Cognos 8. 10 10.1.1 10.2 10.2.1 8 8.3 8.4 8.4.1 aktiv an og. CY vs PY Measure legger årene sammen i stedet for å trekke PY fra. Periode til dato totalt (gruppert) eller Nth periode kjøring totalt (gruppert). Søkesultatets tittel: IBM Business Analytics Utprøvd praksis: Stille inn dynamisk dato. Søkeresultatbeskrivelse: 14. november 2012. Angi dynamiske datopromptverdier i IBM Cognos 10.2 Report Studio. Produkt (er): IBM Cognos 10 Interessegruppe: Rapportering. Legge til JavaScript for å angi dynamiske spørsmål. Snook (tsnook) Opher Banarie (OpherB) Hei - Har dere funnet ut løsningen på spørsmålet om hurtig tilbakestilling? Søkeresultatets tittel: Cognos 8 Dimensjonsfunksjoner - IBM Søkeresultatbeskrivelse: Cognos-programvareprodukter og relaterte tjenester som du har lisensiert eller kjøpt fra Cognos. BOTTOMSUM. Dimensjonale funksjoner. 10. Cognos proprietær informasjon. Inntektsmålet er standard fra Mål-dimensjonen. Returnerer et medlem fra en annen periode i samme slekt. TM1-kokeboken Dette er et samarbeidsprosjekt: Brukerne kan samarbeide med å skrive artikler i boken, plassere artiklene i riktig rekkefølge, og gjennomgå eller endre tidligere skrevet artikler. Så når du har litt informasjon å dele eller når du leser en artikkel av boken, og du ikke likte det, eller hvis du tror at en bestemt artikkel kunne ha blitt skrevet bedre, kan du gjøre noe med det. Artikklene er delt inn i følgende seksjoner: perspektiver server explorer kube viewer tm1web planlegger manager kube seer tips kolonner bredde når du har en visning med mange dimensjoner synlig, det skjer at det meste av skjermet går forbruket av dimensjonene kolonner og deg finn deg selv å rulle mye igjen og høyre for å lese datapunktene. For å lette dette problemet:.Velg de korteste aliasene for alle dimensjoner fra delsetteditoren. Velg delnavnene så kort som mulig. uncheck Options-gtExpand-radteksten ExcelPerspectives Tips for å redusere sannsynligheten for å krasje Excel, deaktiver automatisk beregning. Gå til Verktøy-gtOptions-gt Beregningsfane og klikk deretter på Manuell-knappen. Du kan bruke: F9 til å manuelt oppdatere alle åpne arbeidsbøkene. Skift F9 for å oppdatere bare gjeldende regneark F2 enter (rediger celle) for å oppdatere bare 1 celle i TM1 9.4. 1 regnes regnearkene automatisk når du åpner en arbeidsbok, eller endrer SUBNM til tross for at automatisk kalk er deaktivert. Fra Excel-toppmenyen klikker du Sett inn-Navn-Definer. I dialogboksen Definer navn, skriv inn TM1REBUILDOPTION. Angi verdien i boksen Refererer til 0 og klikk OK. . unngå flere dynamiske stykker på flere ark i en arbeidsbok hvis du kan, ellers blir Excel ganske ustabil og noen referanser kan bli ødelagt. hvis du får e10) datakatalog ikke funnet feil når du laster inn perspektiver, må du definere datakatalogen på den lokale serveren din selv om du ikke kan kjøre en. Gå til Filer-gtOptions og skriv inn en gyldig mappe i datalisten. Hvis den boksen er grå, må du manuelt redigere variabelen i tm1p. ini som er lagret på PCen. Alternativt kan du endre innstillingen direkte fra Excel med følgende VBA-kode: Application. Run (OPTSET, DatabaseDirectory, C: somepath) Hvilken TM1-versjon er du på publiseringsvisninger Publiseringsbrukernes visning er fortsatt langt fra en rask og enkel prosess i TM1. Først kan administratoren ikke se andre brukeres visninger. For det andre kan brukerne ikke publisere sine egne synspunkter selv. Så publisere visninger krever alltid direkte inngrep fra administrasjonen, vel ikke lenger :) hvis ((ViewExists (Cube, View) 0) ((ViewExists (Cube, View) 1) amp (Overskriv Y))) Få Brukernavn Hvis (SubsetExists (Clients, WhoAmI) 0) SubsetDestroy (Clients, WhoAmI) Endif StringMDX-klienter), USERNAME) SubsetCreatebyMDX (WhoAmI, StringMDX) UserSubsetGetElementName (Klienter, WhoAmI, 1) SubsetDestroy (Clients, WhoAmI) Kopier visning til offentlig plass TM1PathE: TM1DataTM1Server Query cmd c mkdir TM1PathCube vues ExecuteCommand (Query, 0) Query cmd c kopi TM1PathUser Cube vues Vis. vue TM1PathCube vues Vis. vue ExecuteCommand (Query, 0) unload cube først for å gjenopplate vues CubeUnload (Cube) Else ItemReject (Se visning i Cube Cube allerede eksisterer) Endif 2. Endre TM1Path og lagre 3. I Server Explorer, Prosess-gtSecurity-oppdrag, sett prosessen som Les for alle grupper som bør få lov til å publisere Nå kan brukerne publisere sine synspunkter alene ved å utføre denne prosessen, de trenger bare å skrive inn navnet o f kuben og visningen å publisere. Koden i den ovennevnte Prolog-fanen kan erstattes med disse 5 linjene: hvis ((ViewExists (Cube, View) 0) ((ViewExists (Cube, View) 1) amp (Overskriv Y)) PublishView (Cube, View, 1 , 1) Else ItemReject (Vis visning i Cube Cube eksisterer allerede) Endif Thats it :) Takk til den anonyme leseren (vennligst registrer deg hvis du vil bli kreditert) for forslaget. delsettredigerings tips for delsetteditoren for å vise konsoliderte elementer under barna sine: View - gt Utvide ovenfor for å få et raskere svar fra delsetteditoren, deaktiver Egenskaper Vinduevisning - gt Egenskaper Vindu eller klikk Vinduet Egenskaper for visning fra verktøylinjen for å legge til ett eller flere elementer i en eksisterende delmengde uten å gjenskape det: Fra delsetteditoren - Rediger-gtInsert delsett - Velg elementene - Klikk OK for å lagre som privat Subset1 nå Subset1 er lagt til i ditt eksisterende undersett - Utvid Subset1 - Klikk på Subset1-konsolideringen elementet og deretter slette du kan nå lagre delmengden med de nye elementene for å få kubevisninger til å vises mye raskere i tm1web: delmåter av dimensjonene på toppen må bare inneholde 1 element hver klikker på ikonene i rullegardinmenyen Eksporter vil ikke ha noen effekt , bare ved å klikke på den tilknyttede teksten til høyre slicesnapshotpdf, starter en eksportsporing TM1 referanser Hjemmelagde TM1 rapporter kan bli ganske innviklet og brukere kan få en vanskelig å oppdatere dem som det er vanskelig å fortelle hvor noen TM1 formler peker på. Excel-verktøylinjen for Excel-formel kan være nyttig i slike situasjoner. høyreklikk ved siden av den øverste linjen for å hente barmenyen. velg revisjonslinje Ganske nyttig for å få omfanget av en kube eller dimensjonsreferanse i en rapport eller for å se hvilke elementer en DBRW-formel er laget av. Utviklerens turbo integrator regulerer VBA-koden nærmere på dynamiske skiver Dynamiske skiver kan være ganske nyttige når elementene du viser i rapportene dine, utvikler seg over tid, oppdaterer de automatisk med de nye elementene. Følgende artikkel vil prøve å grave inn i parametrene som definerer disse stykkene, og vise noen av mulighetene for å kommunisere med disse. Ideen ble opprinnelig sendt av Philip Bichard. De dynamiske delingsparametrene lagret i navnelisten på regnearket for å vise disse i Excel: Sett inn - gt navn - gt Paste - gt-limeliste De fleste parametrene er definert som SL xx C yy xx er skivereferansen 01, 02, 03 for så mange skiver som det er i rapporten er du den stakkede dimensjonsreferansen ex: SL01 C 01 gjelder den første stablede dimensjonen på toppen av kolonnene SL01C02 gjelder den andre stablede dimensjonen fra toppen SL02R01 gjelder den første stablede dimensjonen for rader på venstre side av den andre delen CUBE01RNG-plasseringen av cellen som heter navnet på kuben, referert til SL01C01DIMNM-undersettet eller dimensjonsnavnet hvis det ikke brukes en lagret delmengde SL01C01ELEMS01 Liste over elementer som skal vises SL01C01EXPANDUP 1 0 Triggerkonsolideringer som kollapset eller utvidet SL01C01FMTNM navn av elementene format for å bruke SL01C01HND. SL01C01IDXS01. SL01C01RNG rekkevidde for stablet dimensjon SL01CPRX01. SL01DATARNG rekkevidde for DBRW-celler SL01FILT filterinnstillinger SL01R01ALIAS navn på aliaset som brukes SL01R01DIMNM SL01R01ELEMS01 liste over elementer som skal vises SL01R01ELEMSxx. SL01R01EXPANDUP SL01R01HND SL01R01IDXS01 SL01R01NM delmengde navn SL01R01RNG seksjon grenseområde SL01RPRX01 SL01TIDXS01. SL01TPRX01. SL01VIEWHND. SL01VIEWIDX. SL01ZEROSUPCOL null suppress på kolonner utløse SL01ZEROSUPROW null undertrykke i rader utløse SX01C01ENABLE trigger SX01C01IDX SX01C01WD SX01R01ENABLE trigger SX01R01HT SX01R01IDX trigger SX01RNG seksjon grenseområdet SXBNDDSP seksjon grense skjerm trigger TITLE1 subnm formel for første dimensjon TITLE1NM undergruppe TITLE1RNG celleplassering TITLE2 TITLE2NM TITLE2RNG TITLE3 TITLE3ALIAS alias som skal vises for dimensjonen TITLE3RNG omdefinerer følgende navn SL01FILT fra SL01FILT FUNCTIONPARAM0.000000SORTORDERdescTUPLESTRSales Measurements. Sales Units til SL01FILT FUNCTIONPARAM0.000000SORTORDERascTUPLESTRSales Measurements. Sales Kostnad vil endre kolonnen som sorteringen er laget fra salgsenheter til salgspris, og også rekkefølgen fra nedstigende til stigende. Man kan også oppnå et lignende resultat med et MDX-uttrykk. Følgende kode vil endre delmengden fra dimensjonen på radstakken til den forhåndsdefinerte Nivå Zero Dynamiske delmengden, så alle elementene vil da bli vist. Merk, du må bruke TM1REFRESH eller Alt-F9 for å få skiven til å gjenoppbygge seg selv. TM1RECALC (F9) vil bare oppdatere DBRW-formlene. Dynamiske stykker vil brytes med følgende popup Ingen verdier tilgjengelig på grunn av at noe element ikke eksisterer lenger eller ikke har noen verdier for det aktuelle stykket. En enkel løsning er å koble fra TM1-serveren, laste inn rapporten, fjerne elementet som forårsaker problemer fra skive - og navnebordet, og koble til TM1-serveren, den dynamiske snittet oppdateres på nytt igjen, bare bra. arbeid på går. attributter Rediger attributter. hvis du får følgende melding Denne operasjonen får tilgang til en dimensjon som inneholder et stort antall elementer. Opplasting av disse elementene fra serveren kan ta noen minutter. Fortsett å redigere attributter kuben i stedet, det er mye raskere: - View - gt Display Control Objects - Åpne dimensjonen ElementAttributes kube - endre deretter de nødvendige feltene som i en hvilken som helst kube Legg til nytt attribut for store dimensjoner, det er raskere å bare opprette en midlertidig TI-prosess med den følgende koden i Prolog ATTRINSERT (Model. InteriorColor, S) Dette eksemplet oppretter strengeintervallet InteriorColor for modelldimensjonen. sjekker om en attributtalier allerede finnes, kan vi si at vi vil opprette et alias Kode for dimensjonen Kunde. I Avansert-gtProlog-fanen: Hvis (DIMIX (ElementAttributesCustomer Code) 0) AttrInsert (Kunde ,, Kode, A) Endif Så du trenger ikke å bekymre deg for AttrInsert genererer en feil hvis du noen gang må kjøre prosessen igjen. Hvis du oppdaterer aliaser i TM1, må du slette Excel-cachen for å se endringene ved å kjøre makro-mclear eller bare starte Excel på nytt. Bulk Reporting TM1-gtPrint Report-funksjonen fra Perspectives er nyttig for å generere statiske rapporter i bulk for et gitt sett med elementer. Følgende kode etterligner og utvider denne funksjonaliteten for å oppnå masserapportering for en TM1-rapport på en mer fleksibel måte. For eksempel kan du få en rapport basert på filialene til et selskap som skal lagres i hver respektive grendokumentmappe, i stedet for å få dem alle dumpet i en enkelt mappe, eller du kan også få hver grenrapport sendt til egen filialforvalter. kommenterer deler av kode i TI Du vil gjerne kommentere deler av kode for arv eller fremtidig bruk i stedet for å fjerne den. Tilføyning på forsiden av hver linje er stygg og setter opp innrykkingen. Her er en rask, fin og enkel løsning. Det er det. Det kan også være nyttig å slå av automatisk generert kode i GENERATED STATEMENTS START. Jeg vil sterkt anbefale at du legger til tydelige markører rundt koden kommentert ellers kan det være veldig enkelt å overse den korte if-setningen og tilbringe en stund og lure på hvorfor prosessen ikke gjør noe. Takk Paul Simon for tipset. oppretter (midlertidig) visninger når du lager visninger fra kubeviseren, det er en vanskelig grense for størrelsen på visningen som vises. Det er 100MB (32bit) eller 500MB (64bit) som standard, det kan endres med parameteren MaximumViewSize i tm1s. cfg. Men det er ikke praktisk å generere slike store visninger manuelt. Et alternativ er å gjøre det fra Turbo Integrator: Opprett en ny TI-prosess velg TM1 Cube Vis Importer Klikk Bla gjennom velg kubeklikk Opprett visning derfra, kan du opprette noen visninger, men det kan være mer fordelaktig å opprette og slette visninger på-the - fly så serveren din er renere og brukerne blir mindre forvirret. Følgende kode genererer en visning fra kuben MyCube, inkludert alle elementer i kuben. Du må legge til noe SubsetCreateSubsetElementInsert for å begrense visningen, fjern alle konsolideringer. i de fleste tilfeller forstyrrer de importen, og du får bare delvis import eller ingenting i det hele tatt. -------- prolog CubeName MyCube ViewName TIImport SubsetName TIImport jeg 1 sløyfe gjennom alle dimensjoner av kuben mens (tabdim (CubeName, i) ltgt) ThisDim tabdim (CubeName, i) Hvis (SubSetExists (ThisDim, SubsetName) 0 ) StringMDX, 0) Opprett en delmengde filtrering ut alle hierarkier SubsetCreatebyMDX (SubsetName, StringMDX) EndIf ii 1 ende Hvis (ViewExists (CubeName, ViewName) 0) ViewCreate (CubeName, ViewName) Endif jeg 1 sløyfe gjennom alle dimensjoner av kuben mens tabsim (CubeName, i) ltgt) ViewSubsetAssign (CubeName, ViewName, tabdim (CubeName, i), SubsetName) ii 1 ende ViewExtractSkipCalcsSet (CubeName, ViewName, 1) ViewExtractSkipZeroesSet (CubeName, ViewName, 1) -------- ------ epilog opprydding ViewDestroy (CubeName, ViewName) Jeg 1 sløyfe gjennom alle dimensjoner av kuben mens (tabdim (CubeName, i) ltgt) SubsetDestroy (tabdim (CubeName, i), SubsetName) ii 1 ende Opprette dynamisk Subsets i Applix TM1 med MDX - En Primer om dette dokumentet Denne MDX Primer er ment å fungere som en simp le introduksjon til å lage dynamiske dimensjonsundergrupper ved hjelp av MDX i TM1. Det fokuserer på å gi arbeidseksempler fremfor å forsøke å forklare den komplette teorien om MDX og sørger for å dekke de funksjonene som er mest nyttige for TM1-brukere. TM1 for øyeblikket (som av 9,0 SP3) tillater bare brukere å bruke MDX for å opprette dimensjonsundergrupper og ikke definere kubevisninger. Dette betyr at bruken av MDX i TM1 ofte er ganske annerledes både når det gjelder syntaks og intensjon fra eksempler som finnes i bøker og på internett. Siden MDX (Multi-Dimensional eXpressions) er et bransjestandardsspråk for OLAP-databaser fra Microsoft, er det mange referanser og eksempler på Internett, men husk at TM1 ikke støtter alle aspekter av språket og legger til noen få unike egenskaper av seg selv. Dette kan gjøre det vanskelig å bruke eksempler på nettet, mens alle eksemplene i dette dokumentet enkelt kan kopieres og limes inn i TM1 og vil utføres uten endring, forutsatt at du har eksemplet mini-modellen opprettet som dokumentert senere. Fullt dokument som bare en HTML-side her. Hva er en MDX-basert dynamisk delmengde i TM1 En dynamisk delmengde er en som ikke er en fast, statisk liste, men i stedet er den basert på en spørring som blir revurdert hver gang delmengden brukes. Faktisk kan MDX brukes til å opprette en statisk delmengde, og et eksempel er vist nedenfor, men dette er usannsynlig å være nyttig eller vanlig. Noen eksempler på nyttige dynamiske delsett kan være en liste over alle produkter på grunnnivå. En liste over våre 10 beste kunder med bruttoavgift. En liste over forsinkede forsendelsesforsendelser alle kostnadssteder som ennå ikke har sendt budsjettet. Poenget er at disse lister (delsett) kan variere fra sekund til sekund basert på strukturen eller dataene i TM1. For eksempel, så snart en ny filial er lagt til Europa, vil den europeiske grenenes undergruppe umiddelbart inneholde denne nye grenen, uten at manuell inngriping er nødvendig. MDX er søkspråket som brukes til å definere disse delsettene. MDX er et bransjestandardsprog for multidimensjonale databaser som TM1, selv om TM1 bare støtter en viss undergruppe (unnskyld ordspillet) av hele språket, og legger til i noen få unike funksjoner i tillegg. Når du definerer en delmengde med MDX i stedet for en standardundergruppe, lagrer TM1 denne definisjonen i stedet for det resulterende settet. Dette betyr at definisjonen eller spørringen går igjen hver gang du ser på den uten at brukeren eller administratoren trenger å gjøre noe i det hele tatt. Hvis databasen er endret på en måte, kan du få forskjellige resultater fra siste gang du brukte den. For eksempel, hvis en delmengde er definert som barn av West Coast Branches, og dette returnerer i utgangspunktet Oakland, San Francisco, San Diego når det er først definert, kan det senere returnere Oakland, San Francisco, San Diego, Los Angeles en gang LA har blitt lagt inn i dimensjonen som et barn av vestkysten grener. Dette er hva vi mener ved å dynamiske resultatendringene. En annen grunn som kan føre til at delmengden endres, er når den er basert på verdiene i en terning eller attributt. Hver dag i avisen er de største aksjemarkederne listet opp, for eksempel en topp 10 når det gjelder aksjekursvekst. I en TM1-modell vil dette være en delmengde som ser på et aksjekursendringsmål, og det vil tydeligvis komme tilbake til et annet sett på 10 medlemmer hver dag. Den beste delen er at delmengden vil oppdatere resultatene automatisk uten noe arbeid som trengs av en bruker. Slik oppretter du en MDX-basert delmengde i TM1 De samme grunnleggende trinnene kan følges med alle eksemplene i dette dokumentet. Vanligvis kan eksemplene kopiere og limes inn i uttrykksvinduet i delsettreditoren for den aktuelle dimensjonen ofte Produkt. Merk at det er irrelevant hvilken kube dimensjonen blir brukt av, vil du få samme resultat om du åpner dimensjonen Subset Editor fra en kubevisning, kubetreet i Server Explorer eller dimensjonstreet i Server Explorer. For å kunne se og redigere en MDX-spørring må du kunne se uttrykkvinduet i delsetteditoren. Hvis du vil bytte dette vinduet av og på, velger du Vis uttrykksvindu. Du kan nå bare skrive inn (eller lime inn) spørringen din i denne Expression Windows og trykk Oppdater-knappen for å se resultatene. Slik oppretter du en statisk delmengde med MDX En statisk delmengde er en som aldri varierer i innholdet. Denne spørringen vil returnere de samme 3 medlemmene (Rabattlån, Terminlån og Detaljhandel) hver gang. Ikke bekymre deg, det blir mer spennende herfra. Hvordan oppretter du en dynamisk delmengde med MDX TM1, støtter bare et visst antall funksjoner fra den komplette MDX-spesifikasjonen. Ulike versjoner av TM1 vil støtte forskjellige funksjoner (og potensielt støtte dem på forskjellige måter). Det gyldige settet av funksjoner for versjonen av TM1 som du bruker, finnes i hovedhjelpfilen, under Referansemateriale MDX Funksjonsstøtte. Før du prøver å skrive en ny spørring, sørg for at den støttes, og selv om noen unoterte funksjoner sikkert gjør arbeid, må de brukes på egen risiko. Standard feilmeldingen som betyr at funksjonen egentlig ikke støttes av din versjon av TM1, Mislyktes å kompilere uttrykk. Ett ord av advarsel: Resultatet av en dynamisk delmengde kan i sin natur endre seg. Når du inkluderer dynamiske delsett i visninger, prosesser, SUBNM-funksjoner og så videre, bør du nøye vurdere hva potensielle fremtidige resultater kan være, spesielt hvis delmengden en dag kunne være tom. De to vanligste metodene for å skape en dynamisk delmengde er å lage dem for hånd eller ved hjelp av TurboIntegrator. For hånd . Du kan enten skrive inn (eller lime inn) en forespørsel i Expressions-vinduet som forklart tidligere, eller du kan velge Verktøy Record Expression (og deretter Stopp opptak når du er ferdig) for å slå på en slags videoopptaker. Du kan deretter bruke de vanlige funksjonene til delsetteditoren (for eksempel velg etter nivå, sorter nedstigning osv.) Og denne opptakeren vil gjøre handlingene dine til et gyldig MDX-uttrykk. Dette er en fin måte å se noen eksempler på gyldig syntaks, spesielt for mer komplekse søk. Når du har tatt opp et uttrykk og velger Stopp innspilling TM1, vil du be om å bekrefte om du vil legge til uttrykket med delmengden - sørg for å si ja og merk av for avkrysningsboksen Lagre uttrykk når du lagrer den resulterende delmengden, ellers bare en statisk liste av resultatet er lagret, ikke selve det dynamiske spørsmålet. Bruke TurboIntegrator. Bare én linje, med SubsetCreateByMDX, er nødvendig for å opprette og definere delmengden. Du må vite hvilken spørring du vil ha som definisjonen allerede. Vær oppmerksom på at spørringen kan bygges opp i TI-skriptet ved hjelp av tekstforbindelser, så det kan inkorporere variabler fra skriptet ditt og tillate lange spørringer å bli bygget opp i etapper som er enklere å lese og vedlikeholde. SubsetCreatebyMDX (Base Products,, 0)) Alle TI-opprettede MDX-undersettene lagres som dynamiske MDX-spørringer automatisk og ikke som en statisk liste. Vær oppmerksom på at, i hvert fall opptil TM1 v9.0 SP3, kan MDX-baserte delsett ikke ødelegges (SubsetDestroy) hvis de brukes av en offentlig visning, og de kan ikke gjenopprettes ved å bruke en annen SubsetCreateByMDX-kommando. Derfor er det vanskelig å endre MDX-baserte delsett ved hjelp av TI. Selv om den dynamiske naturen til delsettet definisjonen kan gjøre det litt usannsynlig at du faktisk vil gjøre dette, er det viktig å huske på. Hvis du trenger å endre et aspekt av spørringen (f. eks. En TM1FilterByPattern fra 2006-12 til 2007-01, må du kanskje definere spørringen for å bruke eksterne parametre, som dokumentert i dette dokumentet. Dette vil få en liten ytelse effekt over det enklere hardkodet versjon. Også filter mot verdier av en kube med SubsetCreateByMDX i Epilog-fanen f. eks. 0), Test. (Posting Measures. Amount) gt 0) virker ikke hvis verdiene er blitt lastet i kategorien Data. Du må utføre kommandoen SubsetCreateByMDX i en påfølgende TI-prosess. Vær oppmerksom på at TI har en grense på 256 tegn for å definere MDX-undergrupper, i hvert fall opptil v9.1 SP3, noe som kan være ganske begrenset. Syntaks og layout En forespørsel kan brytes over flere linjer for å gjøre den mer lesbar. For eksempel: FILTER (, 0), Test2. (Rate MeasuresRate) 19) er mer lesbar enn å ha hele spørringen i en linje. Den faktiske filterseksjonen er lettere å lese og modifisert nå ved å ha den på en linje av seg selv. Merk at referanser til medlemmer vanligvis har dimensjonsnavnet som prefiks. For eksempel er dimensjonsnavnet faktisk valgfritt, men bare hvis medlemsnavnet (Retail i dette tilfellet) er helt unikt innen hele serveren, det vil si at det ikke er noen kuber, dimensjoner eller medlemmer med det nøyaktige navnet. For eksempel er dette det samme spørsmålet med dimensjonsnavnet utelatt: Som ville fungere i sammenheng med prøveapplikasjonen som brukes av dette dokumentet, men det ville være risikabelt i en applikasjon i virkeligheten. Feilmeldingen mottatt når du glemmer å spesifisere prefikset, vil være noe som, Nivå eller medlemsnavn. Retail tvetydig: funnet i dimensjoner, og så fortsetter det å oppgi de ulike dimensjonene der det ikke-unike medlemnavnet kan bli funnet, noe som er veldig hjelpsom. Det er derfor absolutt sikreste og mest performante å alltid bruke dimensjonsprefikset. Bruken av firkantede parentes kan noen ganger virke litt vilkårlig når man leser eksempler på MDX-spørringer. Faktum er at et OLAP-objektnavn (f. eks. Kubenavn, dimensjonsnavn, medlemsnavn) bare skal være vedlagt i firkantede parenteser hvis den inneholder et mellomrom, starter med et tall eller er et MDX-reservert ord (f. eks. Velg). Men noen ganger kan det være enklere å bestemme seg for alltid å bruke parentes, slik at lignende søk kan sammenlignes side om side lettere. Den nøyaktige definisjonen av et medlem i TM1 er nesten alltid uttrykt som Dimensjonsnavn. Medlemsnavn og ikke mer. I andre produkter som også bruker MDX som et spørrespråk (for eksempel Microsoft Analysis Services), kan du legge merke til at spørringene spesifiserer hele banen fra dimensjonsnavnet gjennom hierarkiet ned til medlemsnavnet, for eksempel: Date.2009.Q1.Feb. Week 06 Dette kan også skrives som Date.2009Q1FebWeek 06 Årsaken til dette er at andre produkter kanskje ikke krever at hvert medlems navn skal være unikt siden hvert medlem har en kontekst (sin familie) slik at den blir unikt identifisert, og derfor de trenger å vite nøyaktig hvilken uke 06 er nødvendig siden det kan være andre (i 2008 for eksempel i eksempelet ovenfor). TM1 krever at alle medlemsnavn, på et hvilket som helst nivå (og innenfor Aliases), er helt unike innenfor den dimensjonen. TM1 ville trenge deg til å gjøre Q1, Feb og Week 06 mer eksplisitt i første omgang (dvs. Q1 2009, Feb 2009, Uke 06 2009), men du kan da bare referere til Date. Week 06 2009. Endelig, tilfelle (dvs. store bokstaver versus små bokstaver) er ikke viktig med MDX-kommandoer (f. eks. Filter eller FILTER, TOPCOUNT eller TopCount er alle fine), men igjen kan du foretrekke å bruke bare en stil som standard for å gjøre det lettere å lese. Eksempelmodellen brukt I dette dokumentet vil mange eksempler på dynamiske spørsmål bli gitt. De jobber alle sammen (akkurat som skrevet, bare kopier og lim dem inn i Expression Window i Subset Editor av den aktuelle dimensjonen for å bruke dem) på det enkle settet med kuber og dimensjoner vist nedenfor. Modellen er bevisst enkel uten spesielle egenskaper, så du burde finne det enkelt å overføre arbeidet til din egen modell. Modellen brukte 1 hoveddimensjon, produkt, hvor det store flertallet av søkene fungerer, pluss 3 kuber: Test, Test2 og Test3. Dataverdiene i kubene vil variere under testingen (du vil justere verdiene og gjenopprette spørringen for å sikre at resultatene endres og er korrekte), men skjermbildene nedenfor viser kube - og dimensjonsstrukturer godt nok til at du raskt kan gjenopprette dem eller hvordan du bruker din egen modell i stedet. For å forenkle distribusjonen av dette dokumentet er det ingen hensikt å også distribuere de faktiske TM1-modellfilene. Vær oppmerksom på at hoveddimensjonen brukes, Produkt, kjennetegnet ragged og flere, hierarkier. TM1SubsetAll, Members, member range The basis for many queries, this returns (almost, see below) the entire dimension, which is the equivalent of clicking the All button in the Subset Editor. TM1SUBSETALL( Product ) Note that only the final instance in the first hierarchy of members that are consolidated multiple times is returned. The Members function, on the other hand, delivers the full dimension, duplicates included: Product. Members A range of contiguous members from the same level can be selected by specifying the first and last member of the set you require with a colon between them. This example returns Jan 1st through to Jan 12th 1972. Select by Level, Regular Expression (Pattern) and Ordinal Selecting members based on their level in the dimension hierarchy (TM1FilterByLevel) or by a pattern of strings in their name (TM1FilterByPattern) can be seen easily by using the Record Expression feature in the subset editor. The classic all leaf members query using TM1s level filtering command TM1FilterByLevel: , 0) Select all the leaf members which match the wildcard HC i. e. that have H and C as the third and fourth characters from the end of their name. , 0), HC) The reason that these functions start with TM1 is that they are not standard MDX commands and are unique to TM1. There are two main reasons why Applix will implement such unique functions: to add a feature that is present in standard TM1 and users will miss if it is not there or because standard TM1 has the same feature as MDX but has historically implemented it slightly differently to MDX and therefore would, again, cause users problems if it was only implemented in the standard MDX way. In these two cases, TM1FilterByPattern brings in a function commonly used by TM1 users that is lacking in MDX, while TM1FilterByLevel exists because TM1 has, since its launch in 1984, numbered consolidation levels starting at zero for the leaf level rising up the levels to the total members, while Microsoft decided to do it the exact opposite way. In certain situations it is useful to use the standard MDX levels method and this is also available with the Levels function. It allows you return the members of a dimension that reside at the same level as a named member, just bear in mind that standard MDX orders the levels in terms of their distance from the top of the hierarchy and not the bottom as TM1. This example returns all the members at the same level as the Retail member: Which, although Retail is a high level consolidation, returns an N: item (Product Not Applicable) in the dimension because this rolls straight up into All Products as does Retail so they are considered to be at the same level. To filter the dimension based on a level number you need to use the. Ordinal function. This is not documented as being supported in the Help file, and did not work in 8.2.7, but appears to work in 9.0 SP3 and 9.1.1.36 at least. This example returns all the members at Level 1: , Product. CurrentMember. Level. Ordinal 1) This example would return all members not at the same level as Discount Loan. , Product. CurrentMember. Level. Ordinal TM1Sort, TM1SortByIndex and Order TM1Sort is the equivalent of pressing one of the two Sort Ascending or Sort Descending buttons in the subset editor i. e. sort alphabetically. TM1SortIndex is the equivalent of pressing one of the two Sort by index, ascending or Sort by index, descending buttons in the subset editor i. e. sort by the dimension index (dimix). Order is a standard MDX function that uses a data value from a cube to perform the sort. For example, sort the list of customers according to the sales, or a list of employees according to their length of service. Sort the whole Product dimension in alphabetically ascending order. , ASC) Sort the leaf members of the dimension according to their Amount values in the Test cube from highest downwards. ORDER( ,0) . Test.(Posting Measures. Amount), BDESC) Note that using BDESC instead of DESC gives radically different results. This is because BDESC treats all the members across the set used (in this case the whole dimension) as being equal siblings and ranks them accordingly, while DESC treats the members as still being in their family groups and ranks them only against their own direct siblings. If youre not sure what this means and cant see the difference when you try it out, then just use BDESC Order can also use an attribute instead of a cube value. In this example the AlternateSort attribute of Product is used to sort the children of Demand Loan in descending order. It is a numeric attribute containing integers (i. e. 1, 2, 3, 4, etc) to allow a completely dynamic sort order to be defined: , Product. AlternateSort, DESC) TopCount and BottomCount A classic Top 10 command: , 0), 10, Test.(Posting Measures. Amount) ) By omitting a sort order it sorts in the default order (which has the values descending in value and breaks any hierarchies present). A Top 10 query with an explicit sort order for the results. ,0), 10, test.(Posting Measures. Amount)), test.(Posting Measures. Amount), BDESC) BDESC means to break the hierarchy. Note how the chosen measure is repeated for the sort order. Although the same measure is used in the sample above you could actually find the top 10 products by sales but then display them in the order of, say, units sold or a Strategic Importance attribute. This is the top 10 products based on Test2s Rate values, not ordered so will be sorted according to the values in Test2. ,0), 10, Test2.(Rate Measures. Rate)) This is the top 10 products based on test2s data in the Rate measure, ordered from 10 through 1. ,0), 10, test2.(Rate Measures. Rate)), test2.(Rate Measures. Rate), ASC) TopCount automatically does a descending sort by value to get the TOP members. If this is not desired, you might want to use the Head function (detailed below) instead. BottomCount is the opposite of TopCount and so is used to find the members with the lowest values in a cube. Beware that the lowest value is often zero and if that value needs to be excluded from the query you will need to refer to the section on the Filter function later in this document. A Bottom 10 query with an explicit sort order for the results. ,0), 10, test.(Posting Measures. Amount)), test.(Posting Measures. Amount), BASC) Further reading: TopSum, TopPercent and their Bottom equivalents are useful related functions. Filter, by values, strings and attributes The FILTER function is used to filter the dimension based on some kind of data values rather than just the members and their hierarchy on their own. This data might be cube data (numeric or string) or attribute data. This requires a change of thinking from straightforward single dimensions (lists with a hierarchy and occasionally some attributes) to a multi-dimensional space, where every dimension in these cubes must be considered and dealt with. This example returns the leaf members of Product that have an Amount value in the Test cube above zero. , 0), Test.(Posting Measures. Amount) gt 0 ) Since the Test cube only has 2 dimensions Product and Posting Measures this is a simplistic example. Most cubes will have more than just the dimension being filtered and the dimension with the filter value in. However, it is simple to extend the first example to work in a larger cube. This example returns the leaf members of Product that have an Amount value for All Entities in the Test3 cube above zero. , 0), Test3.(Entity. All Entities, Posting Measures. Amount) gt 0 ) As you can see from the above, simply include all the requisite dimension references inside the round brackets. Usually you will just need a specific named member (e. g. All Entities). If the dimension is omitted then the CurrentMember is used instead which is similar to using dimension (i. e. for each) in a TM1 rule, and could return different results at a different speed. Instead of just using a hardcoded value to filter against (zeroes in the examples above), this example returns all products with an amount in the Test cube greater than or equal to the value in the cell MidasJCFI, Amount. , 0), Test.(Posting Measures. Amount) gt Test.(Product. MidasJCFI, Posting Measures. Amount) ) This query returns the products that have a Rate value in Test2 greater than MidasJXCOs Rate in Test2. Now, this query just returns a set of products its up to you which cube you display these products in i. e. you can run this while browsing Test and therefore return what looks like an almost random set of products but the fact is that the query is filtering the list of products based on data held in Test2. This may not immediately appear to be useful but actually it is, and can be extremely useful for example display the current years sales for products that were last years worst performers. If the data for two years was held in different cubes then this would be exact same situation as this example. There are often many potential uses for displaying a filteredfocused set of data in Cube B that is actually filtered based on data in Cube A. , 0), Test.(Posting Measures. Amount) gt Test2.(Product. MidasJXCO, Rate Measures. Rate) ) As detailed elsewhere, Tail returns the final member(s) of a set. An example of when it is handy when used with Filter would be for finding the last day in a month where a certain product was sold. The simple example below initially filters Product to return only those with an All Entity Amount gt 0, and then uses tail to return the final Product in that list. , 0), Test3.( Entity. All Entities, Posting Measures. Amount) gt 0 )) Note: with the other cubes having more dimensions than does Test the current member is used (each), not All so whether you want each or All you should write this explicitly to be clearer. You can even filter a list in Cube1 where the filter is a value in one measure compared to another measure in Cube1. This example returns the Products with an amount in the Test cube above zero where this Amount is less than the value in Count. , 0), (Test. Posting Measures. Amount 0 ) This example returns all the leaf products that have an Amount in Entity Not Applicable 10 greater than the Amount in Entity Not Found, in the Test3 cube. Not very useful but this was the only example cube we had to work with, but it would be very useful when comparing, say, Actual Q1 Sales with Budget, or finding out which cost centres Q2 Costs were 10 higher than Q1. Later in this document we will see how to take that 10 bit and make it a value from another cube, thus allowing administrators, or even end users, to set their own thresholds. , 0), test3.(Entity. Entity Not Applicable, Posting Measures. Amount) 1.1 gt test3.(Entity. Entity Not Found, Posting Measures. Amount)) Filtering for strings uses the same method but you need to use double quotes to surround the string. For example, this query returns products that have a value of bob in the Test2 cube against the String1 member from the StringTest dimension. Note that TM1 is case-insensitive. , 0), Test2.(StringTest. String1) bob ) Filter functions can be nested if required, although the AND or INTERSECT functions may be useful alternatives. The limit to the number of characters that an MDX subset definition can sometimes be, 256, is too restricting for many data-based queries. When trying to shoehorn a longer query into less characters there are a few emergency techniques that might help: consider whether you need things like TM1FILTERBYLEVEL, 0 (it might well be that the filter would only return members at the leaf level by definition anyway) whether the dimension name prefix can be removed if the member is guaranteed to be unique remove all spaces lookup cubes are not for end users so maybe you could shorten some names (cubes, dimension, members) drastically whether there are alternative functions with shorter syntaxes that return the same result - e. g. an INTERSECT or AND versus a triple FILTER. Finally, if it really is vital to get a long query working then you can build up the final result in stages i. e. put some of the filtering into Subset1, then use Subset1 as the subject of Subset2 which continues the filtering, etc. Parent, Children, FirstChild, LastChild, Ancestors, Descendants, DrillDownLevel and TM1DrilldownMember Children returns the set of members one level below a named parent. FirstChild returns the first child one level below a named parent. Returns Call Participation Purchased. LastChild returns the last child one level below a named parent. This is excellent for finding the last day in a month, since they can vary from 28 to 31. Another example is when a consolidation is set up to track a changing set of members (e. g. Easter, or Strategic Customers). Returns Term Participation Purchased. Parent returns the first parent of a given member. If a member has more than one parent, and the full unique path to the member is not specified then the first parent according to the dimension order is returned. Returns Bonds. Would force TM1 to return the second parent, External Bonds. Descendants returns the named parent and all of its descendant children i. e. the hierarchy down to the leaf level: TM1DrilldownMember returns the same thing as descendants: , ALL, RECURSIVE ) DrillDownLevel just returns the parent and its immediate children: ) DrillDownLevel can be extended with a parameter to say which level to return the members from, rather than the level immediately below, but this doesnt appear to work in TM1 v9.0 SP2 through to 9.1.1.36. The common requirement to return a list of just leaf-level descendants of a given consolidated member just needs a level filter applied to the TM1DrillDownMember example above:,ALL, RECURSIVE), 0) Or: , 0) Ancestors is like a more powerful version of Parent it returns a set of all the parents of a member, recursively up though the hierarchy including any multiple parents, grandparents, etc. Returns 2006 October, 2006 Q4, 2006 H2, 2006, All Dates. Lag, Lead, NextMember, PrevMember, FirstSibling, LastSibling, Siblings and LastPeriods Lags and Leads are the equivalent of DnextDprev. will return 2006-10-04. Lead(n) is the same as Lag(-n) so either function can be used in place of the other by using a negative value, but if only one direction will ever be needed in a given situation then you should use the correct one for understandabilitys sake. Note that they only return a single member so to return the set of members between two members you can use the lastperiods function. Equally you can use NextMember and PrevMember when you only need to move along by 1 element. Or: To return the 6 months preceding, and including, a specific date: Or: LastPeriods(6, Date.2006-10-03) Both of which work because LastPeriods is a function that returns a set, and TM1 always requires a set. Curly braces convert a result into a set which is why many TM1 subset definitions are wrapped in a pair of curly braces, but in this case they are not required. This will return the rest (or the ones before) of a dimensions members at the same level, from a specified member. Despite its name LastPeriods works on any kind of dimension: Siblings are members who share a common parent. For example, a date of 14th March 2008 will have siblings of all the other dates in March the first of which is the 1st March and the last of which is 31st March. A cost centre under West Coast Branches would have a set of siblings of the other west coast branches. The FirstSibling function returns the first member that shares a parent with the named member. For example: Returns MidasHCBK. While: Returns MidasHSFI. The siblings function should return the whole set of siblings for a given member. TM1 9.0 SP2 through to 9.1.2.49 appear to give you the entire set of members at the same level (counting from the top down) rather than the set of siblings from FirstSibling through to LastSibling only. Filtering by CurrentMember, NextMember, PrevMember, Ancestor and FirstSibling This example returns the members that have an Amount value in the Test cube above 18. The Product. CurrentMember part is optional here but it makes the next example clearer. , 0), Test.(Product. CurrentMember, Posting Measures. Amount) gt 18 ) This query then modifies the previous query slightly to return members where the NEXT member in the dimension has a value above 18. In practice this is probably more useful in time dimensions. , 0), Test.(Product. CurrentMember. NextMember, Posting Measures. Amount) gt 18 ) This can then be improved to returning members where the next member is greater than their amount. , 0), Test.(Product. CurrentMember. NextMember, Posting Measures. Amount) gt Test.(Product. CurrentMember, Posting Measures. Amount) ) In addition to NextMember, PrevMember can also be used as could lags and leads. The simple, but unsupported as of 9.1.1.89, Name function allows you to filter according to the name of the member. As well as exact matches you could find exceptions, less-thans and greater-thans, bearing in mind these are alphanumeric comparisons not data values. This example returns all base members before and including the last day in January 1972. ,0), Date. CurrentMember. Name For example, this could be a useful query even a dimension not as obviously sorted as dates are: ,0), Product. CurrentMember. Name which returns all base members before MidasJ in terms of their name rather than their dimension index. Parent returns the first parent of a given member: Used with Filter you can come up with another way of doing a children of query: ,0), Date. CurrentMember. Parent. Name 1972 - January) Ancestor() can be used instead of Parent if desired. This example returns base-level product members whose first parents have a value above zero, in other words a kind of family-based suppress zeroes: a particular product might have a value of zero but if one if its siblings has a value then it will still be returned. , 0), Test.(Ancestor(Product. CurrentMember,0), Posting Measures. Amount) gt 0 ) This example filters the products based on whether they match the Amount value of MidasHCBK. , Test.(Ancestor(Product. CurrentMember,0), Posting Measures. Amount) Test.(Product. MidasHCBK, Posting Measures. Amount) ) This example uses FirstSibling to filter the list based on whether a products value does not match that products First Sibling (useful for reporting changing stock levels or employee counts over time, for example, things that are usually consistent). , 0), Test.(Ancestor(Product. CurrentMember,0), Posting Measures. Amount) Filtering by Attributes and logical operators This returns members that match a certain attribute value using the Filter function. , Product. Category Customer Lending) This example looks at multiple attribute values to return a filtered list: FILTER( , ( (Product. CategoryCustomer Lending OR Product. TypeDebit) AND (Product. Internal Deal Filtering by level, attribute and pattern are combined in the following example: ,0), Product. Internal Deal Yes), ID) Head, Tail and Subset Where TopCount and BottomCount sort the values automatically and chop the list to leave only the most extreme values, Head combined with Filter works in a similar manner but Head then returns the FIRST members of the filtered set in their original dimension order. These queries simply return the first and last members of the Product dimension as listed when you hit the All button: This returns the actual last member of the whole Product dimension according to its dimix: , ALL, RECURSIVE ), ASC)) An example of Tail returning the last member of the Customer Lending hierarchy: , ALL, RECURSIVE )) An example of Head returning the first 10 members (according to the dimension order) in the product dimension that have an Amount in the Test cube above zero. , 0), Test.(Posting Measures. Amount) gt 0 ), 10) With both Head and Tail the ,10 part can actually be omitted (or just use ,0) which will then return the first or last member. This returns the last (in terms of dimension order, not sorted values) product that had an amount gt 0 in the Test cube. , 0), Test.(Posting Measures. Amount) gt 0 )) One example of when this is useful over TopCount or BottomCount i. e. when sorting the results would be detrimental - would be to return the last day the year when a certain product was sold. Subset is closely related to Head and Tail, and can actually replicate their results, but is additionally capable of specifying a start point and a range, similar in concept to substring functions (e. g. SUBST) found in other languages, though working on a tuple of objects not strings. The equivalent of Head, 10 would be: , 1, 10) But Subset would also allow us to start partitioning the list at a point other than the start. So for example to bring in the 11th 20th member: , 11, 10) Note that asking for more members than exist in the original set will just return as many members as it can rather than an error message. Union joins two sets together, returning the members of each set, optionally retaining or dropping duplicates (default is to drop). To create a list of products that sold something both in this cube and in another (e. g. last year and this): FILTER( , 0), Test.(Posting Measures. Amount) gt 0 ) , FILTER( , 0), Test3.(Posting Measures. Amount, Entity. All Entities) gt 0 ) ) Intersect returns only members that appear in both of two sets. One example might be to show products that performed well both last year and this year, or customers that are both high volume and high margin. The default is to drop duplicates although , ALL can be added if these are required. This example returns leaf Product members that have an Amount gt 5 as well as a Count gt 5. INTERSECT( FILTER( , 0), Test.(Posting Measures. Amount) gt 5 ) , FILTER( , 0), Test.(Posting Measures. Count) gt 5 ) ) Except and Validating Dimension Hierarchies The function takes two sets as its mandatory parameters and removes those members in the first set that also exist in the second . In other words it returns only those members that are not in common between the two sets, but note that members that are unique to the second set are not included in the result set. Except is a useful function in a variety of situations, for example when selecting all the top selling products except for 1 or 2 you already know are uninteresting or irrelevant, or selecting all the cost centres with high IT costs except for the IT department. The simplest example is to have a first set of 2 members and a second set of 1 of those members: EXCEPT ( , ) Which returns MidasJCFI, the only member not in common between the two sets. For the purposes of maximum clarity in the rest of this section only, we will drop the Product reference and trust that these product names are uniquely in the Product dimension on our server. The optional extra ALL parameter allows duplicates to remain prior to the determination of the difference i. e. matching duplicates within the first set are discarded, while non-matching duplicates are retained. A simple example where there are duplicate members in the first set: EXCEPT ( , ) Returns MidasJCCO (because duplicates are discarded without ALL), while: EXCEPT ( , . ALL) Returns MidasJCCO, MidasJCCO (as ALL allows the duplicate MidasJCCO members to be retained). Note that ALL has no effect on the following query as MidasJCFI is the only member not in common between the two sets and so this is the only result either way: EXCEPT ( , ) Returns MidasJCFI. Remember, the members in the first set that also exist in the second are eliminated, hence (both instances of) MidasJCCO is eliminated So if you were to ask for EXCEPT( , ) then the final set would be without ALL and with ALL. Because matching duplicates in the first set are eliminated first (that is, duplicates in the first set that match a member in the second set), Apples (the only member in the second set that matches a pair of duplicates in the first set, is eliminated. To put the fruit down and return to our demo model we can write the equivalent query against products: EXCEPT ( , ) Returns just one MidasJCFI (the equivalent of Oranges above) while: EXCEPT ( , . ALL) Returns two instances of MidasJCFI. These results are due to the fact that, in the example with ALL, MidasJCCO is eliminated due to a matching member in set 2, while MidasJCFI is reduced to 1 instance due to the lack of ALL. MidasHDBK has no impact because it could not be subtracted from set 1 as it was not in set 1. When ALL was used in the second example, the two MidasJCCO members were still eliminated due to a match in set 2, and MidasHDBK was still irrelevant, but this time the two MidasJCFI members were left alone due to the ALL allow ing duplicates. Note: the following section does not work in v9.1 SP2, but does work in v9.0. Kilometertrykket kan variere. A particularly clever use of Except is to check a TM1 dimension for a valid structure. A simple query can return a list of members that do not eventually roll up into a particular consolidated member. This could be included in a TI process to automate the consistency checking of dimensions after an update. This example returns all the members in the dimension that do not roll up into All Products: EXCEPT ( TM1SUBSETALL( Product ), TM1DRILLDOWNMEMBER( , ALL, RECURSIVE )) Modifying this slightly makes it return base-level members that do not roll up into All Products: EXCEPT ( TM1FILTERBYLEVEL(TM1SUBSETALL( Product ), 0), TM1FILTERBYLEVEL(TM1DRILLDOWNMEMBER( , ALL, RECURSIVE ), 0)) This query returns members that have been consolidated twice or more at some point under the given consolidated member this will often mean there has been an accidental double-count. EXCEPT ( TM1DRILLDOWNMEMBER( , ALL, RECURSIVE ), TM1SUBSETALL( Product ), ALL) It will return one instance of the multi-consolidated member for each time it is consolidated greater than once i. e. if it has been consolidated 4 times then it will return 3 instances. This is due to the fact that TM1SUBSETALL( Product ) will only return one instance of a member that has been consolidated multiple times while the TM1DrilldownMember function will return all the instances. You are reminded that Dimension. Member is actually a shortcut that usually works in TM1 but because the MDX specification allows for member names to be non-unique within a dimension the full address of a member is actually Dimension. Parent1.Parent2Member. Therefore more specific references to duplicate members may be needed, for example Product. Demand Loan. MidasHCBK will address a different instance of MidasHCBK than would Product. Discount Loan. MidasHCBK. In this case, with the Except function, they are treated as if they are different member names altogether. ToggleDrillState ToggleDrillState changes the default drill state from a returned set so if the first query returns a member in a hierarchy rolled up then it will drill it down, or vice versa. Using TM1 Subsets, TM1Member and TM1SubsetToSet One of the special features of using MDX with TM1 dimensions is that existing subsets can be used within the query for defining a new subset. This can be useful in allowing a simpler building block approach and for not having to repeat the same code over and over again and having to maintain it. Used throughout this section, Report Date is an existing subset in the Date dimension containing one leaf date member and test2 is an existing 20-member subset. Note that private subsets are used in preference to public subsets when there is one of each with the same name. This can allow a public subset to return different results based on the contents of different users private subsets, though inevitably with some issues with reliability of results. To simply return the member(s) of pre-existing Date subsets: Date. Report Date Or TM1SubsetToSet(Date, Report Date) The first syntax may be shorter and more convenient but bear in mind, as per the TM1 help file, Since the same syntax (.IDENTIFIER ) is used for members and levels, a subset with the same name of a member or a level will never be instantiated. The second syntax on the other hand will happily work with any subset names even if they are named the same as a cube or dimension. To return the first member of the test2 subset: To return a valid cube reference within a more complex query: TM1Member(Date. Current Date. Item(0), 0) For example: , 1), Reconciliation.(Entity. All Entities, TM1Member(Date. Current Date. Item(0),0),Reconciliation Measures. Transaction Balance) To start with the fourth item (.Item counts from zero) in the test2 subset and then return the preceding 14 members from the whole dimension, including the fourth item: This example returns the one date in Report Date and the next 13 periods, sorted with the earliest date first a moving 2-week reporting window which just needs the Report Date subset to be maintained. This query uses another subset, Strategic Products, as a building block and finds the Top 5 members within it, even though this ranking may well have been based on different values than the original subset was built on. For example, a subset that is already defined may list the 10 highest spending customer segments in terms of year to date actuals, and you then build a new subset that works with these 10 only to find the top 5 in terms of planned marketing spend next quarter. , 5, Test.(Posting Measures. Count)), Test.(Posting Measures. Count), BDESC) Heres a bigger example using TM1member and TM1SubsetToSet functions, in addition to various others. It takes the single period in the Current Date subset and returns the last day of the two preceding months. There would be several different ways of achieving the same result. union( tm1member(tm1subsettoset(Date, Current Date).item(0),0),1) )))), tm1member(tm1subsettoset(Date, Current Date).item(0),0),1) )))) ) Username and StrToMember It returns the TM1 username (or Windows domain username depending on the security system being used for example, GERJEREMY) of the user who runs the query. Note that you may need to give all users Read access to the Clients dimension and all its elements. It is not documented in the help file as being officially supported by TM1 but it is a standard MDX feature that appears to work in v8.3. However, since 8.4.3 until 9.1.2.49 it is reported as failing to automatically update when a new user uses the subset. This can be circumvented by running a frequent TI process that uses the subset as its datasource and the following line in Prolog (Workaround reported by Steve Vincent on the Applix Forum, 2nd August 2006): DIMENSIONSORTORDER(CLIENTS, BYNAME, ASCENDING,,) With this micro-process workaround set to run every few minutes a pseudo-dynamic result is possible. An actual solution to the problem should be tested for in your version if it is 9.1 or later. To save a dynamic subset it needs to be set up on the Clients dimension choose View Control Objects in Server Explorer to see this dimension. Once you have saved the public subset (e. g. as Current User) you can turn this option off again. Clients ), USERNAME ) As an alternative to the above method, and as a way of including the current username directly in queries use the StrToMember function which converts a plain string into a valid MDX member reference. Clients. USERNAME) Either way the subset can then be referred to on Excel spreadsheets, VBA processes and, as it is simply a standard TM1 subset, in TM1 Websheets. As a non-MDX alternative v9.1.2.49 introduced a TM1User(servername) worksheet function which could be used in some circumstances. Data-based queries, Filter, Sum, Avg and Stdev Sometimes it is not adequate to simply use a single value in a query you need to consider a combination of values. It might be that this combination is only needed for one or two queries, though, so it is not desirable to calculate and store the result in the cube for all to see. Therefore it is more logical to quickly calculate the result on the fly and although this is then repeated every time the subset is used, it is still the preferred choice. The function Sum, Avg and Stdev are therefore useful for things that are only needed occasionally or by a limited number of users and means that the actual cube is thus smaller and more efficient. SUM, as it might appear, sums up a set of numbers. This allow the aggregation of members not already consolidated in the model. This example checks the Test3 cube for products whose Amounts in the on-the-fly-consolidation of 2 entities are greater than 50. , 0), SUM( , Test3.(Posting Measures. Amount) ) gt 50 ) AVG calculates the average value of a set. Note how empty (zero) cells are not included by the AVG function so the resulting average value might be higher than you expected. This example returns a list of leaf products that have an Amount value in the Test cube higher than the average Amount value of all leaf products (or rather all non-zero leaf products). , 0), (Test. Posting Measures. Amount gt AVG( , 0), Test.(Posting Measures. Amount)) ) ) The set of members that AVG works on here (AVG , 0)) can be changed to something that doesnt match the list of members being filtered earlier in the query. For example, return a list of all leaf products that are higher than the average of the leaf descendants of the Customer Lending consolidation only. , 0), (Test. Posting Measures. Amount gt AVG(,Test.(Posting Measures. Amount))) ) STDEV is the standard deviation function. It returns the average distance from each value in a set to the average of the set as a whole. In this way you can calculate how consistent or unpredictable a set of data is if all the values lie tightly around the average, or if the values vary to be extremely high and low. This example returns the outlying products whose Amount value in the Test cube is greater than the average value plus the standard deviation i. e. those products who have values that are above averagely above the average. , 0), ( Test. Posting Measures. Amount gt ( AVG( , 0), Test.(Posting Measures. Amount)) STDEV( , 0), Test.(Posting Measures. Amount) gt 0 ) ) ) ) ) Note that the AVG function automatically drops empty cells from the filtering set but STDEV does not so we have to apply our own filter. The above queries could be INTERSECTed for both sets of outliers in one subset, if required. Further reading: The MEDIAN function is also supported by TM1 and might be more appropriate than AVG (mean) in some circumstances. Using parameters in queries TM1Member will allow you to use parameterized references by using cube values as part of the query itself. For example if a UserParams cube was created with the Clients dimension (thus allowing concurrent usage by all users) which would hold various choices made by users as they used your application, then dynamic subsets could use those choices as part of their syntax, thus altering not just the thresholds for comparisons (we can see elsewhere in this document how to check if something is, say, above a certain threshold which is actually a value in another cube) but the actual thing that is queried in the first place. For example, this shows the descendants of a parent member, the name of which is held in the 2D UserParams cube at the intersection of the current username and SelectedParentDimix. TM1SUBSETALL( Product ).Item(UserParams.(StrToMember(Clients. USERNAME), UserParamMeasures. SelectedParentDimix)-1) . 0)) Below are screenshots showing the parameter cube which can be extended to hold various user-specific selections and then link them into dynamic subsets plus the other relevant screens. The Generate function applies a second set to each member of a first set, performing a union of the results. Duplicates are dropped by default but can be retained with, ALL. Although Generate doesnt really do anything unique in itself it is a very useful way of shortening what would otherwise be long, laborious and error-prone queries. In the following example the top performing child product is returned for each member of Level 1 of the hierarchy: GENERATE( , 1), TopCount(Descendants(Product. CurrentMember, 1),1,Test.(Posting Measures. Amount))),Test.(Posting Measures. Amount) gt 0 ) Count and IIF Caveat: Note that IIF is not listed in the TM1 v9.0 SP2 help file as being supported so use at your own risk. Count returns the number of items in a set but this set can be a set of members or a set of data values. The result is, obviously, a number and is often returned in reports when used in MDX queries outside of TM1. When trying to use it do define a TM1 subset it can only be used as part of the query logic and not as a result itself. Count can be wrapped around a lot of the other MDX functions and so can be used in many different scenarios. One example is to count how many children a month has and, if there are 28, doing something that is unique to February. Although dimension subsets are usually a list of meaningful items in a business model and are included within application cubes, it is actually possible to have dimensions for administrator purposes only (that are never used to build cubes) which might indicate the state of something e. g. All Passwords Set, or Reconciliation Failed and the Count function could be used to define a subset that contains one of these members, which is information for the administrator only. IIF allows you to introduce some branching logic in your queries i. e. do one thing if this is true, otherwise do something else. You could use it to apply different statistical functions to members that have certain attributes. It works quite commonly with Count to allow one thing to happen if the count of something falls below a threshold, or do something else if not. This example performs either a Top 5 or a Top 10 on all base products Amounts in the Test cube, depending on whether the number of base level Products is 10 or less at the time the query is run. , 0), IIF(Count( , 0)) lt 10, 5, 10), Test.(Posting Measures. Amount) ) This example does a TopCount of the base products based on their Amount value in the Test cube where the number of items displayed is equal to the number of cells in the Test cube whose Amount value is anything other than zero. , 0), Count( Filter( , 0), Test.(Posting Measures. Amount) These are fairly pointless examples, practically speaking, but they show the syntax. Comments allow you to explain, to yourself andor to your users, what the query is trying to achieve, how it works, who wrote it or amended, etc. Use or (without the double quotes) to end a line with a comment or to have the comment on its own line. You can also use COMMENT (again without the quotes) to insert a comment in the middle of a line. You are also able to type anything after the command. This heavily-commented example returns all the products beginning MidasJ: Comment number 1 this is another comment -- and another comment , this is yet another comment MidasJ) You seem to be able to type what you like here, but treat with caution This does not work in version 8.2.7 but does in at least 9.0 and 9.1.1. AsciiOutput can help tracking what values are being used during execution of your TI processes. Keep in mind Asciioutput limitations: . it is limited to 1024 characters per line . it can deal only with strings . so you need to apply the NumberToString() function to all numeric variables that you would like to display like var3 in the example above. . it will openclose the file at every step of the TI. PrologMetadataDataEpilog that means if you use the same filename to dump your variables in any of these, it will be overwritten by the previous tab process. Hence you should use different filenames in each tab. . use DataSourceASCIIQuoteCharacter in prolog if you want to get rid of the quotes in output. . use DatasourceASCIIThousandSeparator to remove thousand separators. Alternatively you can use ItemReject if the record you step through is rejected, it will then be dumped to the error message ItemReject(var1var2) Dynamic Formatting It is possible to preformat dynamic slices by using the Edit Element Formats button in the subset editor. However that formatting is static and will not apply to new elements of a slowly changing dimension. Also it takes a long time to loadsave when you try to apply it to more than a few dozen elements. As an example, we will demonstrate how to dynamically alternate row colors in a TM1 report for a Customer dimension. Open subset editor for the Customer dimension. Select some elements and click Edit Elements Format. the Edit Element Formats worksheet opens, just click Save as colored row this creates the DimensionFormatStyles Customer dimension and the DimensionFormats Customer cube. Now we can modify this cube with our rules. open the rules editor for the DimensionFormatsCustomer cube, add this: alternate row colors for all elements colored row, Cond1Type S: 2 colored row, Cond1Formula1 S: MOD(ROW(),2)1 colored row, Cond1InteriorColorIndex S: 34 colored row, Cond2Type S: 2 colored row, Cond2Formula1 S: MOD(ROW(),2)0 colored row, Cond2InteriorColorIndex S: 2 To create a different style:.edit one element from the Edit element format, apply the desired formatting and save. note the new values of the measures in the DimensionFormatsCustomer cube for that element. reflect these changes in the rules to apply to all elements for that style There is also another rule-based formatting article on the Applix recommended practices website. dynamic SQL queries with TI parameters It is possible to use parameters in the SQL statement of Turbo Integrator to produce dynamic ODBC queries Here is how to proceed: 1. create your TI process 2. Advanced-gtParameters Tab, insert parameter p0 3. Advanced-gtProlog Tab add the processing code to define parameter p0 example: p0 CellGetS(cube, dim1,dim2. ) 4. save the TI process 5. open Data Source, add parameter p0 in WHERE clause example select from lib. table where name p0 DO NOT CLICK ON THE VARIABLES TAB AT ANY TIME 6. run, answer keep all variables when prompted If you need numeric parameters, there is a twist numeric parameters do not work (at least for TM1 9.x) example: select from lib. customer where purchase gt p0 will fail although p0 is defined as a numeric and quotes have been removed accordingly. But fear not, there is a simple workaround 1. proceed as above up to step 5 2. Advanced-gtProlog tab, at the bottom: strp0 NumberToString(p0) 3. Data Source tab, in the SQL statement replace p0 with CAST(strp0 as int) example: select from lib. customer where purchase gt p0 becomes select from lib. customer where purchase gt CAST(strp0 as int) clicking the preview button will not show anything but the process will work as you can verify by placing an asciioutput in the Advanced-gtData tab. The CAST function is standard SQL so that should be working for any type of SQL server. renaming elements renaming elements without activating aliases Yes we can The dimension editor and dimension worksheets cannot rename elements directly, so let me introduce you to SwapAliasWithPrincipalName . create a new alias new in the Dimension, by default the new elements are identical. change all required elements to their new names in that alias. Below we pad a zero in front of all elements create a new TI with the following line in the Prolog: The third parameter needs to be zero to execute the swap. If it has any other purpose, please editleave a comment. Make sure the associated dimension worksheet is updated if there is any. This was tested successfully under v. 9.0.3 and v. 9.4. This TI function is listed in the documentation. however there is no description of its function and syntax. Click on the RTFM FAIL tag to find out some other poorly documented or simply undocumented TM1 functions. dimension elements with a quote character () require a double quote to be interpreted correctly by the rules engine example: department s - gt department s after modifying cells through rules, the consolidations of these cells wont match the new values. To reconciliate consolidations add in your rules: Total ConsolidateChildren( dimension ) scheduling chores on calendar events Scheduling chores in TM1 can be frustrating as it does not offer to run on specific dates or other types of events. The following article explains how to create chores schedules as flexible as you need them to be. From the Server Explorer . create a new process . go directly in Advanced-gtProlog . add this code: run chore every 1st day of the month If(SUBST(TODAY,7,2) Another example to run on specific days of the week: DayOfWeek Mod ( DayNo( TODAY ) 21915, 7) 0 Sunday, 1 Monday to 6 Saturday. If( DayOfWeek 0 DayOfWeek gt 4 ) ChoreQuit send emailattachments It is possible to send email alerts or reports as attachments from Turbo Integrator. This can be achieved by executing a VB script. 1. save the attached VB script on your TM1 server 2. create a TI process 3. in Epilog add the following code: SRuncmd c D:pathtoSendMail. vbs smtp. mycompany 25 116109496410912199111109112971101214699111109 1091016410912199111109112971101214699111109 Today report check it out E:TM1Reportstodaysreport. xls ExecuteCommand(SRun,0) The syntax is: SendMail. vbs server port sender destination subject body attachment so replace the fields as required to suit your setup The DOS command line is limited to 255 characters so avoid putting too much information in the body. If a field contains a blank sp ace you must enclose that field in quotes so the script gets the correct parameters code from kckang (applix forum) and rondebruin. nlcdo. htm Applix is developing a tool called Report Connect currently in the beta stage, which will integrate with TM1 and allow users to schedule automated reports emailing from the TM1 server silence is fool039s gold TM1 processes will not complain when their input source is empty. So although the process successful or chore successful message will popup, your cube will remain desperately empty. In order to solve that silent bug (or Cogglix feature), you will need to add specific code to your TI processes to test against empty sources. Here follows: initialise counter PROLOG TAB increment counter DATA TAB check counter value at the end and take appropriate action EPILOG TAB ItemReject will send the error to the msg log and the execution status box will signal a minor error. the fallacy of blb files TM1 system files with the blb extension, incorrectly referenced as cube formatting files (admin guide p.35) are actually rule formatting files for the standard rules editor (prior to 9.1). The rules editor actually displays the contents of the. blb if there is one, otherwise it defaults to the. rux. Unfortunately things can go wrong, and the. blb file gets desynchronised from the actual. rux or just go blank. As a result, what you see in the rule editor are NOT the rules attached to your cube and it becomes tricky to pinpoint any issue as the rule tracer gets confused too. A simple fix is to delete the associated. blb file in the TM1 Data folder and reopen the rules in the rule editor. Well it works only until the next time it goes desynchronised or blank. From 9.1, you can turn on the new rules editor from the tm1s. cfg: AdvancedRulesEditor T Ultimately if you really cannot do without formatting, consider using an editor with highlighting features and copypaste the rules. Attached below is a very basic lint tool whose task is to flag dangerous TM1 rules. For now it is only checking for aliases in rules. Indeed, if an alias is changed or deleted, any rule based on that alias will stop working without any warning from the system. The values will remain in place until the cube or its rules gets reloaded but you will only get a silent warning in the messages log after reloading the cube. How to proceed:.configure and execute the following TI process (to put in prolog), this will generate a list of all cubes and associated dimensions, and a dictionary of all aliases on the system in. csv format configure and execute the perl script attached below that script will load the csv files generated earlier in hash tables, scan all rules files and finally report any aliases. If the element is ambiguous because it is present in 2 different dimensions then you should write it as dimension:element instead of using aliases (e. g. write Account:71010 instead of 71010). TM1 operators logical operators: amp AND OR strings operators: concatenate string1 equals string2 disabling the DEL key to forbid users from deleting DBRW formulas Private Sub WorkbookActivate() DisableDel End Sub Private Sub WorkbookDeactivate() EnableDel End Sub Private Sub WorkbookBeforeClose(Cancel As Boolean) a MsgBox(Your data have already been saved in Tm1, you dont need to save this Excel slice, vbInformation) EnableDel ActiveWorkbook. Close False End Sub Sub DisableDel() Application. OnKey , SendSpace MsgBox Delete Key Disable End Sub Sub EnableDel() Application. OnKey MsgBox Delete Key Enable End Sub Sub SendSpace() MsgBox Delete key not allowed. Sending a space instead SendKeys TM1RECALC1. same as shift-F9, refreshes only the active worksheet TM1RECALC. same as F9, refreshes ALL open workbooks TM1REFRESH. same as Alt F9, rebuilds (dynamic spreadsheets) and refreshes ALL open workbooks TM1StartOrionWithAutomation. opens Server Explorer CUBESBROWSE. opens Server Explorer SUBDELETE: deletes a subset (if unused) ex: Application. Run(SUBDELETE, myserver:account, MySubset) TM1InsertViewControl. starts In Spreadsheet browser TWHELP. opens TM1 perspectives help TWDEFAULTS. opens TM1 Options menu TWMERUL. opens rules worksheets menu TWMEDIM. opens dimensions worksheets menu to be continued. Excel formulas references When editing a cell formula ( F2 ), you can easily toggle between relative and absolute references with the F4 key: B10 - F4-gt B10 - F4-gt B10 - F4-gt B10 zero out portions In order to zero out data points you can do it either: - from Cube Viewer select all the cells with the pointer right click: Data Spread-gtClear. or select the top left corner cell of the portion to zero out right click: Data Spread-gtRepeat set value box to 0 and tick boxes Extend right and down - from a TI process in the prolog tab: ViewZeroOut(Cube, View) setup the View to zero out then run that process This section is dedicated to various material for admins: monitoring, troubleshooting, optimising. Before running the following script you need to setup a chore with a TI process to execute that line in Prolog: Avoid using the SaveTime setting in tm1s. cfg as it could conflict with other choresprocesses trying to run at the same time here is the DOS backup script that you can schedule to backup your TM1 server Documenting TM1 section dedicated to documenting TM1 with different techniques and tools. a closer look at chores if you ever loaded a. cho file in an editor this is what you would expect: 534,8 530,yyyymmddhhmmss ------ datetime of the first run 531,dddhhmmss ------ frequency 532,p ------ number p of processes to run 13,16 6,process name 560,0 13,16 533,x ------ x1 active x0 inactive In the 9.1 series it is possible to see from the Server Explorer which chores are active from the chores menu. However this is not the case in the 9.0 series, also it is not possible to see when and how often the chores are running unless you deactivate them first and edit them. Not quite convenient to say the least. From the specs above, it is easy to set rules for a parser and deliver all that information in a simple report. So the perl script attached below is doing just that: listing all chores on your server, their datetime of execution, frequency and activity status. Procedure to follow: 1. install perl 2. save chores. pl in a folder 3. doubleclick on chores. pl 4. a window opens, enter the path to your TM1 server data folder there 5. open resulting file chores. txt created in the same folder as chores. pl a closer look at subsets if you ever loaded a. sub file (subset) in an editor this is the format you would expect: 283,2 start 11,yyyymmddhhmmss creation date 274,string name of the alias to display 18,0 275,d d number of characters of the MDX expression stored on the next line 278,0 281,b b 0 or 1 expand above trigger 270,d d number of elements in the subset followed by the list of these elements, this also represents the set of elements of if you have an MDX expression attached These. sub files are stored in cube subs folders for public subsets or user cube subs for private subsets. Often a source of discrepancy in views and reports is the use of static subsets. For example a view was created a while ago, displaying a bunch of customers, but since then new customers got added in the system and they will not appear in that view unless they are manually added to the static subset. Based on the details above, one could search for all non-MDXstatic subsets (wingrep regexp search 275, in all. sub files) and identify which might actually need to be made dynamic in order to keep up with slowly changing dimensions. Beam me up Scotty: 3D Animated TM1 Data Flow Explore the structure of your TM1 system through the Skyrails 3D interface: If you do not have flash, you can have a look at some screenshots WARNING: your eyeballs may pop out This is basically the same as the previous work with graphviz. except this time it is pushed to 3D, animated and interactive. So the visualisation engine Skyrails is developed by Ph. D. student Yose Widjaja. I only wrote the TM1 parser and associated Skyrails script to port a high level view of the TM1 Data flow into the Skyrails realm. download and unzip skyrails beta 2nd build. download and unzip TM1skyrails. zip (attachment below) in the skyraildist2 folder. in the skyraildist2 folder, doubleclick TM1skyrails. pl (you will need perl installed unless someone wants to provide a compiled. exe of the script with the PAR module).enter the path to (a copy of) your TM1 Data folder. skyrails window opens, click on the folder icon and click TM1 If you dont want to install perl, you can still enjoy a preview of the Planning Sample that comes out of the box. Just double-click on raex. exe . w, s,a, d keys to move the camera Quick legend : orange -- cube blue -- process light cyan -- file red -- ODBC source green sphere -- probably reference to an object that does not exists (anymore) green edge: intercube rule flow red edge: process (CellGetCellPut) flow Changelog: 1.1 a few mouse gestures added (right click on a node then follow instructions) to get planar (like graphviz) and spherical representations. 1.2 - edges color coded, see legend above - animated arrows - gestures to display different flows (no flowrules onlyprocesses onlyall flow) Dimensions updates mapping When faced with a large undocumented TM1 server, it might become hard to see how dimensions are being updated. The following perlgraphviz script creates a graph to display which processes are updating dimensions. That script dimflow. pl below is looking for functions updating dimensions (DimensionElementInsert, DimensionCreate. ) in. pro files in the TM1 datafolder and maps it all together. Unfortunately it does not take into account manual editing of dimensions. This is the result: The above screenshot is probably a good example of why such map can be useful: you can see immediately that several processes are updating the same dimensions. It might be necessary to have several processes feeding a dimension, though it will be good to review these processes to make sure they are not redundant or damaging each others effects. Procedure to follow: 1. install perl and graphviz 2. download the script below and rename it to. pl extension 3. doubleclick on it 4. enter the path to your TM1 Data folder (servernamedatafolder) 5. This will create 2 files dim. dot and dim. gif in the same folder as the perl script 6. Open dim. gif with any browser picture editor graphing TM1 data flow Attached is the new version of a little parser in perl (free) that will create a text file for graphviz (free too) out of your. pro and. rux files and then generate a graph of the data flow in your TM1 server. (the image has been cropped and scaled down for display, the original image is actually readable) legend ellipses cubes, rectangles processes red cellget, blue cellput, green inter-cube rule Procedure to follow : 1. install perl and graphviz 2. put the genflow perl script in any folder, make sure it has the. pl extension (not txt) 3. doubleclick on it 4. Enter the path to your TM1 Data folder such as: servernamedatafolder where servernamedatafolder is the full file path to your TM1 data folder 5. Hit return and wait until the window disappears This creates 2 files: flow. dot and flow. gif in the same folder as the perl script 6. Open flow. gif in any browser or picture editor Changelog 1.3 :.CellPut parsing fix. cubesprocesses names displayed as is 1.4 :.display import view names along the edges. display zeroout views. sources differentiated by shape This is still quite experimental but this could become useful to view at a glance high-level interactions between your cubes. indexing subsets Maintaining subsets on your server might be problematic. For example you wanted to delete an old subset that you found out to be incorrect and your server replied this: This is not quite helpful, as it does not say which views are affected and need to be corrected. Worse is that, as Admin, you can delete any public subset as long as it is not being used in a public view. If it is used in a users private view, it will be deleted anyway and that private view might become invalid or just wont load. In order to remediate to these issues, I wrote a little perl script, attached below, that will:.index all your subsets, including users subsets..display all unused subsets (i. e. not attached to any existing views) From the index, you can find out right away in which views a given subset is used. I suppose the same could be achieved through the TM1 API though you would have to log as every user in turn in order to get a full index of all subsets. Run from a DOS shell: perl indexsubset. pl pathtoTM1server gt mysubsets. txt processes history On a large undocumented and mature TM1 server you might find yourself with a lot of processes and you wonder how many of them are still in use or the last time they got run. The following script answers these questions for you. One could take a look at the creationmodification time of the processes in the TM1 Data folder however you would have to sit through pages of the tms1msg. log to get the history of a given process which is what the script below does. Procedure to follow for TM1 9.0 or 8.x 1. install perl (free) 2. save loganalysis. pl. txt in a folder as loganalysis. pl 3. stop your TM1 service (necessary to beat the windows lock on tm1smsg. log) 4. copy the tm1smsg. log into the folder where loganalysis. pl is 5. start your TM1 service 6. double click loganalysis. pl Procedure to follow for TM1 9.1 1. install perl (free) 2. save loganalysis. pl. txt in a folder as loganalysis. pl 3. copy the tm1server. log into the folder where loganalysis. pl is 4. double click loganalysis. pl That should display the newly created processes. txt in notepad and that should look like the following: First, all processes sorted by name and the last run time, user and how many times it ran. Second, all processes sorted by last run time, user and how many times it ran. I do not know what these BrandAnalysisUpdate or LoadDelivery processes do but I guess nobody is going to miss them. The case against single children I came across hierarchies holding single children. While creating a consolidation over only 1 element might make sense in some hierarchies, some people just use consolidations as an alternative to aliases. Either they just dont know they exist or they come from an age when TM1 did not have aliases yet. The following process will help you identify all the single child elements in your system. This effectively loops through all elements of all dimensions of your system, so this could be reused to carry out other checks. TM1 Documenter (a Documenting tool) Just FYR, New Version of TM1 Documenter Version2.5 (A documenting tool) has been released. I believe, it will very useful to TM1 Consultants amp Developers, and the Organisations as well. Myself being a TM1 Consultant, know the pain-areas of a Consultant. Having a Java background I developed this software. Usually documentation task takes about 20-40 days, thereby blocking a valuable resource (TM1 Developer) for such less-important task. By using this software, the documentation task will be completed in just few clicks. Moreover during development or support task, when the model becomes huge or complex sometimes people lose the exact data flow of the model (as Rule sets are difficult to understand). Some times a developer, by mistake, deletes an object (be it a cube, dimension, element, or a subset) that is providing is data to some other object. TM1 do not disallow to do so, but the model goes on a toss. Here the Object Dependency Checker comes to rescue. Software Summary: It has in all 2 Main Modules. I have introduced one new module in it, i. e. Object Dependency Checker Following are the details: Documenter Module: 1. Detailed information summary of the cubes. 2. Cube Sizing. 3. Views info (optional). 4. Rules info (optional). 5. Detailed information summary of the Dimensions. 6. Subsets info (optional). 7. Export TI Process list. 8. Export Dimension Attributes 9. Export Element Attributes 10.Output html file with index on left side amp Object details in center pane that make easy to navigate the objects. Object Dependency Checker Module: 1. Cube Dependency: Select a cube amp see all the other cubes depending on this. (i. e. view all cubes that are been sourced by this cube) 2. Dimension Dependency: Select a DIM amp see all cubes amp rules using this DIM. It also displays if this Dim is being used as Picklist anywhere. 3. Element Dependency: Select an Element from Dim amp see all cubes using. specifically, this element Data. 4. Subset Dependency: Select a Subset amp see all the views using this subset. It also displays if this Subset is being used as Picklist anywhere. 5. Cube-Element Dependency: This is detailed level element dependency. This module checks if the data of selected element of the selected cube is being used anywhere. 6. Export option available for all above sub-modules. Your suggestions amp queries welcome. Please provide me feedback of this tool. dynamic tm1p. ini and homepages in Excel Pointing all your users to a single TM1 Admin host is convenient but not flexible if you manage several TM1 services. Each TM1 service might need different settings and you do not necessarily want users to be able to see the development or test services for example. Attached below is an addin that logs the users on a predefined server and settings as shown on that graph: With such a setup, you can basculate your users from one server to the other without having to tinker the tm1p. ini files on every single desktop. This solution probably offers the most flexibility and maintainability as you could add conditional statements to point different groups of users to different serverssettings and even manage and retrieve these settings from a cube through the API. This addin also includes: - previous code like the TM1 freeze button - it loads automatically an excel spreadsheet named after the user so each user can customise it with their reportslinks for a faster access to their data. The TM1 macro OPTSET . used in the. xla below, can preconfigure the tm1p. ini with a lot more values. The official TM1 Help does not reference all the available values though. Here is a more complete list, you can actually change all the parameters displayed in the Server Explorer File-gtOptions with OPTSET: AdminHost DataBaseDirectory IntegratedLogin ConnectLocalAtStartup InProcessLocalServer TM1PostScriptPrinter HttpProxyServerHost HttpProxyServerPort UseHttpProxyServer HttpConnectorUrl UseHttpConnector AnsiFiles GenDBRW NoChangeMessage DimensionDownloadMaxSize this also applies to OPTGET WARNING: Make sure that all hosts in the AdminHost line are up and working otherwise ArchitectPerspectives will hang for a couple of seconds while trying to connect to these hosts. free utilities If you are stuck with a Windows operating system, you might need some tools for basic needs, these are all free: for bruteregexp search in. pro and. rux files: WinGrep finding out changes in different versions of files: WinMerge or the PSPad editor Windirstat is quite a useful visualisation tool to clean up your drivesservers. A picture is worth a thousand words, take a look at the screenshot . It was first developed for KDE: kdirstat SnagIt can capture scrolling long web pages, extract text from windows, annotate images and more. Read the detailed SnagIt review . download here free license subscription here (thanks Eric ) A few more tools from lifehacker And finally, not a desktop tool per se, but quite useful to share files quick, easy and secure: drop. io How to monitor TM1 connections using a Java application download --gt TM1Shell. rar . This program is a shell that allows you to connect to a TM1 server running. Then it starts two threads : - One write in a logfile the log activity on the server and save it - The second allows you to have a description of dimensions, cubes, elements. and to export it into an Excel File. Use cmd to draw a list of available commands. The project is under construction, any suggestions welcome. A new version is coming out soon. If you have any questions, you can contact me. 1081179997115461061111051031109711712064103109971051084699111109 Locking and updating locked cubes Locking cubes is a good way to insure your (meta)data is not tampered with. Right click on the cube you wish to lock, then select Security - gt Lock . This now protects the cube contents from TI process and (un)intentional admins changes. However, this makes updating your (meta)data more time consuming, as you need to remove the lock prior to updating the cube. Hopefully, the function CubeLockOverride allows you to automate that step. The following TI code demonstrates this,.lock a cube. copypaste the code in a TI Prolog tab. change the parameters to fit your system. execute: Note: CubeLockOverride is in the reserved words listed in the TM1 manual but its function seems to be only documented in the 8.4.5 releases notes . This works from 8.4.5 to the most recent 9.x series managing the licences limit One day you might face or already faced the problem of too many licences being in use and as a result additional users cannot log in. Also on a default setup, nothing stops users from opening several tm1webperspectives sessions and reach the limit of licenses. So in order to prevent that: open the cube ClientProperties, change all users MaximumPorts to 1.in your tm1s. cfg add that line, it will timeout all idle connections after 1 hour: IdleConnectionTimeOutSeconds 3600 To see whos logged on :.use tm1top or. open the cube ClientProperties all logged users have the STATUS measure set to ACTIVE or. in server manager (rightclick server icon), click Select clients. to get the list To kick some users without taking the server down: in server explorer right click on your server icon - gt Server Manager select disconnect clients and Select clients. then OK and they are gone. Unfortunately there is still no workaround for the admin to log in when users take all the slots allowed. monitor rules and processes Changing a rule or process in TM1 does not show up in the logs. That is fine as long as you are the only Power User able to tinker with these objects. Unfortunately, it can get out of hand pretty quickly as more power users join the party and make changes that might impact other departments data. So here goes a simple way to report changes. The idea is to compare the current files on the production server with a backup from the previous day. You will need:.access to the live TM1 Data Folder. access to the last daily backup. a VB script to email results you can find one there. diff, egrep and unix2dos, you can extract these from that zip package and efghsoftwareunix2dos. exe or download directly the attachments below (GNU license) Dump these files in D:TM1DATABIN for example, or some path accessible to the TM1 server. In the same folder create a diff. bat file, replace all the TM1DATA paths to your configuration: Now you can set a TM1 process with the following line to run diff. bat and schedule it from a chore. Best is to run the process at close of business, just before creating the backup of the day. And you should start receiving emails like these: In this case we can see that the rules from the Productivity cube have changed today. monitoring chores by email Using the script in the Send Email Attachments article, it is possible to set it up to automatically email the Admin when a process in a chore fails. Here is how to proceed: 1. setup admin email process First we create a process to add an email field to the ClientProperties cube and add an email to forward to the Admin. 1.1 create a new process ---- AdvancedParameters Tab, insert this parameter: AdminEmail String Admin Email --- AdvancedProlog tab if(DIMIX(ClientProperties, Email) 0) DimensionElementInsert(ClientProperties,,Email, S) Endif --- AdvancedEpilog tab CellPutS(AdminEmail, ClientProperties, Admin, Email) 1.2 Save and Run 2. create monitor process ---- AdvancedProlog tab MailServer smtp. mycompany LogDir tm1servereTM1DataLog ScriptDir E:TM1Data If(ProcessReturnCode mind that future TM1 versions might use a different format for. cho files and that might break this script If(Tag 6) MailServer mail. myserver LogDir serverfTM1DatamyTM1Log get the process names from the deactivated chore ProcessMeasure NumericGlobalVariable( ProcessReturnCode) StringGlobalVariable(Status) The code only differs from the first method when the process returns a ChoreQuit exit. Because we will be running the chore Daily Update from another chore, the ChoreQuit will not apply to the later, so we need to specify it e xplicitly to respect the flow and stop at the same point. 2. create chore ProcessCheck just add the process above and set it to the same frequency and time as the Daily Update chore that you want to monitor 3. deactivate Daily Update since the ProcessCheck chore will run the Daily Update chore there is no need to execute Daily Update another time monitoring users logins a quick way to monitor users loginlogout on your system is to log the STATUS value (i. e. ACTIVE or blank) from the ClientProperties cube. View-gtDisplay Control Objects Cubes - rightclick - Security Assignments browse down to the ClientProperties cube and make sure the Logging box is checked tm1server - rightclick - View Transaction Log Select Cubes: ClientProperties All the transactions are stored in the tm1s. log file, however if you are on a TM1 version prior to version 9.1 and hosted on a Windows server, the file will be locked. A Save Data will close the log file and add a timestamp to its name, so you can start playing with it. This trick does not work in TM1 9.1SP3 as it does not update the STATUS value. Oops I did it again OH NOOOEES A l user just ran that hazardous process or spreading on the production server and as a result trashed loads of data on your beloved server. You cannot afford to take the server down to get yesterdays backup and they need the data now. Fear not, the transaction log is here to save the day. in server explorer, right click on server-gtView Transaction Log. narrow the query as much as you can to the timeclientcubemeasures that you are after Mind the date is in north-american format mmddyyyy. Edit-gtSelect All. Edit-gtBack Out will rollback the selected entries Alternatively, you could get the last backup of the corresponding. cub of the damaged cube. in server explorer: right-click-gtunload cube. overwrite the. cub with the backed up. cub. reload the cube from server explorer by opening any view from it Out of Memory You will get the dreaded message Out of Memory if your TM1 server reaches beyond 2 GB of RAM. On top of adding more RAM, you also need to add the 3GB flag in C:boot. ini to extend the available space of the TM1 server from 2 to 3 GB, if you ever need more then you will have to look for a 64bit server. boot loader timeout10 defaultmulti(0)disk(0)rdisk(0)partition(1)WINDOWS operating systems multi(0)disk(0)rdisk(0)partition(1)WINDOWSMicrosoft Windows 2000 Advanced Server fastdetect multi(0)disk(0)rdisk(0)partition(1)WINDOWSrestore mode safeboot:dsrepair sos boot loader timeout10 defaultmulti(0)disk(0)rdisk(0)partition(1)WINDOWS operating systems multi(0)disk(0)rdisk(0)partition(1)WINDOWSMicrosoft Windows 2000 Advanced Server fastdetect 3GB multi(0)disk(0)rdisk(0)partition(1)WINDOWSrestore mode safeboot:dsrepair sos This trick will work only for Windows 2000 Advanced or Datacenter Server and Windows 2003 Enterprise or Datacenter Edition. It is also recommended that you restart your TM1 service daily in order to free up the RAM used from TM1 operations during the day. After a RAM upgrade to 3 GB, you might still get the Out of Memory message when you are importing a lot of data at once although your server itself is actually using only 2.5 GB. A way to circumvent that limit is to breakdown the import of data: use SaveDataAll to commit the changes on disk after some import then use CubeUnload( cube ) of the cube that you just updated. That will free some space that can be used for importing further data for other cubes and that space will be used back to load that cube later once someone opens a view from that cube . pushing data from an iSeries to TM1 TM1 chore scheduling is frequency based, i. e. it will run and try to pull data after a predefined period of time regardless of the availability of the data at the source. Unfortunately it can be a hit or miss and it can even become a maintenance issue when Daylight Saving Time come into play. Ideally you would need to import or get the data pushed to TM1 as soon as it is available. The following article shows one way of achieving that goal with an iSeries as the source. prerequesites on the TM1 server:.Mike Cowie s TIExecute or download it from the attachment below. iSeries Client Access components (iSeries Access for Windows Remote Command service) Procedure to follow 1. drop TM1ChoreExecute, TM1ProcessExecute, associated files and the 32bit TM1 API dlls in a folder on the TM1 server (see readme in the zip for details) 2. start iSeries Access for Windows Remote Command on the TM1 server, set as automatic and select a user that can execute the TM1ChoreExecute 3. in client access setup: set remote incoming command run as system generic security 3. on your iSeries, add the following command after all your queriesextracts: RUNRMTCMD CMD(start D:pathtoTM1ChoreExecute AdminServer TM1Server UserID Password ChoreName) RMTLOCNAME(10.xx. x.xx IP) WAITTIME(10) 10.xx. x.xx IP of your TM1 server D:pathto path where the TM1ChoreExecute is stored AdminServer name of machine running the Admin Server service on your network. TM1Server visible name of your TM1 Server (not the machine name of the machine running TM1. UserID TM1 user ID with credentials to execute the chore. Password TM1 user IDs password to the TM1 Server. ChoreName name of requested chore to be run to load data from the iSeries. You should consider setting a userpass to restrict access to the iSeries remote service and avoid abuse. But ideally an equivalent of TM1ChoreExecute should be compiled and executed directly from the iSeries. store any files in the Applications folder The Applications folder is great but limited to views and xls files, well not anymore ). The following explains how to make available just any file in your Applications folders. 1. create a file called myfile. blob in Applications on your TM1 server it should contain the following 3 lines: ENTRYNAMEtutorial. pdf ENTRYTYPEblob ENTRYREFERENCETM:blobpublic. Externalstutorial. pdf 2. place your file, tutorial. pdf in this case, in Externals or whatever path you defined in ENTRYREFERENCE 3. restart your TM1 service ENTRYNAME is the name that will be displayed in Server Explorer. ENTRYREFERENCE is the path to your actual file. The file does not need to be in the folder Externals but the server must be able to access it avoid large files, there is no sign to tell you to wait while loading, impatient users might click several times on the file and unvoluntarily flood the server or themselves. add the extension in ENTRYNAME to avoid confusion, although it is not a. xls file, it will be displayed with an XLS icon. TM1 services on the command line removing a TM1 service in a DOS shell: go to the bin folder where TM1 is installed then: tm1sd - remove TM1 Service where TM1 Service is the name of an existing TM1 service or: sc delete TM1 Service removing the TM1 Admin services sc delete tm1admsdx64 sc delete TM1ExcelService installing a TM1 service in a DOS shell: go to the bin folder where TM1 is installed then: tm1sd - install TM1 Service DIRCONFIG where DIRCONFIG is the absolute path where the tm1s. cfg of your TM1 Service is stored manually starting a TM1 service from a DOS shell in the bin folder of the TM1 installation: tm1s - z DIRCONFIG remotely start a TM1 service netsvc start TM1server TM1 service sc TM1server start TM1 service remotely stop a TM1 service netsvc stop TM1server TM1 service sc TM1server stop TM1 service TM1 sudoku Beyond the purely ludic and mathematical aspects of sudoku. this code demonstrates how to set up dimensions, cubes, views, cell formating, security at elements and cells levels all through Turbo Integrator in just one process. Thanks to this application, you can prove your TM1 ROI. none of your company employees will ever need to shell out 1 for their daily sudoku from the Times. Alternatively, you could move your users to a probation group before they start their shift. It is only by completing successfully the sudoku that the users will be moved back to their original group. This way you can insure your company employees are mentally fit to carry out changes to the budget, especially after last evening ethylic abuses down the pub. Of course it exists many sudoku available for Excel, this is one is to be played primarily from the cube viewer, but you could also slice the view and play it from Excel too. How to install:.Save the processes in your TM1 folder and reload your server or copy the code directly to new turbo integrator processes..Execute Create Sudoku. That creates the cube, default view and new puzzle in less than a second. sudoku The user can input numbers in the input grid only where there are zeroes. The solution grid cannot be read by default..Execute Check Sudoku to verify your input grid matches the solution. If you are logged under an admin account, you will not see any cells locked, you need to be under the group defined in the process to see the cells properly locked. You might want to change the default group allowed to play and the number of initial pairs that are blanked in order to increase difficulty. The algorithm provided to generate the sudoku could be quickly modified to solve by brute force any sudoku. Provided the sudoku grid is valid, it will find the solution, however some sudokus with too many empty cells will have more than one solution. This post is published on April 1st, but I can assure you the code is not an Aprils fool, it works and it was tested on TM1 9.0.3. realtime monitoring of your TM1 server, pretty much like the GNU top command. It is bundled with TM1 only from version 9.1. You might have to ask your support contact to get it or get Ben Hills TM1Top below. dump the files in a folder . edit tm1top. ini, replace myserver and myadminhost with your setup servername myserver adminhost myadminhost refresh5 logfile C:tm1top. log logperiod0 logappendT run the tm1top. exe Commands: X exit W write display to a file H help V verifylogin to allow cancelling jobs C cancel threads, you must first login to use that command Keep in mind all it does is to insert a ProcessQuit command in the chosen thread. Hence it will not work if the user is calculating a large view or a TI is stuck in a loop where it never reads the next data record, as the quit command is entered for the next data line rather than the next line of code. Then your only option becomes to terminate the users connection with the server manager or API. (thanks Steve Vincent). Ben Kyro Hill did a great job developing a very convenient GUI TM1Top. You can find it attached below. tm1web customizer The tm1web customizer will allow you to change the default logos and color schemes of tm1web from a graphical interface. That is trying to make it more convenient to customize your tm1web without having to dig in the code. It can be found here: ftp:ftp. applixpubGruenesTM1WebAppCustomizer. zip However note that it is configured to work with 9SP1. TM1Web vs TM1 Server Explorer DeathMatch I have a strong dislike for TM1Web and here is why. Quick Traffic Analysis comparison On the recommended practices site from Applix, the following article TM1 Deployment Options and Network Bandwidth Considerations claims that TM1Web is more suited to low bandwidth networks. O RLY So I decided to give it a go with Wireshark. great network analysis tool, used to be known as Ethereal. I do 2 runs, one with Server Explorer (direct over TCPIP no HTTP), the other with TM1Web The analysis takes place between a Windows XP client and Windows 2000 Advanced server hosting TM1. Both are using TM1 9.0SP2, the only customisation brought to TM1Web was to remove the top left TM1 logo so that should have only a neglectable effect on the statistics. In each case:.close all connections to TM1 server. on the client host, Wireshark capture filter set to log only packets to and from the TM1 Server Capture - gt Options set Interface to the ethernet card in use set capture filter to that string: host TM1 server IP if the TM1 server has the IP 192.168.0.10 then the capture filter must be: host 192.168.0.10.check the capture baseline is flat to be sure there will be no other traffic. start logging packets just before opening the view. open a decent view, 412 rows x 8 columns. scroll through all the rows until bottom is reached. stop logging Results (in Wireshark, Statistics - gt Summary): 978 kBytes went through the network with TM1Web 150 kBytes went through the network with server explorercube viewer So much for saving bandwidth with TM1Web, it is actually consuming at least 5 times more traffic than Server Explorer. If I get more time I will look in the packets to see why there is so much overhead with TM1Web, my initial guess is this is caused by the additional HTTP protocol layer. This time I tried with another view, 7 dimensions, 415 rows by 9 columns similar results: 947 kB for TM1 Web 147 kB for cube viewer And I pushed the analysis a bit further. Wireshark Menu: Statistics - gt Protocol Hierarchy As you can see HTTP takes up only 8.7 of the total traffic, but that is already 47 kBytes just to embed data on the wire, cube viewer would have already transfered 30 of the view in the same amount of bytes Now lets breakdown the conversation between the client and server. From the Wireshark menu: Statistics - gt Conversation List - gt TCP The popup window now displays the TCP conversations by size, the fattest are at the bottom. So lets see what is causing all that traffic. Right click the last one: Apply As Filter-gt Selected - gt A--B then from the Wireshark menu: Analyze - gt Follow TCP Stream You can now see what makes up all that traffic, and the culprit is. OMG ALL THAT JUNK HTML CODE and that is sent every single time you press the little arrows to change the page on a view. You would think TM1Web would somehow send only the actual data and leave the formatting processing to the client (AJAX) to spare the network and boost response times, well it is just not the case. Future Developments attached to this page is a presentation from David Corbett made last October about the roadmap for current and future developments in TM1
No comments:
Post a Comment