Il sito non è attualmente disponibile il sito che si desidera visitare non è attualmente disponibile. Ciò può essere dovuto a che il sito è in fase di sviluppo, che la manutenzione è in corso o che il sito è chiuso a causa di qualche altro motivo. proprietario del dominio, si prega di vedere il tuo indirizzo di contatto (e-mail) o contatto supportloopia per ulteriori informazioni. Webbplatsen gr ej att n fr tillfllet Webbplatsen du vill Beska r fr tillfllet inte tillgnglig. Detta kan bero p att sidan R sotto Utveckling, att underhll gr eller att sidan av annan anledning r stngd. Domngare, se din kontaktadress (e-post) Eller KONTAKTA supportloopia. se fr mer informazioni. Nettsiden kan ikke ns per yeblikket Nettsiden du vil beske er per yeblikket ikke tilgjengelig. Dette kan komme av er a siden sotto Utvikling, Vedlikehold utfres Eller a siden av en anledning er stengt. Domeneier, se din kontaktadresse (e-post) Eller Kontakt supportloopia. no per mer Informasjon. Questo messaggio è anche disponibile in una parte di Visma middot Tel.: 46 82 22 21-12 middot Fax: 46 82 33 21-12 middot E-mail: infoloopia En del av Vismakoncernen middot Tel: 021-12 82 22 middot Fax: 021-12 82 33 middot E-post: infoloopia. seLearn su GT come calcolare fino ad oggi, somma in Cognos titolo risultato 10 ricerca: Cognos: Cognos BI 10: calcolo tra 2 query diverse. Ricerca descrizione risultato: 2 Ottobre 2013. Ho Q1 con tavolo CT1 per ottenere corrente reveue1 periodo (sintesi). Il periodo si basa sul valore di 2 pronta data selezionata (mese), ad esempio: iniziare. Ricerca titolo risultato: Cognos: Cognos BI 10: rolling 12 mesi di totale per ogni mese di. Ricerca descrizione risultato: 24 Luglio 2014. Display: Conversazioni per data. Ho bisogno di calcolare rotolamento 12 mesi conteggio per ogni mese elencato. In primo luogo, sarebbe utile sapere quale versione di Cognos 10 che si sta utilizzando, quali i dati quotdimensional esatte. Questo è noto come misura Time - Stato - non può essere aggiunto in più periodi di tempo. Ricerca titolo risultato: Utilizzando la funzione PeriodsToDate all'interno di IBM Cognos 10 Relazione descrizione Risultato della ricerca: 13 maggio 2011. Una descrizione di come la funzione PeriodsToDate dimensionale può essere utilizzato al di fuori di una dimensione temporale per calcolare un totale parziale contro su. Ricerca titolo risultato: Cognos: Cognos BI. 10: tasso di varianza non aggregando in modo corretto nella descrizione Risultato della ricerca: 27 agosto 2013. Display: Conversazioni per data. 1-6 di 6. Sto cercando di calcolare il prezzo, volume e varianza Mix tra i 2 periodi. Tuttavia, la somma dei livelli inferiori non uguale livello uppper. La mia domanda è come faccio a calcolare solo i livelli di foglia e hanno Cognos BI 10.1 aggregato il titolo superiore risultato livelli ricerca: Cognos: Cognos 8: Analisi delle vendite per il giorno precedente, ultimi 5 giorni e. Ricerca descrizione risultato: Mar 18, 2011. 10 risposte Ultimo messaggio - 18 Dicembre 2013 da Arhan. Poi si sarebbe creare calcoli individuali per ogni valore di misura periodo. Penso che ci potrebbe essere un FirstLast di calcolo data mese sul lato Cognos che. Vorrei commentare Phil39s risposta che, dal momento che si sommerà questi importo delle vendite, non utilizzare. Ricerca titolo risultato: Cognos: Cognos 8: le funzioni di movimento-media totale MOVING - dare. Ricerca descrizione risultato: 7 Gennaio 2009. Display: Conversazioni per data. Here39s come il file della guida descrive la funzione totale MOVING-. Aggiornato il 7 gennaio 2009 a 1: 10 PM di SystemAdmin. Ho seguito la formula che avete condiviso per un totale MOVING - ma what39s strana è. Ricerca titolo risultato: Cognos: Cognos BI 10: Per ottenere dinamicamente mesi in chiusura trimestre. Ricerca descrizione risultato: 20 Dicembre 2012. L'ambiente è Cognos 8.4.1 rapporto di studio contro MSAS cubo. alcuni mesi (3,6,9 amp 12) solo dal livello del mese all'interno Data dimensione. Come posso automatizzare creare un calcolo che automaticamente. Re: Per ottenere dinamicamente mesi nel periodo di chiusura trimestre. totale (currentMeasure entro set generare (.)). Ricerca titolo risultato: Cognos: Cognos 8: come gruppo campo della data per 1 settimana o 2 settimane, ecc Ricerca descrizione risultato: 11 maggio 2011. ma quello che voglio è quello di raggruppare la data di ispezione per settimana e riassumere il qty come di seguito. data di ispezione Quantità 2011-01-03 4 (settimana 1) 2011-01- 10 3 (Settimana 2). cercando di calcolare tutti i periodi richiesti in base alle date di tuo. Ricerca titolo risultato: Cognos: Cognos Express: Reporter. Come fare somma cumulativa. Ricerca descrizione risultato: 6 giugno 2012. Cognos Express. Display: Conversazioni per data. Funziona bene per mostrare le entrate per società e per mese, ma voglio una somma culative. Ricerca titolo risultato: Uso delle relative Ora Categorie: IBM Cognos pratiche comprovate. Ricerca descrizione risultato: 30 agosto 2011. Applicabilità. La guida è applicabile alle versioni di IBM Cognos Report Studio 8.4 e superiori. Figura 10. Affettatrice editor di espressione membro con l'espressione. Figura 10. membro affettatrice. Perché questo Al fine di calcolare il 39PreviousMonth39 viene utilizzata la funzione PrevMember. editoria data 08302011. titolo Risultato della ricerca: Cognos: Cognos BI 10: Inserimento di un totale da una query diversa. Ricerca descrizione risultato: 21 Gennaio 2013. Cognos BI 10. e perdita di prodotto rispetto ad una dimensione di laminazione 12 data. Un esempio potrebbe essere quello di calcolare tutti i prodotti che hanno avuto una crescita tra l'ultimo precedente. Guardo ogni prodotto, se ha una crescita tra i due periodi. aggiungerlo. Come faccio ad avere il totale di questa query in un titolo a campi incrociati Risultato della ricerca: Cognos: Cognos 8: Recupero dati tra due date nella relazione. Ricerca descrizione risultato: 5 gennaio 2011. Ho una richiesta Periodo (scatola List) che terrà la data in formato gen - 10. Un elemento di dati per calcolare l'Esercizio data di inizio (aprile-YY). Ricerca titolo risultato: Cognos: Cognos 8: CY e PY misure non funziona correttamente. Ricerca descrizione risultato: 2 agosto, 2011. Cognos 8. 10 10.1.1 10.2 10.2.1 8 8.3 8.4 8.4.1 un attivo e. Il CY vs PY Misura aggiunge gli anni insieme invece di sottrazione del PY da. Fino ad oggi, in totale (raggruppati) o N tempo totale di esecuzione (raggruppati). Ricerca titolo risultato: ambiente dinamico Data: IBM Business Analytics pratiche comprovate. Ricerca descrizione risultato: Nov 14, 2012. Impostazione dinamica valori di data prompt all'interno di IBM Cognos Report Studio 10.2. Prodotto (s): IBM Cognos 10 Area di interesse: Reporting. Aggiunta del JavaScript per impostare i valori prompt dinamico. Snook (tsnook) Opher Banarie (OpherB) Ciao - lo ragazzi a capire la soluzione per il ripristino problema Ricerca titolo pronta risultato: Cognos 8 Funzioni dimensionali - IBM descrizione del risultato: prodotti software Cognos e servizi correlati che avete in licenza o acquistati da Cognos. BottomSum. Funzioni dimensionali. 10. Cognos informazioni proprietarie. la misura delle Entrate è l'impostazione predefinita dalla dimensione Measures. Restituisce un membro da un periodo diverso nella stessa ricettario relative. The TM1 Si tratta di uno sforzo di scrittura collaborativa: gli utenti possono collaborare scrivendo gli articoli del libro, posizionando gli articoli nel giusto ordine, e la revisione o la modifica di articoli scritti in precedenza. Così, quando si dispone di alcune informazioni da condividere o quando si legge un articolo del libro e che non ha ancora piace, o se pensate che un certo articolo avrebbe potuto essere scritto meglio, si può fare qualcosa al riguardo. Gli articoli sono suddivisi nelle seguenti sezioni: larghezza prospettive Server Explorer suggerimenti Visualizzatore cubi TM1Web Gestione pianificazione cubo spettatori colonne quando si ha una vista con un sacco di dimensioni visualizzabili, accade che la maggior parte della tenuta dello schermo viene consumata dalle colonne dimensioni e si ritrovi scorrimento molto a destra ea sinistra per leggere i punti dati. Per facilitare questo problema: Scelga gli alias più brevi per tutte le dimensioni da l'editor sottoinsieme. choose nomi di sottoinsieme più breve possibile. uncheck le opzioni della riga di intestazione gtExpand ExcelPerspectives suggerimenti per ridurre la probabilità di crash il vostro Excel, disabilitare Calcolo automatico. Vai alla scheda Calcolo Strumenti-gtOptions-gt quindi fare clic sul pulsante manuale È possibile utilizzare: F9 per aggiornare manualmente tutte le cartelle di lavoro aperte Maiusc F9 per aggiornare solo la corrente del foglio di lavoro F2 entrare (cella edit) per aggiornare solo 1 cella in TM1 9.4. 1, i fogli di calcolo ricalcola automaticamente quando si apre una cartella di lavoro, o la modifica di un SUBNM nonostante calc automatica è disattivata. Dal menu principale di Excel, fare clic su Inserisci-nome-Definire. Nella finestra di dialogo Definisci nome, ingresso TM1REBUILDOPTION. Set il valore nella casella Riferire a 0 e fare clic su OK. . evitare più sezioni dinamiche su più fogli in una cartella di lavoro, se potete, altrimenti Excel diventa molto instabile e alcuni riferimenti potrebbe avere incasinato. se hai trovato l') directory dei dati e10 non trovato errore durante il caricamento di prospettive, è necessario definire la directory dei dati del server locale, anche se non è possibile eseguire uno. Vai a File-gtOptions e inserire una cartella valida nella casella Directory dati. se quella casella è grigio allora avete bisogno di modificare manualmente la variabile nelle Tm1p. ini memorizzati sul PC. In alternativa, è possibile modificare l'impostazione direttamente da Excel con il seguente codice VBA: Application. Run (OPTSET, DatabaseDirectory, C: somepath) Quale versione TM1 sei su Pubblica viste editrici agli utenti di visualizzare è ancora lontano da un processo rapido e semplice in TM1. In primo luogo, l'amministratore non può vedere di vista di altri utenti. In secondo luogo, gli utenti non possono pubblicare le proprie opinioni stessi. Così viste pubblicazione richiedono sempre un intervento diretto da admin, così non più :) if ((ViewExists (cubo, Vista) 0) ((ViewExists (cubo, Vista) 1) amp (sovrascrittura Y))) ottenere nome utente, se (SubsetExists (clienti, whoami) 0) SubsetDestroy (clienti, whoami) clienti Endif StringMDX), username) SubsetCreatebyMDX (wHOAMI, StringMDX) UserSubsetGetElementName (clienti, whoami, 1) SubsetDestroy (clienti, whoAmI) copia al fine di spazio pubblico TM1PathE: cmd TM1DataTM1Server query c mkdir TM1PathCube vues ExecuteCommand (Query, 0) cmd query C copia vues TM1PathUser vista cubo. vue vues TM1PathCube Vedi ExecuteCommand. vue (Query, 0) scaricare cubo prima di ricaricare vues CubeUnload (cubo) Else ItemReject (panorama vista in Cube Cube già esiste) Endif 2. Modificare il TM1Path e salvare 3. in Esplora server, Process-gtSecurity Assegnazione, impostare questo processo come Letto a tutti i gruppi dovrebbe essere permesso di pubblicare Ora gli utenti possono pubblicare le loro opinioni sul loro proprio eseguendo questo processo, hanno solo bisogno di inserire il nome del cubo e la vista di pubblicare. Il codice in quanto sopra Tab Prolog può essere sostituito con queste 5 linee: if ((ViewExists (cubo, Vista) 0) ((ViewExists (cubo, Vista) 1) amp (sovrascrittura Y))) PublishView (Cube, Vista, 1 , 1) Else ItemReject (panorama vista in Cube esiste già) Endif Ecco fatto :) Grazie al lettore anonimo (registrati per favore, se si desidera essere accreditati) per il suggerimento. suggerimenti Editor sottoinsiemi per l'editor sottoinsieme per visualizzare elementi consolidati sotto i loro figli: Guarda - gt Espandi sopra per ottenere una risposta più rapida dall'editor sottoinsieme, disattivare la finestra Proprietà View - gt Finestra Proprietà o fare clic sul display Finestra Proprietà dalla barra degli strumenti per aggiungere uno o più elementi in un sottoinsieme esistente senza ricrearlo: dall'editor sottoinsieme - Edit-gtInsert sottoinsieme - selezionare gli elementi - Fare clic su OK per salvare Subset1 come private ora Subset1 viene aggiunto al sottoinsieme esistente - Espandi Subset1 - Clicca sul consolidamento Subset1 elemento quindi eliminare ora è possibile salvare il sottoinsieme con i nuovi elementi per ottenere una vista cubo per visualizzare molto più velocemente in TM1Web: sottoinsiemi delle dimensioni nella parte superiore devono contenere solo 1 elemento ogni click sulle icone nel menù a tendina Export non avrà alcun effetto , solo cliccando sul testo associato sulla slicesnapshotpdf destra inizierà l'esportazione tracciando TM1 riferimento fatto in casa TM1 rapporti possono diventare piuttosto contorto e gli utenti potrebbero ottenere un momento difficile aggiornando loro come è difficile dire dove alcune formule TM1 stanno indicando. La barra degli strumenti Verifica formule di Excel può essere utile in queste situazioni. tasto destro del mouse accanto alla barra in alto per far apparire il menu bar bar. select auditing molto utile per ottenere l'estensione di un riferimento cubo o una dimensione in un rapporto o per vedere quali elementi di una formula DBRW è fatta di. Gli sviluppatori turbo integratore governa codice VBA uno sguardo più da vicino a fette dinamiche fette dinamico può essere molto utile quando gli elementi si visualizzano nei rapporti si evolvono nel tempo, si aggiorna automaticamente con i nuovi elementi. Il seguente articolo tenterà di scavare i parametri che definiscono queste fette e mostrano alcune delle possibilità di interagire con questi. L'idea è stata originariamente presentata da Philip Bichard. I parametri dinamici fetta memorizzati nella lista i nomi del foglio di lavoro per visualizzare questi in Excel: inserire - gt nome - gt incollare lista pasta - gt maggior parte dei parametri sono definiti come SL xx C yy xx è il riferimento fetta 01, 02, 03 . per il maggior numero di fette come ci sono in yY rapporto è il impilati di riferimento dimensione ex: SL01 C 01 si riferisce alla prima dimensione impilati uno sopra colonne SL01C02 si riferisce alla dimensione 2 ° accatastati da SL02R01 superiore si riferisce alla prima dimensione impilati per righe sul più sinistra del 2 ° posizione fetta CUBE01RNG della cella che ospita il nome del cubo sottoinsieme SL01C01DIMNM riferimento usato o nome di dimensione se non si utilizza un sottoinsieme lista SL01C01ELEMS01 salvato degli elementi da visualizzare SL01C01EXPANDUP 1 0 consolidamenti di trigger come nome SL01C01FMTNM compresso o espanso degli elementi formato da utilizzare SL01C01HND. SL01C01IDXS01. gamma SL01C01RNG per la dimensione impilati SL01CPRX01. gamma SL01DATARNG per DBRW cellule SL01FILT impostazioni del filtro nome SL01R01ALIAS del alias utilizzato lista SL01R01DIMNM SL01R01ELEMS01 di elementi da visualizzare SL01R01ELEMSxx. SL01R01EXPANDUP SL01R01HND SL01R01IDXS01 SL01R01NM nome sottoinsieme sezione SL01R01RNG gamma confine SL01RPRX01 SL01TIDXS01. SL01TPRX01. SL01VIEWHND. SL01VIEWIDX. SL01ZEROSUPCOL a zero sopprimere su colonne innescare SL01ZEROSUPROW a zero sopprimere sulle righe innescare SX01C01ENABLE grilletto SX01C01IDX SX01C01WD SX01R01ENABLE grilletto SX01R01HT SX01R01IDX grilletto SX01RNG sezione confine gamma SXBNDDSP sezione confine visualizzazione grilletto TITLE1 subnm formula per il 1 ° dimensione TITLE1NM sottoinsieme posizione della cella TITLE1RNG TITLE2 TITLE2NM TITLE2RNG TITLE3 alias TITLE3ALIAS da visualizzare per il TITLE3RNG dimensione ridefinire il seguente nome SL01FILT da unità SL01FILT FUNCTIONPARAM0.000000SORTORDERdescTUPLESTRSales Measures. Sales a SL01FILT FUNCTIONPARAM0.000000SORTORDERascTUPLESTRSales Measures. Sales costo sarebbe cambiare la colonna su cui la selezione è fatta da unità di vendita a costi di vendita, ed anche l'ordine di scendere a ascendente. Si potrebbe anche ottenere un risultato simile con un'espressione MDX. Il seguente codice cambierà il sottoinsieme dalla dimensione sulla riga pila al predefinita sottoinsieme livello zero dinamico in modo verranno quindi visualizzati tutti gli elementi. Nota, è necessario utilizzare TM1REFRESH o Alt-F9 per ottenere la fetta di ricostruire se stessa, TM1RECALC (F9) sarebbe solo aggiornare le formule DBRW. fette dinamici si romperà con il seguente popup Nessun attributo disponibile a causa di qualche elemento non più esistente o di non avere alcun valore per quella fetta specifica. Una soluzione semplice è quella di disconnettersi dal server TM1, caricare il rapporto, rimuovere l'elemento che causa problemi dalla fetta e nomi delle tabelle, quindi ricollegare al server TM1, la fetta dinamica si aggiorna su riconnessione bene. lavori in corso. attributi Modifica attributi. se si ottiene il seguente messaggio Questa operazione accede a una dimensione che contiene un gran numero di elementi. Il caricamento di questi elementi dal server può richiedere alcuni minuti. Continua modificare direttamente il attributi cubo, invece, è molto più veloce: - Vista - gt Control Display Objects - Aprire la dimensione ElementAttributes cubo - quindi modificare i campi richiesti come in qualsiasi cubo Aggiungi nuovo attributo per le grandi dimensioni, è più veloce di creare solo un processo temporanea TI con il seguente codice nel prologo ATTRINSERT (Modello. InteriorColor, S) Questo esempio crea l'attributo di stringa InteriorColor per la dimensione del modello. controllare se un attributealias esiste già Diciamo che vogliamo creare un codice di alias per la dimensione Customer. In Scheda Avanzate-gtProlog: Se (DIMIX (ElementAttributesCustomer, Codice) 0) AttrInsert (Customer ,, Codice, A) Endif Quindi non c'è bisogno di preoccuparsi per la AttrInsert generare un errore se mai necessario eseguire nuovamente il processo. Se si aggiorna alias in TM1, è necessario svuotare la cache Excel per visualizzare le modifiche eseguendo il McLear macro o semplicemente riavviare Excel. Bulk funzione di reporting Il TM1-gtPrint Relazione prospettive è utile per generare report statici in massa per un determinato insieme di elementi. Il seguente codice è imitando ed estendere tale funzionalità per ottenere rapporti di massa per un rapporto TM1 in modo più flessibile. Ad esempio si potrebbe ottenere un rapporto basato sui rami di una società da salvare in ogni rispettiva cartella di documenti filiali, invece di tutti scaricati in una singola cartella ottenere o si potrebbe anche ottenere ogni rapporto filiale inviato via email al proprio direttore di filiale. commentando le porzioni di codice in TI Desiderate per commentare porzioni di codice per legacy o utilizzo futuro invece di rimuoverlo. Aggiungendo alla parte anteriore di ogni linea è brutta e scombina il rientro. Ecco una soluzione rapida, pulito e semplice. Questo è tutto. Si può venire utile anche per disattivare il codice generato automaticamente nelle istruzioni generate START. Che vi consiglio vivamente di aggiungere segni visibili intorno al codice commentato altrimenti può essere molto facile trascurare il breve if e trascorrere un po 'chiedendo perché il processo non sta facendo nulla. Grazie Paul Simon per la punta. creazione di viste (temporanei) quando si crea una vista dal visualizzatore cubo, c'è un limite difficile la dimensione della vista visualizzata. Si tratta di 100 MB (32 bit) o 500 MB (64 bit) per impostazione predefinita, chan essere modificata con il parametro MaximumViewSize in Tm1s. cfg Ma non è pratico di generare tali grandi viste manualmente. Un'alternativa è quella di farlo dal Turbo Integrator: creare un nuovo processo TI selezionare TM1 Cube vista importazione Fare clic su Sfoglia selezionare il cubo fare clic su Crea vista da lì è possibile creare qualsiasi viewhowever può essere più vantaggioso per creare ed eliminare viste on-the - volare così il server è più pulita e gli utenti sarà meno confuso. Il codice seguente genera una vista dal MyCube cubo compresi tutti gli elementi del cubo. È necessario aggiungere un po 'SubsetCreateSubsetElementInsert al fine di limitare la vista rimuovere tutti i consolidamenti. nella maggior parte dei casi essi interferiscono con l'importazione e si otterrà solo un'importazione parziale o niente del tutto. -------- ProLog CubeName MyCube ViewName TIImport SubsetName TIImport i 1 ciclo attraverso tutte le dimensioni del cubo, mentre (tabdim (CubeName, i) ltgt) ThisDim tabdim (CubeName, i) Se (SubSetExists (ThisDim, SubsetName) 0 ) StringMDX, 0) creare un sottoinsieme filtrando tutte le gerarchie SubsetCreatebyMDX (SubsetName, StringMDX) EndIf ii 1 end If (ViewExists (CubeName, ViewName) 0) ViewCreate (CubeName, ViewName) Endif i 1 ciclo attraverso tutte le dimensioni del cubo, mentre ( tabdim (CubeName, i) ltgt) ViewSubsetAssign (CubeName, ViewName, tabdim (CubeName, i), SubsetName) ii 1 fine ViewExtractSkipCalcsSet (CubeName, ViewName, 1) ViewExtractSkipZeroesSet (CubeName, ViewName, 1) -------- ------ epilogo vista pulizia ViewDestroy (CubeName, ViewName) i 1 ciclo attraverso tutte le dimensioni del cubo, mentre (tabdim (CubeName, i) ltgt) SubsetDestroy (tabdim (CubeName, i), SubsetName) ii 1 end Creazione dinamica sottoinsiemi in Applix TM1 con MDX - a Primer su questo documento Questo Primer MDX è destinato a servire come una semplice introduzione alla creazione di sottoinsiemi dimensione dinamica utilizzando MDX in TM1. Si concentra sul dare esempi di lavoro, piuttosto che cercare di spiegare la teoria completa di MDX e fa in modo di coprire le caratteristiche più utili per gli utenti TM1. TM1 attualmente (a partire da 9,0 SP3) consente solo agli utenti di utilizzare MDX per creare sottoinsiemi di dimensione e non per definire una vista cubo. Ciò significa che l'utilizzo di MDX in TM1 è spesso molto diversa in termini di sintassi e dell'intenzione dagli esempi trovati in libri e su internet. Come MDX (espressioni Multi-Dimensional) è un linguaggio di query standard di settore per i database OLAP di Microsoft ci sono molti riferimenti e gli esempi che si trovano su Internet, anche se tenere a mente che TM1 pretende supporta tutti gli aspetti della lingua e aggiunge alcuni caratteristiche uniche proprie. Questo può rendere difficile utilizzare gli esempi trovati sul web, mentre tutti gli esempi in questo documento possono essere semplicemente copiato e incollato in TM1 e verranno eseguiti senza modifiche, a patto che l'esempio mini-modello creato come documentato in seguito. Documento completo come solo una pagina HTML qui. Che è un sottoinsieme dinamico MDX basata TM1 Un sottoinsieme dinamico è uno che non è, statico, elenco fisso ma invece si basa su una query che viene ri-valutata ogni volta il sottoinsieme viene utilizzato. Infatti, MDX potrebbe essere usato per creare un sottoinsieme statico e un esempio è mostrato sotto, ma questo improbabile utile o comune. Alcuni esempi di sottoinsiemi dinamici utili potrebbero essere un elenco di tutti i prodotti di livello base di un elenco dei nostri primi 10 clienti per il margine lordo un elenco delle spedizioni di fornitura scaduti tutti i centri di costo che non hanno ancora presentato il loro bilancio. Il punto è, queste liste (sottoinsiemi) possono variare da secondo a base secondo sulla struttura o di dati in TM1. Ad esempio, non appena un nuovo ramo è aggiunto in Europa, i rami europee sottoinsieme immediatamente contenere questo nuovo ramo, senza alcun intervento manuale necessario. MDX è il linguaggio di query utilizzato per definire questi sottoinsiemi. MDX è un linguaggio di query standard per i database multidimensionali come TM1, anche se TM1 supporta solo un determinato sottoinsieme (scusate il gioco di parole) di tutta la lingua e aggiunge alcune caratteristiche uniche della propria pure. Quando si definisce un sottoinsieme utilizzando MDX invece di un sottoinsieme di serie, i negozi TM1 questa definizione, piuttosto che l'insieme risultante. Questo significa che la definizione o la query viene nuovamente eseguito ogni volta che si guarda senza che l'utente o l'amministratore bisogno di fare nulla. Se il database è stato modificato in qualche modo, allora si possono ottenere risultati diversi da l'ultima volta che è stato utilizzato. Ad esempio, se un sottoinsieme è definita come i figli di Rami West Coast e questo inizialmente ritorna Oakland, San Francisco, San Diego prima volta che viene definito, si può poi tornare Oakland, San Francisco, San Diego, Los Angeles una volta LA ha stato aggiunto nella dimensione di figlio di rami West Coast. Questo è ciò che intendiamo per dinamiche il risultato cambia. Un altro motivo che può causare il sottoinsieme di cambiare è quando si basa sui valori all'interno di un cubo o attributo. Ogni giorno sul giornale più grandi motori del mercato azionario sono elencati, come ad esempio un top 10 in termini di aumento dei prezzi delle azioni. In un modello TM1 questo sarebbe un sottoinsieme guardando una misura di variazione prezzo delle azioni e chiaramente sarebbe tale da restituire un diverso insieme di 10 membri ogni giorno. La parte migliore è che il sottoinsieme aggiornerà i risultati automaticamente senza alcun lavoro necessario da parte di un utente. Come creare un sottoinsieme MDX-based in TM1 Gli stessi passi di base possono essere seguiti con tutti gli esempi in questo documento. Generalmente gli esempi possono essere copiare e incollato nella finestra Espressione del Editor sottoinsiemi della dimensione in questione spesso prodotto. Si noti che è irrilevante che il cubo dimensione viene utilizzato da otterrete stessi risultati se si apre l'Editor sottoinsiemi dimensione all'interno di una visualizzazione cubo, l'albero cubo in Esplora server o l'albero dimensione in Esplora server. Per visualizzare e modificare una query MDX si deve essere in grado di vedere la finestra Espressione nell'Editor sottoinsiemi. Per attivare o disattivare questa finestra on e off scegliere Visualizza finestra Espressione. È possibile ora solo digitare (o incollare) la query in questa espressione di Windows e premere il pulsante Aggiorna per vedere i risultati. Come creare un sottoinsieme statico con MDX Un sottoinsieme statico è uno che non varia nel suo contenuto. Questa query restituirà le stesse 3 membri (sconto di prestito, Term Loan e al dettaglio) ogni volta. Non vi preoccupate, si fa più emozionante da qui. Come creare un sottoinsieme dinamico con MDX TM1 supporta solo un certo numero di funzioni dalla specifica completa MDX. Le varie versioni di TM1 supporteranno funzioni diverse (e potenzialmente sostenere l'in modi diversi). Il set di funzioni valida per la versione di TM1 che si sta utilizzando può essere trovato nel file della Guida principale, indicando il riferimento Materiale MDX Funzione di sostegno. Prima di tentare di scrivere una nuova query, assicurarsi che sia supportato, e anche se alcune funzioni non quotate certamente fare il lavoro che devono essere utilizzate a proprio rischio. Il messaggio di errore standard che significa che la funzione non è realmente supportato dalla versione di TM1 è, non riuscito per la compilazione espressione. Un avvertimento: per sua stessa natura, i risultati di un sottoinsieme dinamico può cambiare. Quando compresi sottoinsiemi dinamici in vista, i processi, le funzioni SUBNM, e così via, considerare attentamente quali potrebbero essere i potenziali risultati futuri, soprattutto se il sottoinsieme potrebbe un giorno essere vuoto. I due metodi più comuni per andare su in realtà la creazione di un sottoinsieme dinamico sono per la loro creazione a mano o con TurboIntegrator. A mano . È possibile digitare (o incollare) una query nella finestra Espressione come spiegato in precedenza, oppure si può scegliere Strumenti Record di espressione (e quindi Interrompi registrazione quando fatto) per attivare una sorta di videoregistratore. È quindi possibile utilizzare le normali caratteristiche di l'editor sottoinsieme (ad esempio selezionare in base al livello, in ordine decrescente, ecc) e il registratore si trasformerà le vostre azioni in un'espressione MDX valida. Questo è un ottimo modo per vedere alcuni esempi di sintassi valida, soprattutto per le query più complesse. Quando sono stati registrando espressione e scegliere Interrompi registrazione TM1 vi chiederà di confermare se si desidera collegare l'espressione con il sottoinsieme - fare in modo di dire Sì e selezionare la casella di controllo Salva espressione quando si salva il sottoinsieme risultante, altrimenti solo un elenco statico del risultato viene salvato, non è la query dinamica stessa. Utilizzando TurboIntegrator. Solo una linea, utilizzando SubsetCreateByMDX, è necessario per creare e definire il sottoinsieme. Avrete bisogno di sapere quali query che si desidera, come la definizione già. Si noti che la query può essere costruito nello script di TI tramite la concatenazione di testo in modo può incorporare variabili dal vostro script e consentire le query lunghe per essere costruito in varie fasi, che sono più facili da leggere e mantenere. SubsetCreatebyMDX (prodotti di base,, 0)) tutti i sottoinsiemi MDX-TI creati vengono salvati come query MDX dinamiche automaticamente e non come un elenco statico. Si noti che, almeno fino a TM1 v9.0 SP3, sottoinsiemi MDX-based non può essere distrutta (SubsetDestroy) quando sono utilizzati da una visione del pubblico, e non possono essere ricreate utilizzando un secondo comando SubsetCreateByMDX. Pertanto è difficile modificare sottoinsiemi MDX-based utilizzando TI. Mentre la natura dinamica della definizione sottoinsieme può rendere un po 'improbabile che effettivamente vuole fare questo, è importante tenere a mente. Se avete bisogno di cambiare qualche aspetto della query (ad esempio una TM1FILTERBYPATTERN 2006-12 al 2007-01 potrebbe essere necessario definire la query per utilizzare parametri esterni, come documentato in questo documento. Ciò avrà un piccolo impatto sulle prestazioni sopra il più semplice versione hardcoded. Inoltre, il filtro contro i valori di un cubo con SubsetCreateByMDX nella scheda Epilogo ad esempio, 0), test. (pubblicazione Measures. Amount) gt 0) non funziona se i valori capita di sono stati caricati nella scheda dati. È necessario eseguire il comando SubsetCreateByMDX in un successivo processo di TI. Si noti che TI ha un limite di 256 caratteri per definire sottoinsiemi MDX, almeno fino a v9.1 SP3, che può essere molto limitante. Sintassi e layout Una query può essere suddiviso su più righe per renderlo più leggibile. Per esempio:. FILTRO (, 0), Test2 (tasso Measures. Rate) 19) è più leggibile che avere tutta la query in una sola riga. La sezione di filtro effettivo è più facilmente letta e modificata ora avendo su una riga. Si noti che i riferimenti ai membri di solito hanno il nome della dimensione come prefisso. Ad esempio, infatti il nome della dimensione è facoltativo ma solo se il nome del membro (vendita al dettaglio, in questo caso) è completamente unico entro l'intero server - vale a dire non ci sono cubi, dimensioni o membri con quel nome esatto. Per esempio questa è la stessa query con il nome della dimensione omesso: che lavorerebbe nel contesto dell'applicazione di esempio utilizzato da questo documento, ma sarebbe rischioso in un'applicazione reale. Il messaggio di errore ricevuto quando dimenticando di specificare il prefisso sarebbe qualcosa di simile, di livello o del nome utente al dettaglio ambiguo: trovato in dimensioni e poi si va avanti ad elencare le varie dimensioni in cui il nome del membro non univoco può essere trovato, che è molto utile. È quindi certamente più sicuro e più performante di utilizzare sempre il prefisso dimensione. L'uso di parentesi quadre a volte può sembrare un po 'arbitrario durante la lettura esempi di query MDX. Il fatto è che un nome di oggetto OLAP (ad esempio nome cubo, nome della dimensione, nome del membro) devono essere racchiusi tra parentesi quadre solo se contiene uno spazio, inizia con un numero o è un MDX parola riservata (ad esempio Select). Tuttavia, a volte può essere più semplice da decidere di utilizzare sempre le staffe query in modo simili possono essere confrontati fianco a fianco con maggiore facilità. La definizione esatta di un membro di TM1 è quasi sempre espressa come dimensione Name. Member nome e non di più. In altri prodotti che utilizzano anche MDX come un linguaggio di query (ad esempio Microsoft Analysis Services) si può notare che le query specifica il percorso completo dal nome della dimensione attraverso la gerarchia fino al nome del membro, ad esempio: Date.2009.Q1.Feb. Week 06 questo può anche essere scritta come Date.2009Q1FebWeek 06 La ragione di questo è che gli altri prodotti non può imporre ad ogni nome del membro per essere unico dato che ogni membro ha un contesto (la sua famiglia) per consentirle di essere identificato in modo univoco, che è il motivo hanno bisogno di sapere esattamente quale settimana 06 è necessaria in quanto ci possono essere altri (nel 2008 per esempio, nell'esempio di cui sopra). TM1 richiede che tutti i nomi dei membri, a qualsiasi livello (e all'interno di Alias) di essere completamente unico all'interno di quella dimensione. TM1 avrebbe bisogno di fare Q1, febbraio e Settimana 06 più esplicito in primo luogo (cioè 1 ° trimestre 2009, Feb 2009, Settimana 06 2009), ma si può poi basta fare riferimento alla Date. Week 06 2009. Infine, caso (ad esempio lettere maiuscole contro minuscolo) non è importante con i comandi MDX (ad esempio filtro o, topCount o topCount sono tutte belle), ma ancora una volta è preferibile adottare un solo stile di serie per rendere più facile da leggere. Il modello di esempio utilizzato in questo documento, molti esempi di query dinamiche sarà dato. Tutti lavorano (esattamente come scritto, basta copiare e incollare li nella finestra Espressione nell'Editor sottoinsiemi della dimensione appropriata per utilizzarli) sul semplice insieme di cubi e dimensioni illustrato di seguito. Il modello è volutamente semplice, senza particolari caratteristiche così si dovrebbe trovare facile trasferire il lavoro per il proprio modello. Il modello utilizzato incluso 1 dimensione principale, prodotto, in cui la stragrande maggioranza delle query funziona più 3 cubetti: Test, Test2 e Test3. I valori dei dati nei cubi varieranno durante i test (youll vogliono modificare i valori e ri-eseguire la query per assicurarsi che i risultati cambiano e sono corretti), ma gli screenshot qui sotto mostrano le strutture del cubo e dimensione abbastanza bene per voi di ricreare rapidamente li o il modo di utilizzare invece il proprio modello. Per semplificare la distribuzione di questo documento non vi è alcuna intenzione di distribuire anche l'attuale file di modello TM1. Si noti che la dimensione principale utilizzato, prodotto, caratterizzato da gerarchie incomplete, e molteplici,. TM1SubsetAll, Membri, membro varia La base per molte query, restituisce (quasi, vedi sotto) l'intera dimensione, che è l'equivalente di fare clic sul pulsante Tutti i nell'Editor sottoinsiemi. TM1SUBSETALL (prodotto) Nota che solo l'ultima istanza nella prima gerarchia dei membri consolidate più volte viene restituito. La funzione di membri, d'altra parte, offre la piena dimensione, duplicati inclusi: Product. Members un intervallo di membri contigui dello stesso livello possono essere selezionati specificando il primo e l'ultimo membro del set si richiede con i due punti tra loro. 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 allowing duplicates. Note: the following section does not work in v9.1 SP2, but does work in v9.0. Your mileage may vary. 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 space 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 explicitly 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
Comments
Post a Comment