Moving-average-cognos-8 4

Moving-average-cognos-8 4

S & p-swing-trading-system
Moving-average-14-strategy
Asli-kura-trading-rules-download


Sistem perdagangan-pengambil Pilihan-trading-online-broker My-binary-options-account Sg-sistem perdagangannya Opsi Questrade Trading-sistem-singapura

Situs web saat ini tidak tersedia Situs web yang ingin Anda kunjungi saat ini tidak tersedia. Ini mungkin karena situs web sedang dikembangkan, pemeliharaan sedang berlangsung atau situs web ditutup karena beberapa alasan lain. Pemilik domain, silakan lihat alamat kontak Anda (email) atau hubungi supportloopia untuk informasi lebih lanjut. Webbplatsen gr ej att n fr tillfllet Webbplatsen du vill beska r fr tillfllet inte tillgnglig. Detta kan bero p att sidan r di bawah utveckling, att underhll grs eller att sidan av annan anledning r stngd. Domngare, se din kontaktadress (e-post) eller kontakta supportloopia.se fr mer informasi. Nettsiden kan ikke ns untuk yeblikket Nettsiden du vil beske er untuk yeblikket ikke tilgjengelig. Dette kan komme av di siden er di bawah utvikling, vedlikehold utfres eller di siden av en anledning er stengt. Domeneier, se din kontaktadresse (e-post) eller kontakt supportloopia.no untuk mer informasjon. Pesan ini juga tersedia. Bagian dari Visma middot Tel: 46 21-12 82 22 middot Faks: 46 21-12 82 33 middot E-mail: infoloopia En del av Vismakoncernen middot Telp: 021-12 82 22 middot Faks: 021-12 82 33 middot E-post: infoloopia.sePelajari tentang gt bagaimana cara menghitung jumlah jumlah sampai dengan tanggal di cognos 10 Judul hasil pencarian: Cognos: Cognos BI 10: perhitungan antara 2 kueri yang berbeda. Deskripsi hasil pencarian: 2 Okt 2013. Saya memiliki Q1 dengan tabel CT1 untuk mendapatkan periode berjalan reveue1 (jumlah). Periode didasarkan pada 2 prompt tanggal terpilih (bulan) nilai, misalnya: start. Judul hasil pencarian: Cognos: Cognos BI 10: Bergulir 12 Bulan total untuk setiap bulannya. Deskripsi hasil pencarian: 24 Jul 2014. Tampilan: Percakapan Berdasarkan Tanggal. Saya perlu menghitung hitungan Rolling 12 bulan untuk setiap bulan yang terdaftar. Pertama, akan sangat membantu untuk mengetahui versi Cognos 10 yang Anda gunakan, data kuotimensi yang tepat. Ini dikenal sebagai ukuran Time-State - tidak dapat ditambahkan dalam beberapa periode waktu. Judul hasil pencarian: Menggunakan fungsi PeriodToDate dalam IBM Cognos 10 Report Hasil pencarian deskripsi: 13 Mei 2011. Deskripsi tentang bagaimana periode fungsi dimensionalToDate dapat digunakan di luar dimensi waktu untuk menghitung jumlah total yang dijalankan terhadap. Judul hasil pencarian: Cognos: Cognos BI 10: Rate Variance tidak digabungkan dengan benar. Deskripsi hasil pencarian: 27 Agustus 2013. Tampilan: Percakapan Berdasarkan Tanggal. 1-6 dari 6. Saya mencoba menghitung varian Price, Volume and Mix antara 2 periode. Namun, jumlah dari level yang lebih rendah tidak sama dengan level uppper. Pertanyaan saya adalah bagaimana cara menghitung hanya tingkat daun dan memiliki Cognos BI 10.1 agregat tingkat atas. Hasil pencarian judul: Cognos: Cognos 8: Laporan Penjualan untuk Hari Sebelumnya, 5 Hari Terakhir dan. Deskripsi hasil pencarian: Mar 18, 2011. 10 balasan Latest Post - 18 Des 2013 oleh Arhan. Maka Anda akan membuat perhitungan individu untuk setiap nilai pengukuran periode. Saya pikir mungkin ada perhitungan tanggal pertama perhitungan bulan di sisi Cognos yang mana. Saya akan mengomentari jawaban Phil bahwa, karena Anda akan SUM jumlah penjualan ini, jangan gunakan. Judul hasil pencarian: Cognos: Cognos 8: Fungsi total bergerak rata-rata bergerak. Deskripsi hasil pencarian: Jan 7, 2009. Tampilan: Percakapan Berdasarkan Tanggal. Begini bagaimana file bantuan menggambarkan fungsi total bergerak. Diperbarui pada 7 Januari 2009 pukul 13:10 oleh SystemAdmin. Saya mengikuti formula yang Anda bagikan untuk pindah - total tapi yang aneh adalah. Judul hasil pencarian: Cognos: Cognos BI 10: Secara dinamis mendapatkan bulan di kuartal penutupan. Deskripsi hasil pencarian: Dec 20, 2012. Lingkungan adalah studio laporan Cognos 8.4.1 melawan kubus MSAS. Bulan tertentu (3,6,9 amp 12) hanya dari tingkat Bulan dalam dimensi Tanggal. Bagaimana cara mengotomatisasi membuat perhitungan yang otomatis. Re: Untuk secara dinamis mendapatkan bulan di kuartal penutupan periode. Total (currentMeasure dalam set generate (.)). Judul hasil pencarian: Cognos: Cognos 8: bagaimana mengelompokkan bidang tanggal 1 minggu atau 2 minggu dll. Uraian hasil pencarian: 11 Mei 2011. Tapi yang saya inginkan adalah mengelompokkan tanggal inspeksi per minggu dan jumlah qty seperti di bawah ini. Tanggal pemeriksaan Qty 2011-01-03 4 (minggu 1) 2011-01-10 3 (minggu ke 2). Dengan mencoba menghitung semua periode yang dibutuhkan berdasarkan tanggal di. Judul hasil pencarian: Cognos: Cognos Express: Reporter. Bagaimana cara melakukan jumlah kumulatif? Deskripsi hasil pencarian: 6 Jun 2012. Cognos Express. Tampilan: Percakapan Berdasarkan Tanggal. Ini bekerja dengan baik untuk menunjukkan pendapatan oleh perusahaan dan bulan tapi saya ingin jumlah kumulatif. Judul hasil pencarian: Praktik Terbukti IBM Cognos: Menggunakan Kategori Waktu Relatif. Deskripsi hasil pencarian: Aug 30, 2011. Penerapan. Panduan ini berlaku untuk IBM Cognos Report Studio versi 8.4 ke atas. Gambar 10. Editor ekspresi anggota slicer dengan ungkapan. Gambar 10. Anggota slicer Mengapa ini Untuk menghitung 39PreviousMonth39 fungsi PrevMember digunakan. Publish- date 08302011. Judul hasil pencarian: Cognos: Cognos BI 10: Memasukkan keseluruhan dari permintaan yang berbeda. Deskripsi hasil pencarian: Jan 21, 2013. Cognos BI 10. Dan kehilangan produk bila dibandingkan dengan 12 dimensi tanggal bergulir. Contohnya adalah menghitung semua produk yang telah mengalami pertumbuhan antara yang terakhir. Saya melihat setiap produk, jika memiliki pertumbuhan antara dua periode tersebut. Tambahkan itu Bagaimana cara mendapatkan total kueri ini ke dalam crosstab Judul hasil pencarian: Cognos: Cognos 8: Mengambil data antara dua tanggal dalam laporan. Deskripsi hasil pencarian: Jan 5, 2011. Saya memiliki prompt Periode (kotak Daftar) yang akan menampung tanggal dalam format Jan-10. Satu item data untuk menghitung tanggal mulai tahun keuangan (April-YY). Judul hasil pencarian: Cognos: Cognos 8: CY dan PY tidak bekerja dengan benar. Deskripsi hasil pencarian: 2 Agustus 2011. Cognos 8. 10 10.1.1 10.2 10.2.1 8 8.3 8.4 8.4.1 a dan. CY vs PY Measure menambahkan tahun-tahun bersama alih-alih mengurangi PY dari. Periode sampai saat total (dikelompokkan) atau periode N berjalan total (dikelompokkan). Judul hasil pencarian: Praktik Terbukti Bisnis IBM Analytics: Menetapkan Tanggal Dinamis. Deskripsi hasil pencarian: Nov 14, 2012. Menetapkan Nilai Prompt Tanggal Dinamis dalam IBM Cognos 10.2 Report Studio. Produk: IBM Cognos 10 Bidang yang diminati: Pelaporan. Menambahkan JavaScript untuk Mengatur Nilai Prompt Dinamis. Snook (tsnook) Opher Banarie (OpherB) Halo - Apakah kalian mencari solusi untuk masalah reset prompt Judul hasil pencarian: Cognos 8 Fungsi Dimensi - IBM Search result description: Produk perangkat lunak Cognos dan layanan terkait yang telah Anda lisensinya atau beli dari Cognos. BOTTOMSUM. Fungsi Dimensi. 10. Informasi Kepemilikan Cognos. Ukuran Pendapatan adalah default dari dimensi Ukuran. Mengembalikan anggota dari periode yang berbeda dalam kategori yang sama. Buku masak TM1 Ini adalah upaya penulisan kolaboratif: pengguna dapat berkolaborasi menulis artikel dari buku ini, memposisikan artikel dengan benar, dan meninjau atau memodifikasi artikel yang telah ditulis sebelumnya. Jadi, ketika Anda memiliki beberapa informasi untuk dibagikan atau saat Anda membaca artikel buku dan Anda tidak menyukainya, atau jika Anda merasa artikel tertentu bisa ditulis dengan lebih baik, Anda dapat melakukan sesuatu untuk hal itu. Artikel terbagi dalam bagian berikut: perspektif server explorer cube viewer tm1web planning manager cube viewer tips kolom lebar bila Anda memiliki pandangan dengan banyak dimensi dapat dilihat, hal itu terjadi bahwa sebagian besar properti layar dikonsumsi oleh kolom dimensi dan Anda Menemukan diri Anda bergulir banyak kiri dan kanan untuk membaca data poin. Untuk memudahkan masalah itu: .Memilih alias terpendek untuk semua dimensi dari editor subset .pilih nama subset sesingkat mungkin. Tentukan Options-gtExpand Row Header ExcelPerspectives Tip untuk mengurangi kemungkinan menabrak Excel Anda, nonaktifkan Perhitungan Otomatis. Pergi ke tab Tools-gtOptions-gt Calculation kemudian klik pada tombol Manual Anda dapat menggunakan: F9 untuk menyegarkan secara manual semua buku kerja yang terbuka Shift F9 untuk menyegarkan hanya lembar kerja F2 yang sekarang masuk (sel edit) untuk menyegarkan hanya 1 sel Di TM1 9.4. 1, spreadsheet akan menghitung ulang secara otomatis saat membuka buku kerja, atau mengubah SUBNM meskipun kalk otomatis dinonaktifkan. Dari menu atas Excel, klik Insert-Name-Define. Di kotak Define Name dialog, masukan TM1REBUILDOPTION. Atur nilai pada kotak Refers to to to 0 dan klik OK. . Hindari beberapa irisan dinamis pada beberapa lembar di buku kerja jika Anda bisa, jika tidak Excel menjadi sangat tidak stabil dan beberapa referensi mungkin akan kacau. Jika Anda mendapatkan e10) direktori data tidak ditemukan kesalahan saat memuat Perspektif, Anda perlu menentukan direktori data server lokal Anda meskipun Anda tidak dapat menjalankannya. Buka File-gtOptions dan masukkan folder yang valid di kotak Data Directory. Jika kotak itu berwarna abu-abu maka Anda perlu mengedit secara manual variabel di tm1p.ini yang tersimpan di PC Anda. Atau Anda dapat memodifikasi setting secara langsung dari Excel dengan kode VBA berikut: Application.Run (OPTSET, DatabaseDirectory, C: somepath) Versi TM1 yang Anda gunakan untuk mempublikasikan pandangan yang dilihat pengguna Penerbitan masih jauh dari proses yang cepat dan sederhana di TM1. Pertama, admin tidak bisa melihat tampilan pengguna lain. Kedua, pengguna tidak bisa mempublikasikan pandangan mereka sendiri. Jadi, tampilan penerbitan selalu memerlukan intervensi langsung dari admin, juga tidak lagi :) if ((ViewExists (Cube, View) 0) ((ViewExists (Cube, View) 1) amp (Timpa Y))) dapatkan UserName If (SubsetExists (Klien, WhoAmI) 0) SubsetDestroy (Klien, WhoAmI) Klien Endif StringMDX), USERNAME) SubsetCreatebyMDX (WhoAmI, StringMDX) UserSubsetGetElementName (Klien, WhoAmI, 1) SubsetDestroy (Klien, WhoAmI) melihat salinan ke ruang publik TM1PathE: TM1DataTM1Server Query cmd Cmkdir TM1PathCube vues ExecuteCommand (Query, 0) Query cmd c copy TM1PathUser Cube vues Lihat .vue TM1PathCube vues Lihat .vue ExecuteCommand (Query, 0) unload cube pertama untuk reload vues CubeUnload (Cube) ItemSejak Lain (Lihat lihat di Cube Cube sudah Ada) Endif 2. ubah TM1Path dan simpan 3. di Server Explorer, Process-gtSecurity Assignment, atur proses itu sebagai Read untuk semua grup yang seharusnya diizinkan menerbitkannya Sekarang pengguna Anda dapat mempublikasikan pandangan mereka sendiri dengan menjalankan proses ini, Mereka hanya perlu memasukkan nama o F kubus dan tampilan untuk mempublikasikan. Kode di Tab Prolog di atas dapat diganti dengan 5 baris berikut: if ((ViewExists (Cube, View) 0) ((ViewExists (Cube, View) 1) amp (Timpa Y))) PublishView (Cube, View, 1 , 1) Else ItemReject (Lihat lihat di Cube Cube sudah ada) Endif Thats it :) Terimakasih kepada pembaca anonim (silahkan mendaftar jika ingin dikreditkan) untuk sarannya. Subset editor tips untuk subset editor untuk menampilkan elemen konsolidasi di bawah anak-anak mereka: Lihat -gt Expand Di atas untuk mendapatkan respon yang lebih cepat dari editor subset, matikan Properties Window View -gt Properties Window atau klik Display Properties Window dari toolbar untuk menambahkan Satu atau lebih elemen dalam subset yang ada tanpa menciptakannya: dari subset editor - Edit-gtInsert Subset - Pilih elemen - Klik OK untuk Save as private Subset1 sekarang Subset1 ditambahkan ke subset Anda yang ada - Expand Subset1 - Klik pada subset1 consolidation Elemen kemudian hapus Anda sekarang dapat menyimpan subset Anda dengan elemen baru untuk mendapatkan tampilan kubus agar ditampilkan lebih cepat di tm1web: subkumpulan dimensi di bagian atas hanya boleh berisi 1 elemen yang mengeklik ikon di menu tarik-turun Ekspor tidak akan berpengaruh. , Hanya mengklik teks yang terkait pada iricesnapshotpdf kanan akan memulai ekspor melacak referensi TM1 Laporan TM1 buatan sendiri bisa menjadi sangat rumit dan pengguna mungkin mendapatkan Sulit memperbarui mereka karena sulit untuk mengatakan di mana beberapa formula TM1 mengarah ke. Toolbar audit rumus excel bisa berguna dalam situasi seperti itu. Klik kanan di sebelah bilah atas untuk membuka menu bar. Pilihlah bar audit Cukup berguna untuk mendapatkan ruang lingkup referensi kubus atau dimensi dalam laporan atau untuk melihat elemen mana formula DBRW dibuat. Pengembang aturan integrator turbo kode VBA melihat lebih dekat irisan dinamis Irisan dinamis bisa sangat berguna bila elemen yang Anda tampilkan di laporan Anda berevolusi dari waktu ke waktu, mereka secara otomatis memperbarui dengan elemen baru. Artikel berikut akan mencoba menggali parameter yang mendefinisikan irisan ini dan menunjukkan beberapa kemungkinan untuk berinteraksi dengannya. Idenya awalnya disampaikan oleh Philip Bichard. Parameter slice dinamis yang tersimpan dalam daftar nama worksheet untuk menampilkannya di Excel: masukkan -gt name -gt paste -gt paste list sebagian besar parameter didefinisikan sebagai SL xx C yy xx adalah referensi irisan 01, 02, 03 . Untuk irisan sebanyak yang ada dalam laporan yy adalah referensi dimensi yang ditumpuk: SL01 C 01 berkaitan dengan dimensi ditumpuk pertama di atas kolom SL01C02 berkaitan dengan dimensi ditumpuk kedua dari SL02R01 teratas yang berkaitan dengan dimensi stacked pertama untuk Baris paling kiri dari slice CUBE01RNG bagian ke 2 dari sel yang mensponsori nama subband referensi yang dinamai SL01C01DIMNM yang digunakan atau nama dimensi jika tidak menggunakan subset tersimpan SL01C01ELEMS01 daftar elemen untuk menampilkan konsolidasi pemicu SL01C01EXPANDUP 1 0 sebagai nama SL01C01FMTNM yang runtuh atau diperluas. Dari format elemen untuk menggunakan SL01C01HND. SL01C01IDXS01. Kisaran SL01C01RNG untuk dimensi ditumpuk SL01CPRX01. SL01DATARNG range untuk sel DBRW SL01FILT filter setting SL01R01ALIAS nama alias yang dipakai SL01R01DIMNM SL01R01ELEMS01 daftar elemen yang akan ditampilkan SL01R01ELEMSxx. SL01R01EXPANDUP SL01R01HND SL01R01IDXS01 SL01R01NM subset nama SL01R01RNG batas bagian SL01RPRX01 SL01TIDXS01. SL01TPRX01. SL01VIEWHND. SL01VIEWIDX SL01ZEROSUPCOL nol menekan pada kolom memicu SL01ZEROSUPROW nol menekan pada baris memicu SX01C01ENABLE memicu SX01C01IDX SX01C01WD SX01R01ENABLE memicu SX01R01HT SX01R01IDX memicu SX01RNG bagian batas rentang SXBNDDSP bagian batas tampilan memicu TITLE1 subnm formula untuk 1 dimensi TITLE1NM subset TITLE1RNG lokasi sel TITLE2 TITLE2NM TITLE2RNG TITLE3 TITLE3ALIAS alias untuk menampilkan untuk Dimensi TITLE3RNG mendefinisikan ulang nama berikut SL01FILT dari SL01FILT FUNCTIONPARAM0.000000SORTORDERdescTUPLESTRSales Measures.Sales Unit ke SL01FILT FUNCTIONPARAM0.000000SORTORDERascTUPLESTRSales Measures.Sales Cost akan mengubah kolom pemilahan yang dibuat dari unit penjualan hingga biaya penjualan, dan juga perintah dari turun ke Naik. Seseorang juga bisa mencapai hasil yang serupa dengan ekspresi MDX. Kode berikut akan mengubah subset dari dimensi pada baris stack ke subset Level Zero Dynamic yang telah ditentukan sehingga semua elemen akan ditampilkan. Perhatikan, Anda perlu menggunakan TM1REFRESH atau Alt-F9 untuk mendapatkan potongan itu untuk membangun kembali dirinya sendiri, TM1RECALC (F9) hanya akan memperbarui formula DBRW. Slice dinamis akan pecah dengan popup berikut Tidak ada nilai yang tersedia karena beberapa elemen tidak ada lagi atau tidak memiliki nilai untuk potongan tertentu. Perbaikan yang mudah adalah memutuskan koneksi dari server TM1, memuat laporan, menghapus elemen yang menyebabkan masalah dari tabel slice dan nama, lalu menyambung kembali ke server TM1, slice dinamis akan menyegarkan kembali koneksi dengan baik. Pekerjaan sedang berjalan Atribut Edit Atribut. Jika Anda mendapatkan pesan berikut Operasi ini mengakses dimensi yang berisi sejumlah besar elemen. Pengunggahan elemen-elemen ini dari server mungkin memakan waktu beberapa menit. Lanjutkan mengedit secara langsung atribut kubus sebagai gantinya, ini jauh lebih cepat: - View -gt Display Control Objects - Buka dimensi ElementAttributes cubube - lalu ubah bidang yang dibutuhkan seperti di kubus Add Atribut Baru untuk dimensi besar, lebih cepat untuk membuat saja. Proses TI sementara dengan kode berikut di Prolog ATTRINSERT (Model. InteriorColor, S) Contoh ini menciptakan atribut string InteriorColor untuk dimensi Model. Memeriksa apakah attributealias sudah ada katakanlah kita ingin membuat alias Code untuk dimensi Customer. Di Advanced-gtProlog Tab: Jika (DIMIX (ElementAttributesCustomer, Code) 0) AttrInsert (Customer ,, Code, A) Endif Jadi Anda tidak perlu khawatir tentang AttrInsert yang menimbulkan kesalahan jika Anda harus menjalankan prosesnya lagi. Jika Anda mengupdate alias di TM1, Anda perlu menghapus cache excel untuk melihat perubahannya dengan menjalankan makro mclear atau hanya me-restart excel. Pelaporan Massal Fungsi Laporan TM1-gtPrint dari Perspektif berguna untuk menghasilkan laporan statis secara massal untuk serangkaian elemen tertentu. Kode berikut menirukan dan memperluas fungsi itu untuk mencapai pelaporan massal untuk laporan TM1 dengan cara yang lebih fleksibel. Misalnya Anda bisa mendapatkan laporan berdasarkan cabang perusahaan yang akan disimpan di setiap folder dokumen cabang masing-masing alih-alih membuat semuanya dibuang dalam satu folder atau Anda juga bisa mendapatkan setiap laporan cabang yang dikirim melalui email ke manajer cabangnya sendiri. Mengomentari bagian kode di TI Anda ingin memberi komentar beberapa bagian kode untuk warisan atau penggunaan masa depan alih-alih mengeluarkannya. Menambahkan di depan setiap baris adalah hal yang jelek dan mengacaukan indentasi. Berikut adalah perbaikan yang cepat, rapi dan sederhana. Itu dia. Ini bisa jadi juga berguna untuk mematikan kode yang dihasilkan secara otomatis di START GENERATED START. Saya akan sangat menyarankan Anda menambahkan spidol yang jelas di sekitar kode yang dikomentari jika tidak, hal itu bisa sangat mudah untuk mengabaikan pernyataan singkat jika dan menghabiskan beberapa waktu sambil bertanya-tanya mengapa prosesnya tidak melakukan apa-apa. Terima kasih Paul Simon untuk tipnya. Membuat tampilan (sementara) saat membuat tampilan dari penampil kubus, ada batas maksimal ukuran tampilan yang ditampilkan. Ini adalah 100MB (32bit) atau 500MB (64bit) secara default, maka chan dapat diubah dengan parameter MaximumViewSize di tm1s.cfg Tetapi tidak praktis menghasilkan tampilan yang begitu besar secara manual. Alternatifnya adalah melakukannya dari Turbo Integrator: buat proses TI baru pilih tampilan TM1 Cube import klik browse pilih kubus klik buat view dari sana anda bisa membuat viewhowever apapun bisa lebih menguntungkan untuk membuat dan menghapus tampilan on-the- Lalat jadi server anda lebih bersih dan pengguna akan kurang bingung. Kode berikut menghasilkan tampilan dari kubus MyCube termasuk semua elemen kubus. Anda perlu menambahkan beberapa SubsetCreateSubsetElementInsert untuk membatasi tampilan menghapus semua konsolidasi. Dalam kebanyakan kasus mereka mengganggu impor dan Anda hanya akan mendapatkan sebagian impor atau tidak sama sekali. -------- prolog CubeName MyCube ViewName TIImport SubsetName TIImport i 1 loop melalui semua dimensi kubus sementara (tabdim (CubeName, i) ltgt) ThisDim tabdim (CubeName, i) If (SubSetExists (ThisDim, SubsetName) 0 ) StringMDX, 0) membuat subset yang menyaring semua hierarki SubsetCreatebyMDX (SubsetName, StringMDX) EndIf ii 1 end If (ViewExists (CubeName, ViewName) 0) ViewCreate (CubeName, ViewName) Endif i 1 loop melalui semua dimensi kubus sementara Tabdim (CubeName, i) ltgt) ViewSubsetAssign (CubeName, ViewName, tabdim (CubeName, i), SubsetName) ii 1 akhir LihatExtractSkipCalcsSet (CubeName, ViewName, 1) ViewExtractSkipZeroesSet (CubeName, ViewName, 1) -------- ------ epilog cleanup view ViewDestroy (CubeName, ViewName) i 1 loop melalui semua dimensi kubus sementara (tabdim (CubeName, i) ltgt) SubsetDestroy (tabdim (CubeName, i), SubsetName) ii 1 end Creating Dynamic Subset di Applix TM1 dengan MDX - A Primer Tentang Dokumen Ini MDX Primer ini dimaksudkan untuk berfungsi sebagai simpul. Pengantar untuk membuat himpunan dimensi dinamis menggunakan MDX di TM1. Ini berfokus pada pemberian contoh kerja daripada mencoba menjelaskan teori lengkap MDX dan memastikan untuk mencakup fitur yang paling berguna bagi pengguna TM1. TM1 saat ini (sebanyak 9.0 SP3) hanya memungkinkan pengguna menggunakan MDX untuk membuat himpunan dimensi dan tidak untuk menentukan tampilan kubus. Ini berarti bahwa penggunaan MDX di TM1 seringkali sangat berbeda dalam hal sintaks dan niat baik dari contoh yang ditemukan di buku dan di internet. Karena MDX (Multi-Dimensional eXpressions) adalah bahasa query standar industri untuk database OLAP dari Microsoft, ada banyak referensi dan contoh yang dapat ditemukan di Internet, namun ingatlah bahwa TM1 tidak mendukung setiap aspek bahasa dan menambahkan beberapa Fitur unik tersendiri. Hal ini dapat menyulitkan penggunaan contoh yang ada di web, sedangkan semua contoh dalam dokumen ini dapat disalin dan disisipkan ke TM1 dan akan dijalankan tanpa modifikasi, dengan asumsi Anda memiliki contoh model mini yang dibuat seperti yang didokumentasikan nanti. Dokumen lengkap hanya dengan satu halaman HTML di sini. Apa itu subset dinamik berbasis MDX di TM1 Subset dinamis adalah daftar yang tidak tetap, statis, namun berdasarkan permintaan yang dievaluasi ulang setiap kali subset digunakan. Sebenarnya, MDX dapat digunakan untuk membuat subset statis dan contoh ditunjukkan di bawah ini, namun ini tidak mungkin berguna atau umum. Beberapa contoh himpunan bagian dinamis yang berguna mungkin merupakan daftar semua produk tingkat dasar daftar 10 pelanggan teratas kami dengan marjin kotor daftar pengiriman barang terlantar semua pusat biaya yang belum menyerahkan anggaran mereka. Intinya, daftar ini (subset) mungkin berbeda dari kedua ke dua berdasarkan struktur atau data di TM1. Misalnya, segera setelah cabang baru ditambahkan ke Eropa, subset Cabang Eropa akan segera berisi cabang baru ini, tanpa intervensi manual yang diperlukan. MDX adalah bahasa query yang digunakan untuk menentukan himpunan bagian ini. MDX adalah bahasa query standar industri untuk database multi-dimensi seperti TM1, walaupun TM1 hanya mendukung subset tertentu (alasan pun) dari keseluruhan bahasa dan menambahkan beberapa fitur uniknya sendiri juga. Bila Anda menentukan subset menggunakan MDX dan bukan subset standar, TM1 menyimpan definisi ini daripada kumpulan yang dihasilkan. Ini berarti definisi atau kueri dijalankan ulang setiap kali Anda melihatnya tanpa pengguna atau administrator yang perlu melakukan apa saja. Jika database telah berubah dalam beberapa cara maka Anda mungkin mendapatkan hasil yang berbeda dari waktu terakhir Anda menggunakannya. Misalnya, jika subset didefinisikan sebagai anak-anak Cabang Pantai Barat dan ini pada awalnya mengembalikan Oakland, San Francisco, San Diego saat pertama kali ditetapkan, mungkin nanti akan kembali ke Oakland, San Francisco, San Diego, Los Angeles setelah LA memiliki Telah ditambahkan ke dimensi sebagai anak dari Cabang Pantai Barat. Inilah yang kita maksud dengan dinamika perubahan hasilnya. Alasan lain yang dapat menyebabkan subset berubah adalah bila didasarkan pada nilai dalam sebuah kubus atau atribut. Setiap hari di surat kabar penggerak pasar saham terbesar tercatat, seperti 10 besar dalam hal kenaikan harga saham. Dalam model TM1, ini akan menjadi subset yang melihat ukuran perubahan harga saham dan jelas akan menghasilkan jumlah anggota 10 anggota yang berbeda setiap hari. Bagian yang terbaik adalah subset akan memperbarui hasilnya secara otomatis tanpa ada pekerjaan yang dibutuhkan oleh pengguna. Cara membuat subset berbasis MDX di TM1 Langkah dasar yang sama dapat diikuti dengan semua contoh dalam dokumen ini. Umumnya contohnya bisa copy-and-paste ke Expression Window dari Subset Editor dari dimensi yang sering dipertanyakan Product. Perhatikan bahwa tidak penting dimensi kubus mana yang sedang digunakan oleh Anda akan mendapatkan hasil yang sama apakah Anda membuka editor subset dimensi dari dalam tampilan kubus, pohon kubus di Server Explorer atau pohon dimensi di Server Explorer. Untuk melihat dan mengedit kueri MDX Anda harus dapat melihat Jendela Ekspresi di Editor Subset. Untuk mengaktifkan dan mematikan jendela ini pilih View Expression Window. Anda sekarang bisa mengetik (atau menempelkan) kueri Anda ke dalam Expression Windows ini dan tekan tombol Update untuk melihat hasilnya. Cara membuat subset statis dengan MDX Sebuah subset statis adalah bahasa yang tidak pernah bervariasi dalam kontennya. Permintaan ini akan mengembalikan 3 anggota yang sama (Discount Loan, Term Loan and Retail) setiap saat. Jangan khawatir, itu akan lebih seru dari sini. Cara membuat subset dinamis dengan MDX TM1 hanya mendukung sejumlah fungsi dari spesifikasi MDX yang lengkap. Versi TM1 yang berbeda akan mendukung fungsi yang berbeda (dan berpotensi mendukungnya dengan cara yang berbeda). Kumpulan fungsi yang valid untuk versi TM1 yang Anda gunakan dapat ditemukan di file Help utama, di bawah Reference Function MDX Function Support. Sebelum mencoba menulis sebuah query baru, pastikan itu didukung, dan walaupun beberapa fungsi yang tidak terdaftar tentu saja bekerja, mereka harus menggunakan risiko Anda sendiri. Pesan kesalahan standar yang berarti fungsinya benar-benar tidak didukung oleh versi TM1 Anda, Gagal mengkompilasi ekspresi. Satu kata peringatan: pada dasarnya, hasil subset dinamis bisa berubah. Bila menyertakan himpunan bagian dinamis dalam tampilan, proses, fungsi SUBNM, dan sebagainya, pertimbangkan baik-baik hasil potensi masa depan, terutama jika subset suatu hari nanti kosong. Dua metode yang paling umum untuk pergi tentang benar-benar menciptakan subset dinamis adalah untuk menciptakan mereka dengan tangan atau menggunakan TurboIntegrator. Dengan tangan . Anda bisa mengetik (atau menempelkan) query ke Expression Window seperti yang dijelaskan sebelumnya, atau Anda dapat memilih Tools Record Expression (dan kemudian Stop Recording when done) untuk menyalakan semacam perekam video. Anda kemudian dapat menggunakan fitur normal editor subset (misalnya memilih menurut tingkat, mengurutkan turun, dll.) Dan perekam ini akan mengubah tindakan Anda menjadi ekspresi MDX yang valid. Ini adalah cara yang bagus untuk melihat beberapa contoh sintaks yang valid, terutama untuk kueri yang lebih kompleks. Bila Anda telah merekam sebuah ekspresi dan memilih Stop Recording TM1 akan meminta Anda untuk mengonfirmasi jika Anda ingin melampirkan ekspresi dengan subset - pastikan untuk mengatakan Ya dan centang kotak centang Save Expression saat menyimpan subset yang dihasilkan, jika tidak, hanya daftar statis Dari hasil yang disimpan, bukan query dinamis itu sendiri. Menggunakan TurboIntegrator Hanya satu baris, dengan menggunakan SubsetCreateByMDX, diperlukan untuk membuat dan menentukan subset. Anda perlu tahu apa permintaan yang Anda inginkan sebagai definisi sudah. Perhatikan bahwa kueri dapat dibangun di skrip TI menggunakan rangkaian teks sehingga dapat menggabungkan variabel dari skrip Anda dan memungkinkan kueri panjang untuk dibangun secara bertahap yang mudah dibaca dan dipelihara. SubsetCreatebyMDX (Produk Basis,, 0)) Semua subnet MDX buatan TI disimpan sebagai kueri MDX yang dinamis secara otomatis dan bukan sebagai daftar statis. Perhatikan bahwa, setidaknya sampai dengan TM1 v9.0 SP3, subset berbasis MDX tidak dapat dihancurkan (SubsetDestroy) jika digunakan oleh tampilan publik, dan tidak dapat dibuat ulang dengan menggunakan perintah SubsetCreateByMDX kedua. Oleh karena itu sulit untuk mengubah himpunan berbasis MDX menggunakan TI. Meskipun sifat dinamis dari definisi subset mungkin membuatnya agak tidak mungkin Anda benar-benar ingin melakukan ini, penting untuk diingat. Jika Anda perlu mengubah beberapa aspek kueri (misalnya TM1FilterByPattern dari tahun 2006-12 sampai 2007-01 Anda mungkin harus menentukan kueri untuk menggunakan parameter eksternal, seperti yang didokumentasikan dalam dokumen ini. Ini akan memiliki dampak kinerja kecil lebih sederhana. Versi hardcoded.Juga, filter terhadap nilai sebuah kubus dengan SubsetCreateByMDX di tab Epilog misalnya, 0), Test (Tindakan Posting). Tidak akan berhasil jika nilai-nilainya terjadi pada tab Data. Anda perlu menjalankan perintah SubsetCreateByMDX dalam proses TI berikutnya. Perhatikan bahwa TI memiliki batas 256 karakter untuk menentukan himpunan bagian MDX, setidaknya sampai v9.1 SP3, yang bisa sangat membatasi. Sintaks dan Tata Letak Sebuah kueri dapat dipecah melalui beberapa baris sehingga lebih mudah dibaca. Sebagai contoh: FILTER (, 0), Test2. (Rate Measures.Rate) 19) lebih mudah dibaca daripada memiliki keseluruhan query dalam satu baris. Bagian filter sebenarnya lebih mudah dibaca dan dimodifikasi sekarang dengan memilikinya pada sebuah garis dengan sendirinya. Perhatikan bahwa referensi kepada anggota biasanya memiliki nama dimensi sebagai awalan. Misalnya, sebenarnya nama dimensi bersifat opsional namun hanya jika nama anggota (Ritel dalam kasus ini) benar-benar unik di dalam keseluruhan server - yaitu tidak ada kubus, dimensi, atau anggota dengan nama persisnya. Misalnya, ini adalah permintaan yang sama dengan nama dimensi yang diabaikan: Yang akan bekerja dalam konteks aplikasi sampel yang digunakan oleh dokumen ini namun berisiko dalam aplikasi dunia nyata. Pesan kesalahan yang diterima saat lupa menentukan awalan akan menjadi seperti, Level atau member name Ritel ambigu: ditemukan dalam dimensi dan kemudian berlanjut ke daftar berbagai dimensi di mana nama anggota yang tidak unik dapat ditemukan, yang sangat bermanfaat. Oleh karena itu pastinya paling aman dan paling performant untuk selalu menggunakan awalan dimensi. Penggunaan tanda kurung siku terkadang tampak sedikit sembarangan saat membaca contoh kueri MDX. Faktanya adalah bahwa nama objek OLAP (misalnya nama kubus, nama dimensi, nama anggota) harus disertakan dalam tanda kurung siku hanya jika berisi spasi, dimulai dengan nomor atau merupakan kata yang dipesan MDX (misalnya Pilih). Namun, terkadang bisa lebih mudah untuk memutuskan untuk selalu menggunakan tanda kurung sehingga permintaan serupa dapat dibandingkan berdampingan lebih mudah. Definisi pasti anggota TM1 hampir selalu dinyatakan sebagai Nama Nama Dimensi. Nama dan tidak lebih. Dalam produk lain yang juga menggunakan MDX sebagai bahasa kueri (seperti Microsoft Analysis Services), Anda mungkin memperhatikan bahwa kueri menentukan jalur lengkap dari nama dimensi melalui hierarki sampai ke nama anggota, misalnya: Date.2009.Q1.Feb .Week 06 Hal ini juga dapat ditulis sebagai Date.2009Q1FebWeek 06 Alasannya adalah bahwa produk lain mungkin tidak mengharuskan setiap nama anggota menjadi unik karena masing-masing anggota memiliki konteks (keluarganya) agar dapat dikenali secara unik, oleh karena itu Mereka perlu mengetahui secara pasti mana 06 06 yang diperlukan karena mungkin ada yang lain (pada tahun 2008 misalnya dalam contoh di atas). TM1 mewajibkan semua nama anggota, pada tingkat manapun (dan dalam Alias) untuk benar-benar unik dalam dimensi itu. TM1 akan meminta Anda membuat Q1, Feb and Week 06 lebih eksplisit di tempat pertama (yaitu Q1 2009, Feb 2009, Week 06 2009) namun Anda kemudian bisa merujuk pada Date.Week 06 2009. Akhirnya, kasus (yaitu huruf besar Versus huruf kecil) tidak penting dengan perintah MDX (misalnya Filter atau FILTER, TOPCOUNT atau TopCount semuanya baik-baik saja) tapi sekali lagi Anda mungkin lebih memilih untuk mengadopsi hanya satu gaya sebagai standar agar lebih mudah dibaca. Contoh model yang digunakan Dalam dokumen ini banyak contoh query dinamis yang akan diberikan. Mereka semua bekerja (persis seperti yang ditulis, copy dan paste saja ke Jendela Ekspresi di Editor Subset dari dimensi yang sesuai untuk menggunakannya) pada sekumpulan batu dan dimensi sederhana yang ditunjukkan di bawah ini. Model ini sengaja sederhana tanpa karakteristik khusus sehingga Anda merasa mudah untuk mentransfer pekerjaan ke model Anda sendiri. Model yang digunakan meliputi 1 dimensi utama, Product, dimana sebagian besar kueri bekerja ditambah 3 kubus: Test, Test2 dan Test3. Nilai data dalam kubus akan bervariasi selama pengujian (Anda ingin mengubah nilai dan menjalankan ulang kueri untuk memastikan hasilnya berubah dan benar) namun tangkapan layar di bawah menunjukkan struktur kubus dan dimensi cukup baik sehingga Anda dapat dengan cepat menciptakan kembali Mereka atau bagaimana menggunakan model Anda sendiri sebagai gantinya. To simplify the distribution of this document there is no intention to also distribute the actual TM1 model files. Note that the main dimension used, Product, featured ragged, and multiple, hierarchies. TM1SubsetAll, Members, member range The basis for many queries, this returns (almost, see below) the entire dimension, which is the equivalent of clicking the All button in the Subset Editor. TM1SUBSETALL( Product ) Note that only the final instance in the first hierarchy of members that are consolidated multiple times is returned. The Members function, on the other hand, delivers the full dimension, duplicates included: Product.Members A range of contiguous members from the same level can be selected by specifying the first and last member of the set you require with a colon between them. This example returns Jan 1st through to Jan 12th 1972. Select by Level, Regular Expression (Pattern) and Ordinal Selecting members based on their level in the dimension hierarchy (TM1FilterByLevel) or by a pattern of strings in their name (TM1FilterByPattern) can be seen easily by using the Record Expression feature in the subset editor. The classic all leaf members query using TM1s level filtering command TM1FilterByLevel: , 0) Select all the leaf members which match the wildcard HC i.e. that have H and C as the third and fourth characters from the end of their name. , 0), HC) The reason that these functions start with TM1 is that they are not standard MDX commands and are unique to TM1. There are two main reasons why Applix will implement such unique functions: to add a feature that is present in standard TM1 and users will miss if it is not there or because standard TM1 has the same feature as MDX but has historically implemented it slightly differently to MDX and therefore would, again, cause users problems if it was only implemented in the standard MDX way. In these two cases, TM1FilterByPattern brings in a function commonly used by TM1 users that is lacking in MDX, while TM1FilterByLevel exists because TM1 has, since its launch in 1984, numbered consolidation levels starting at zero for the leaf level rising up the levels to the total members, while Microsoft decided to do it the exact opposite way. In certain situations it is useful to use the standard MDX levels method and this is also available with the Levels function. It allows you return the members of a dimension that reside at the same level as a named member, just bear in mind that standard MDX orders the levels in terms of their distance from the top of the hierarchy and not the bottom as TM1. This example returns all the members at the same level as the Retail member: Which, although Retail is a high level consolidation, returns an N: item (Product Not Applicable) in the dimension because this rolls straight up into All Products as does Retail so they are considered to be at the same level. To filter the dimension based on a level number you need to use the .Ordinal function. This is not documented as being supported in the Help file, and did not work in 8.2.7, but appears to work in 9.0 SP3 and 9.1.1.36 at least. This example returns all the members at Level 1: , Product.CurrentMember.Level.Ordinal 1) This example would return all members not at the same level as Discount Loan. , Product.CurrentMember.Level.Ordinal TM1Sort, TM1SortByIndex and Order TM1Sort is the equivalent of pressing one of the two Sort Ascending or Sort Descending buttons in the subset editor i.e. sort alphabetically. TM1SortIndex is the equivalent of pressing one of the two Sort by index, ascending or Sort by index, descending buttons in the subset editor i.e. sort by the dimension index (dimix). Order is a standard MDX function that uses a data value from a cube to perform the sort. For example, sort the list of customers according to the sales, or a list of employees according to their length of service. Sort the whole Product dimension in alphabetically ascending order. , ASC) Sort the leaf members of the dimension according to their Amount values in the Test cube from highest downwards. ORDER( ,0) . Test.(Posting Measures.Amount), BDESC) Note that using BDESC instead of DESC gives radically different results. This is because BDESC treats all the members across the set used (in this case the whole dimension) as being equal siblings and ranks them accordingly, while DESC treats the members as still being in their family groups and ranks them only against their own direct siblings. If youre not sure what this means and cant see the difference when you try it out, then just use BDESC Order can also use an attribute instead of a cube value. In this example the AlternateSort attribute of Product is used to sort the children of Demand Loan in descending order. It is a numeric attribute containing integers (i.e. 1, 2, 3, 4, etc) to allow a completely dynamic sort order to be defined: , Product.AlternateSort, DESC) TopCount and BottomCount A classic Top 10 command: , 0), 10, Test.(Posting Measures.Amount) ) By omitting a sort order it sorts in the default order (which has the values descending in value and breaks any hierarchies present). A Top 10 query with an explicit sort order for the results. ,0), 10, test.(Posting Measures.Amount)), test.(Posting Measures.Amount), BDESC) BDESC means to break the hierarchy. Note how the chosen measure is repeated for the sort order. Although the same measure is used in the sample above you could actually find the top 10 products by sales but then display them in the order of, say, units sold or a Strategic Importance attribute. This is the top 10 products based on Test2s Rate values, not ordered so will be sorted according to the values in Test2. ,0), 10, Test2.(Rate Measures.Rate)) This is the top 10 products based on test2s data in the Rate measure, ordered from 10 through 1. ,0), 10, test2.(Rate Measures.Rate)), test2.(Rate Measures.Rate), ASC) TopCount automatically does a descending sort by value to get the TOP members. If this is not desired, you might want to use the Head function (detailed below) instead. BottomCount is the opposite of TopCount and so is used to find the members with the lowest values in a cube. Beware that the lowest value is often zero and if that value needs to be excluded from the query you will need to refer to the section on the Filter function later in this document. A Bottom 10 query with an explicit sort order for the results. ,0), 10, test.(Posting Measures.Amount)), test.(Posting Measures.Amount), BASC) Further reading: TopSum, TopPercent and their Bottom equivalents are useful related functions. Filter, by values, strings and attributes The FILTER function is used to filter the dimension based on some kind of data values rather than just the members and their hierarchy on their own. This data might be cube data (numeric or string) or attribute data. This requires a change of thinking from straightforward single dimensions (lists with a hierarchy and occasionally some attributes) to a multi-dimensional space, where every dimension in these cubes must be considered and dealt with. This example returns the leaf members of Product that have an Amount value in the Test cube above zero. , 0), Test.(Posting Measures.Amount) gt 0 ) Since the Test cube only has 2 dimensions Product and Posting Measures this is a simplistic example. Most cubes will have more than just the dimension being filtered and the dimension with the filter value in. However, it is simple to extend the first example to work in a larger cube. This example returns the leaf members of Product that have an Amount value for All Entities in the Test3 cube above zero. , 0), Test3.(Entity.All Entities,Posting Measures.Amount) gt 0 ) As you can see from the above, simply include all the requisite dimension references inside the round brackets. Usually you will just need a specific named member (e.g. All Entities). If the dimension is omitted then the CurrentMember is used instead which is similar to using dimension (i.e. for each) in a TM1 rule, and could return different results at a different speed. Instead of just using a hardcoded value to filter against (zeroes in the examples above), this example returns all products with an amount in the Test cube greater than or equal to the value in the cell MidasJCFI, Amount. , 0), Test.(Posting Measures.Amount) gt Test.(Product.MidasJCFI,Posting Measures.Amount) ) This query returns the products that have a Rate value in Test2 greater than MidasJXCOs Rate in Test2. Now, this query just returns a set of products its up to you which cube you display these products in i.e. you can run this while browsing Test and therefore return what looks like an almost random set of products but the fact is that the query is filtering the list of products based on data held in Test2. This may not immediately appear to be useful but actually it is, and can be extremely useful for example display the current years sales for products that were last years worst performers. If the data for two years was held in different cubes then this would be exact same situation as this example. There are often many potential uses for displaying a filteredfocused set of data in Cube B that is actually filtered based on data in Cube A. , 0), Test.(Posting Measures.Amount) gt Test2.(Product.MidasJXCO,Rate Measures.Rate) ) As detailed elsewhere, Tail returns the final member(s) of a set. An example of when it is handy when used with Filter would be for finding the last day in a month where a certain product was sold. The simple example below initially filters Product to return only those with an All Entity Amount gt 0, and then uses tail to return the final Product in that list. , 0), Test3.( Entity.All Entities, Posting Measures.Amount) gt 0 )) Note: with the other cubes having more dimensions than does Test the current member is used (each), not All so whether you want each or All you should write this explicitly to be clearer. You can even filter a list in Cube1 where the filter is a value in one measure compared to another measure in Cube1. This example returns the Products with an amount in the Test cube above zero where this Amount is less than the value in Count. , 0), (Test.Posting Measures.Amount 0 ) This example returns all the leaf products that have an Amount in Entity Not Applicable 10 greater than the Amount in Entity Not Found, in the Test3 cube. Not very useful but this was the only example cube we had to work with, but it would be very useful when comparing, say, Actual Q1 Sales with Budget, or finding out which cost centres Q2 Costs were 10 higher than Q1. Later in this document we will see how to take that 10 bit and make it a value from another cube, thus allowing administrators, or even end users, to set their own thresholds. , 0), test3.(Entity.Entity Not Applicable, Posting Measures.Amount) 1.1 gt test3.(Entity.Entity Not Found, Posting Measures.Amount)) Filtering for strings uses the same method but you need to use double quotes to surround the string. For example, this query returns products that have a value of bob in the Test2 cube against the String1 member from the StringTest dimension. Note that TM1 is case-insensitive. , 0), Test2.(StringTest.String1) bob ) Filter functions can be nested if required, although the AND or INTERSECT functions may be useful alternatives. The limit to the number of characters that an MDX subset definition can sometimes be, 256, is too restricting for many data-based queries. When trying to shoehorn a longer query into less characters there are a few emergency techniques that might help: consider whether you need things like TM1FILTERBYLEVEL, 0 (it might well be that the filter would only return members at the leaf level by definition anyway) whether the dimension name prefix can be removed if the member is guaranteed to be unique remove all spaces lookup cubes are not for end users so maybe you could shorten some names (cubes, dimension, members) drastically whether there are alternative functions with shorter syntaxes that return the same result - e.g. an INTERSECT or AND versus a triple FILTER. Finally, if it really is vital to get a long query working then you can build up the final result in stages i.e. put some of the filtering into Subset1, then use Subset1 as the subject of Subset2 which continues the filtering, etc. Parent, Children, FirstChild, LastChild, Ancestors, Descendants, DrillDownLevel and TM1DrilldownMember Children returns the set of members one level below a named parent. FirstChild returns the first child one level below a named parent. Returns Call Participation Purchased. LastChild returns the last child one level below a named parent. This is excellent for finding the last day in a month, since they can vary from 28 to 31. Another example is when a consolidation is set up to track a changing set of members (e.g. Easter, or Strategic Customers). Returns Term Participation Purchased. Parent returns the first parent of a given member. If a member has more than one parent, and the full unique path to the member is not specified then the first parent according to the dimension order is returned. Returns Bonds. Would force TM1 to return the second parent, External Bonds. Descendants returns the named parent and all of its descendant children i.e. the hierarchy down to the leaf level: TM1DrilldownMember returns the same thing as descendants: , ALL, RECURSIVE ) DrillDownLevel just returns the parent and its immediate children: ) DrillDownLevel can be extended with a parameter to say which level to return the members from, rather than the level immediately below, but this doesnt appear to work in TM1 v9.0 SP2 through to 9.1.1.36. The common requirement to return a list of just leaf-level descendants of a given consolidated member just needs a level filter applied to the TM1DrillDownMember example above: ,ALL,RECURSIVE), 0) Or: , 0) Ancestors is like a more powerful version of Parent it returns a set of all the parents of a member, recursively up though the hierarchy including any multiple parents, grandparents, etc. Returns 2006 October, 2006 Q4, 2006 H2, 2006, All Dates. Lag, Lead, NextMember, PrevMember, FirstSibling, LastSibling, Siblings and LastPeriods Lags and Leads are the equivalent of DnextDprev. will return 2006-10-04. Lead(n) is the same as Lag(-n) so either function can be used in place of the other by using a negative value, but if only one direction will ever be needed in a given situation then you should use the correct one for understandabilitys sake. Note that they only return a single member so to return the set of members between two members you can use the lastperiods function. Equally you can use NextMember and PrevMember when you only need to move along by 1 element. Or: To return the 6 months preceding, and including, a specific date: Or: LastPeriods(6, Date.2006-10-03) Both of which work because LastPeriods is a function that returns a set, and TM1 always requires a set. Curly braces convert a result into a set which is why many TM1 subset definitions are wrapped in a pair of curly braces, but in this case they are not required. This will return the rest (or the ones before) of a dimensions members at the same level, from a specified member. Despite its name LastPeriods works on any kind of dimension: Siblings are members who share a common parent. For example, a date of 14th March 2008 will have siblings of all the other dates in March the first of which is the 1st March and the last of which is 31st March. A cost centre under West Coast Branches would have a set of siblings of the other west coast branches. The FirstSibling function returns the first member that shares a parent with the named member. For example: Returns MidasHCBK. While: Returns MidasHSFI. The siblings function should return the whole set of siblings for a given member. TM1 9.0 SP2 through to 9.1.2.49 appear to give you the entire set of members at the same level (counting from the top down) rather than the set of siblings from FirstSibling through to LastSibling only. Filtering by CurrentMember, NextMember, PrevMember, Ancestor and FirstSibling This example returns the members that have an Amount value in the Test cube above 18. The Product.CurrentMember part is optional here but it makes the next example clearer. , 0), Test.(Product.CurrentMember, Posting Measures.Amount) gt 18 ) This query then modifies the previous query slightly to return members where the NEXT member in the dimension has a value above 18. In practice this is probably more useful in time dimensions. , 0), Test.(Product.CurrentMember.NextMember, Posting Measures.Amount) gt 18 ) This can then be improved to returning members where the next member is greater than their amount. , 0), Test.(Product.CurrentMember.NextMember, Posting Measures.Amount) gt Test.(Product.CurrentMember, Posting Measures.Amount) ) In addition to NextMember, PrevMember can also be used as could lags and leads. The simple, but unsupported as of 9.1.1.89, Name function allows you to filter according to the name of the member. As well as exact matches you could find exceptions, less-thans and greater-thans, bearing in mind these are alphanumeric comparisons not data values. This example returns all base members before and including the last day in January 1972. ,0), Date.CurrentMember.Name For example, this could be a useful query even a dimension not as obviously sorted as dates are: ,0), Product.CurrentMember.Name which returns all base members before MidasJ in terms of their name rather than their dimension index. Parent returns the first parent of a given member: Used with Filter you can come up with another way of doing a children of query: ,0), Date.CurrentMember.Parent.Name 1972 - January) Ancestor() can be used instead of Parent if desired. This example returns base-level product members whose first parents have a value above zero, in other words a kind of family-based suppress zeroes: a particular product might have a value of zero but if one if its siblings has a value then it will still be returned. , 0), Test.(Ancestor(Product.CurrentMember,0), Posting Measures.Amount) gt 0 ) This example filters the products based on whether they match the Amount value of MidasHCBK. , Test.(Ancestor(Product.CurrentMember,0), Posting Measures.Amount) Test.(Product.MidasHCBK, Posting Measures.Amount) ) This example uses FirstSibling to filter the list based on whether a products value does not match that products First Sibling (useful for reporting changing stock levels or employee counts over time, for example, things that are usually consistent). , 0), Test.(Ancestor(Product.CurrentMember,0), Posting Measures.Amount) Filtering by Attributes and logical operators This returns members that match a certain attribute value using the Filter function. , Product.Category Customer Lending) This example looks at multiple attribute values to return a filtered list: FILTER( , ( (Product.CategoryCustomer Lending OR Product.TypeDebit) AND (Product.Internal Deal Filtering by level, attribute and pattern are combined in the following example: ,0), Product.Internal Deal Yes), ID) Head, Tail and Subset Where TopCount and BottomCount sort the values automatically and chop the list to leave only the most extreme values, Head combined with Filter works in a similar manner but Head then returns the FIRST members of the filtered set in their original dimension order. These queries simply return the first and last members of the Product dimension as listed when you hit the All button: This returns the actual last member of the whole Product dimension according to its dimix: , ALL, RECURSIVE ), ASC)) An example of Tail returning the last member of the Customer Lending hierarchy: , ALL, RECURSIVE )) An example of Head returning the first 10 members (according to the dimension order) in the product dimension that have an Amount in the Test cube above zero. , 0), Test.(Posting Measures.Amount) gt 0 ), 10) With both Head and Tail the ,10 part can actually be omitted (or just use ,0) which will then return the first or last member. This returns the last (in terms of dimension order, not sorted values) product that had an amount gt 0 in the Test cube. , 0), Test.(Posting Measures.Amount) gt 0 )) One example of when this is useful over TopCount or BottomCount i.e. when sorting the results would be detrimental - would be to return the last day the year when a certain product was sold. Subset is closely related to Head and Tail, and can actually replicate their results, but is additionally capable of specifying a start point and a range, similar in concept to substring functions (e.g. SUBST) found in other languages, though working on a tuple of objects not strings. The equivalent of Head, 10 would be: , 1, 10) But Subset would also allow us to start partitioning the list at a point other than the start. So for example to bring in the 11th 20th member: , 11, 10) Note that asking for more members than exist in the original set will just return as many members as it can rather than an error message. Union joins two sets together, returning the members of each set, optionally retaining or dropping duplicates (default is to drop). To create a list of products that sold something both in this cube and in another (e.g. last year and this): FILTER( , 0), Test.(Posting Measures.Amount) gt 0 ) , FILTER( , 0), Test3.(Posting Measures.Amount, Entity.All Entities) gt 0 ) ) Intersect returns only members that appear in both of two sets. One example might be to show products that performed well both last year and this year, or customers that are both high volume and high margin. The default is to drop duplicates although , ALL can be added if these are required. This example returns leaf Product members that have an Amount gt 5 as well as a Count gt 5. INTERSECT( FILTER( , 0), Test.(Posting Measures.Amount) gt 5 ) , FILTER( , 0), Test.(Posting Measures.Count) gt 5 ) ) Except and Validating Dimension Hierarchies The function takes two sets as its mandatory parameters and removes those members in the first set that also exist in the second . In other words it returns only those members that are not in common between the two sets, but note that members that are unique to the second set are not included in the result set. Except is a useful function in a variety of situations, for example when selecting all the top selling products except for 1 or 2 you already know are uninteresting or irrelevant, or selecting all the cost centres with high IT costs except for the IT department. The simplest example is to have a first set of 2 members and a second set of 1 of those members: EXCEPT ( , ) Which returns MidasJCFI, the only member not in common between the two sets. For the purposes of maximum clarity in the rest of this section only, we will drop the Product reference and trust that these product names are uniquely in the Product dimension on our server. The optional extra ALL parameter allows duplicates to remain prior to the determination of the difference i.e. matching duplicates within the first set are discarded, while non-matching duplicates are retained. A simple example where there are duplicate members in the first set: EXCEPT ( , ) Returns MidasJCCO (because duplicates are discarded without ALL), while: EXCEPT ( , . ALL) Returns MidasJCCO, MidasJCCO (as ALL allows the duplicate MidasJCCO members to be retained). Note that ALL has no effect on the following query as MidasJCFI is the only member not in common between the two sets and so this is the only result either way: EXCEPT ( , ) Returns MidasJCFI. Remember, the members in the first set that also exist in the second are eliminated, hence (both instances of) MidasJCCO is eliminated So if you were to ask for EXCEPT( , ) then the final set would be without ALL and with ALL. Because matching duplicates in the first set are eliminated first (that is, duplicates in the first set that match a member in the second set), Apples (the only member in the second set that matches a pair of duplicates in the first set, is eliminated. To put the fruit down and return to our demo model we can write the equivalent query against products: EXCEPT ( , ) Returns just one MidasJCFI (the equivalent of Oranges above) while: EXCEPT ( , . ALL) Returns two instances of MidasJCFI. These results are due to the fact that, in the example with ALL, MidasJCCO is eliminated due to a matching member in set 2, while MidasJCFI is reduced to 1 instance due to the lack of ALL. MidasHDBK has no impact because it could not be subtracted from set 1 as it was not in set 1. When ALL was used in the second example, the two MidasJCCO members were still eliminated due to a match in set 2, and MidasHDBK was still irrelevant, but this time the two MidasJCFI members were left alone due to the ALL allow ing duplicates. Note: the following section does not work in v9.1 SP2, but does work in v9.0. Jarak tempuh Anda mungkin berbeda. A particularly clever use of Except is to check a TM1 dimension for a valid structure. A simple query can return a list of members that do not eventually roll up into a particular consolidated member. This could be included in a TI process to automate the consistency checking of dimensions after an update. This example returns all the members in the dimension that do not roll up into All Products: EXCEPT ( TM1SUBSETALL( Product ), TM1DRILLDOWNMEMBER( , ALL, RECURSIVE )) Modifying this slightly makes it return base-level members that do not roll up into All Products: EXCEPT ( TM1FILTERBYLEVEL(TM1SUBSETALL( Product ), 0), TM1FILTERBYLEVEL(TM1DRILLDOWNMEMBER( , ALL, RECURSIVE ), 0)) This query returns members that have been consolidated twice or more at some point under the given consolidated member this will often mean there has been an accidental double-count. EXCEPT ( TM1DRILLDOWNMEMBER( , ALL, RECURSIVE ), TM1SUBSETALL( Product ), ALL) It will return one instance of the multi-consolidated member for each time it is consolidated greater than once i.e. if it has been consolidated 4 times then it will return 3 instances. This is due to the fact that TM1SUBSETALL( Product ) will only return one instance of a member that has been consolidated multiple times while the TM1DrilldownMember function will return all the instances. You are reminded that Dimension.Member is actually a shortcut that usually works in TM1 but because the MDX specification allows for member names to be non-unique within a dimension the full address of a member is actually Dimension.Parent1.Parent2Member. Therefore more specific references to duplicate members may be needed, for example Product.Demand Loan.MidasHCBK will address a different instance of MidasHCBK than would Product.Discount Loan.MidasHCBK. In this case, with the Except function, they are treated as if they are different member names altogether. ToggleDrillState ToggleDrillState changes the default drill state from a returned set so if the first query returns a member in a hierarchy rolled up then it will drill it down, or vice versa. Using TM1 Subsets, TM1Member and TM1SubsetToSet One of the special features of using MDX with TM1 dimensions is that existing subsets can be used within the query for defining a new subset. This can be useful in allowing a simpler building block approach and for not having to repeat the same code over and over again and having to maintain it. Used throughout this section, Report Date is an existing subset in the Date dimension containing one leaf date member and test2 is an existing 20-member subset. Note that private subsets are used in preference to public subsets when there is one of each with the same name. This can allow a public subset to return different results based on the contents of different users private subsets, though inevitably with some issues with reliability of results. To simply return the member(s) of pre-existing Date subsets: Date.Report Date Or TM1SubsetToSet(Date, Report Date) The first syntax may be shorter and more convenient but bear in mind, as per the TM1 help file, Since the same syntax ( .IDENTIFIER ) is used for members and levels, a subset with the same name of a member or a level will never be instantiated. The second syntax on the other hand will happily work with any subset names even if they are named the same as a cube or dimension. To return the first member of the test2 subset: To return a valid cube reference within a more complex query: TM1Member(Date.Current Date.Item(0), 0) For example: , 1), Reconciliation.(Entity.All Entities,TM1Member(Date.Current Date.Item(0),0),Reconciliation Measures.Transaction Balance) To start with the fourth item (.Item counts from zero) in the test2 subset and then return the preceding 14 members from the whole dimension, including the fourth item: This example returns the one date in Report Date and the next 13 periods, sorted with the earliest date first a moving 2-week reporting window which just needs the Report Date subset to be maintained. This query uses another subset, Strategic Products, as a building block and finds the Top 5 members within it, even though this ranking may well have been based on different values than the original subset was built on. For example, a subset that is already defined may list the 10 highest spending customer segments in terms of year to date actuals, and you then build a new subset that works with these 10 only to find the top 5 in terms of planned marketing spend next quarter. , 5, Test.(Posting Measures.Count)), Test.(Posting Measures.Count), BDESC) Heres a bigger example using TM1member and TM1SubsetToSet functions, in addition to various others. It takes the single period in the Current Date subset and returns the last day of the two preceding months. There would be several different ways of achieving the same result. union( tm1member(tm1subsettoset(Date, Current Date).item(0),0),1) )))), tm1member(tm1subsettoset(Date, Current Date).item(0),0),1) )))) ) Username and StrToMember It returns the TM1 username (or Windows domain username depending on the security system being used for example, GERJEREMY) of the user who runs the query. Note that you may need to give all users Read access to the Clients dimension and all its elements. It is not documented in the help file as being officially supported by TM1 but it is a standard MDX feature that appears to work in v8.3. However, since 8.4.3 until 9.1.2.49 it is reported as failing to automatically update when a new user uses the subset. This can be circumvented by running a frequent TI process that uses the subset as its datasource and the following line in Prolog (Workaround reported by Steve Vincent on the Applix Forum, 2nd August 2006): DIMENSIONSORTORDER(CLIENTS,BYNAME,ASCENDING,,) With this micro-process workaround set to run every few minutes a pseudo-dynamic result is possible. An actual solution to the problem should be tested for in your version if it is 9.1 or later. To save a dynamic subset it needs to be set up on the Clients dimension choose View Control Objects in Server Explorer to see this dimension. Once you have saved the public subset (e.g. as Current User) you can turn this option off again. Clients ), USERNAME ) As an alternative to the above method, and as a way of including the current username directly in queries use the StrToMember function which converts a plain string into a valid MDX member reference. Clients.USERNAME) Either way the subset can then be referred to on Excel spreadsheets, VBA processes and, as it is simply a standard TM1 subset, in TM1 Websheets. As a non-MDX alternative v9.1.2.49 introduced a TM1User(servername) worksheet function which could be used in some circumstances. Data-based queries, Filter, Sum, Avg and Stdev Sometimes it is not adequate to simply use a single value in a query you need to consider a combination of values. It might be that this combination is only needed for one or two queries, though, so it is not desirable to calculate and store the result in the cube for all to see. Therefore it is more logical to quickly calculate the result on the fly and although this is then repeated every time the subset is used, it is still the preferred choice. The function Sum, Avg and Stdev are therefore useful for things that are only needed occasionally or by a limited number of users and means that the actual cube is thus smaller and more efficient. SUM, as it might appear, sums up a set of numbers. This allow the aggregation of members not already consolidated in the model. This example checks the Test3 cube for products whose Amounts in the on-the-fly-consolidation of 2 entities are greater than 50. , 0), SUM( , Test3.(Posting Measures.Amount) ) gt 50 ) AVG calculates the average value of a set. Note how empty (zero) cells are not included by the AVG function so the resulting average value might be higher than you expected. This example returns a list of leaf products that have an Amount value in the Test cube higher than the average Amount value of all leaf products (or rather all non-zero leaf products). , 0), (Test.Posting Measures.Amount gt AVG( , 0), Test.(Posting Measures.Amount)) ) ) The set of members that AVG works on here (AVG , 0)) can be changed to something that doesnt match the list of members being filtered earlier in the query. For example, return a list of all leaf products that are higher than the average of the leaf descendants of the Customer Lending consolidation only. , 0), (Test.Posting Measures.Amount gt AVG( ,Test.(Posting Measures.Amount))) ) STDEV is the standard deviation function. It returns the average distance from each value in a set to the average of the set as a whole. In this way you can calculate how consistent or unpredictable a set of data is if all the values lie tightly around the average, or if the values vary to be extremely high and low. This example returns the outlying products whose Amount value in the Test cube is greater than the average value plus the standard deviation i.e. those products who have values that are above averagely above the average. , 0), ( Test.Posting Measures.Amount gt ( AVG( , 0), Test.(Posting Measures.Amount)) STDEV( , 0), Test.(Posting Measures.Amount) gt 0 ) ) ) ) ) Note that the AVG function automatically drops empty cells from the filtering set but STDEV does not so we have to apply our own filter. The above queries could be INTERSECTed for both sets of outliers in one subset, if required. Further reading: The MEDIAN function is also supported by TM1 and might be more appropriate than AVG (mean) in some circumstances. Using parameters in queries TM1Member will allow you to use parameterized references by using cube values as part of the query itself. For example if a UserParams cube was created with the Clients dimension (thus allowing concurrent usage by all users) which would hold various choices made by users as they used your application, then dynamic subsets could use those choices as part of their syntax, thus altering not just the thresholds for comparisons (we can see elsewhere in this document how to check if something is, say, above a certain threshold which is actually a value in another cube) but the actual thing that is queried in the first place. For example, this shows the descendants of a parent member, the name of which is held in the 2D UserParams cube at the intersection of the current username and SelectedParentDimix. TM1SUBSETALL( Product ).Item(UserParams.(StrToMember(Clients.USERNAME), UserParamMeasures.SelectedParentDimix)-1) . 0)) Below are screenshots showing the parameter cube which can be extended to hold various user-specific selections and then link them into dynamic subsets plus the other relevant screens. The Generate function applies a second set to each member of a first set, performing a union of the results. Duplicates are dropped by default but can be retained with ,ALL. Although Generate doesnt really do anything unique in itself it is a very useful way of shortening what would otherwise be long, laborious and error-prone queries. In the following example the top performing child product is returned for each member of Level 1 of the hierarchy: GENERATE( , 1), TopCount(Descendants(Product.CurrentMember, 1),1,Test.(Posting Measures.Amount))) ,Test.(Posting Measures.Amount) gt 0 ) Count and IIF Caveat: Note that IIF is not listed in the TM1 v9.0 SP2 help file as being supported so use at your own risk. Count returns the number of items in a set but this set can be a set of members or a set of data values. The result is, obviously, a number and is often returned in reports when used in MDX queries outside of TM1. When trying to use it do define a TM1 subset it can only be used as part of the query logic and not as a result itself. Count can be wrapped around a lot of the other MDX functions and so can be used in many different scenarios. One example is to count how many children a month has and, if there are 28, doing something that is unique to February. Although dimension subsets are usually a list of meaningful items in a business model and are included within application cubes, it is actually possible to have dimensions for administrator purposes only (that are never used to build cubes) which might indicate the state of something e.g. All Passwords Set, or Reconciliation Failed and the Count function could be used to define a subset that contains one of these members, which is information for the administrator only. IIF allows you to introduce some branching logic in your queries i.e. do one thing if this is true, otherwise do something else. You could use it to apply different statistical functions to members that have certain attributes. It works quite commonly with Count to allow one thing to happen if the count of something falls below a threshold, or do something else if not. This example performs either a Top 5 or a Top 10 on all base products Amounts in the Test cube, depending on whether the number of base level Products is 10 or less at the time the query is run. , 0), IIF(Count( , 0)) lt 10, 5, 10), Test.(Posting Measures.Amount) ) This example does a TopCount of the base products based on their Amount value in the Test cube where the number of items displayed is equal to the number of cells in the Test cube whose Amount value is anything other than zero. , 0), Count( Filter( , 0), Test.(Posting Measures.Amount) These are fairly pointless examples, practically speaking, but they show the syntax. Comments allow you to explain, to yourself andor to your users, what the query is trying to achieve, how it works, who wrote it or amended, etc. Use or (without the double quotes) to end a line with a comment or to have the comment on its own line. You can also use COMMENT (again without the quotes) to insert a comment in the middle of a line. You are also able to type anything after the command. This heavily-commented example returns all the products beginning MidasJ: Comment number 1 this is another comment -- and another comment , this is yet another comment MidasJ) You seem to be able to type what you like here, but treat with caution This does not work in version 8.2.7 but does in at least 9.0 and 9.1.1. AsciiOutput can help tracking what values are being used during execution of your TI processes. Keep in mind Asciioutput limitations: . it is limited to 1024 characters per line . it can deal only with strings . so you need to apply the NumberToString() function to all numeric variables that you would like to display like var3 in the example above. . it will openclose the file at every step of the TI. PrologMetadataDataEpilog that means if you use the same filename to dump your variables in any of these, it will be overwritten by the previous tab process. Hence you should use different filenames in each tab. . use DataSourceASCIIQuoteCharacter in prolog if you want to get rid of the quotes in output. . use DatasourceASCIIThousandSeparator to remove thousand separators. Alternatively you can use ItemReject if the record you step through is rejected, it will then be dumped to the error message ItemReject(var1var2) Dynamic Formatting It is possible to preformat dynamic slices by using the Edit Element Formats button in the subset editor. However that formatting is static and will not apply to new elements of a slowly changing dimension. Also it takes a long time to loadsave when you try to apply it to more than a few dozen elements. As an example, we will demonstrate how to dynamically alternate row colors in a TM1 report for a Customer dimension. Open subset editor for the Customer dimension .Select some elements and click Edit Elements Format .the Edit Element Formats worksheet opens, just click Save as colored row this creates the DimensionFormatStyles Customer dimension and the DimensionFormats Customer cube. Now we can modify this cube with our rules. open the rules editor for the DimensionFormatsCustomer cube, add this: alternate row colors for all elements colored row,Cond1Type S: 2 colored row,Cond1Formula1 S: MOD(ROW(),2)1 colored row,Cond1InteriorColorIndex S: 34 colored row,Cond2Type S: 2 colored row,Cond2Formula1 S: MOD(ROW(),2)0 colored row,Cond2InteriorColorIndex S: 2 To create a different style: .edit one element from the Edit element format, apply the desired formatting and save .note the new values of the measures in the DimensionFormatsCustomer cube for that element .reflect these changes in the rules to apply to all elements for that style There is also another rule-based formatting article on the Applix recommended practices website. dynamic SQL queries with TI parameters It is possible to use parameters in the SQL statement of Turbo Integrator to produce dynamic ODBC queries Here is how to proceed: 1. create your TI process 2. Advanced-gtParameters Tab, insert parameter p0 3. Advanced-gtProlog Tab add the processing code to define parameter p0 example: p0 CellGetS(cube,dim1,dim2. ) 4. save the TI process 5. open Data Source, add parameter p0 in WHERE clause example select from lib.table where name p0 DO NOT CLICK ON THE VARIABLES TAB AT ANY TIME 6. run, answer keep all variables when prompted If you need numeric parameters, there is a twist numeric parameters do not work (at least for TM1 9.x) example: select from lib.customer where purchase gt p0 will fail although p0 is defined as a numeric and quotes have been removed accordingly. But fear not, there is a simple workaround 1. proceed as above up to step 5 2. Advanced-gtProlog tab, at the bottom: strp0 NumberToString(p0) 3. Data Source tab, in the SQL statement replace p0 with CAST(strp0 as int) example: select from lib.customer where purchase gt p0 becomes select from lib.customer where purchase gt CAST(strp0 as int) clicking the preview button will not show anything but the process will work as you can verify by placing an asciioutput in the Advanced-gtData tab. The CAST function is standard SQL so that should be working for any type of SQL server. renaming elements renaming elements without activating aliases Yes we can The dimension editor and dimension worksheets cannot rename elements directly, so let me introduce you to SwapAliasWithPrincipalName . create a new alias new in the Dimension, by default the new elements are identical .change all required elements to their new names in that alias. Below we pad a zero in front of all elements create a new TI with the following line in the Prolog: The third parameter needs to be zero to execute the swap. If it has any other purpose, please editleave a comment. Make sure the associated dimension worksheet is updated if there is any. This was tested successfully under v. 9.0.3 and v. 9.4. This TI function is listed in the documentation. however there is no description of its function and syntax. Click on the RTFM FAIL tag to find out some other poorly documented or simply undocumented TM1 functions. dimension elements with a quote character () require a double quote to be interpreted correctly by the rules engine example: department s -gt department s after modifying cells through rules, the consolidations of these cells wont match the new values. To reconciliate consolidations add in your rules: Total ConsolidateChildren( dimension ) scheduling chores on calendar events Scheduling chores in TM1 can be frustrating as it does not offer to run on specific dates or other types of events. The following article explains how to create chores schedules as flexible as you need them to be. From the Server Explorer . create a new process . go directly in Advanced-gtProlog . add this code: run chore every 1st day of the month If(SUBST(TODAY,7,2) Another example to run on specific days of the week: DayOfWeek Mod ( DayNo( TODAY ) 21915, 7) 0 Sunday, 1 Monday to 6 Saturday. If( DayOfWeek 0 DayOfWeek gt 4 ) ChoreQuit send emailattachments It is possible to send email alerts or reports as attachments from Turbo Integrator. This can be achieved by executing a VB script. 1. save the attached VB script on your TM1 server 2. create a TI process 3. in Epilog add the following code: SRuncmd c D:pathtoSendMail.vbs smtp.mycompany 25 116109496410912199111109112971101214699111109 1091016410912199111109112971101214699111109 Today report check it out E:TM1Reportstodaysreport.xls ExecuteCommand(SRun,0) The syntax is: SendMail.vbs server port sender destination subject body attachment so replace the fields as required to suit your setup The DOS command line is limited to 255 characters so avoid putting too much information in the body. If a field contains a blank sp ace you must enclose that field in quotes so the script gets the correct parameters code from kckang (applix forum) and rondebruin.nlcdo.htm Applix is developing a tool called Report Connect currently in the beta stage, which will integrate with TM1 and allow users to schedule automated reports emailing from the TM1 server silence is fool039s gold TM1 processes will not complain when their input source is empty. So although the process successful or chore successful message will popup, your cube will remain desperately empty. In order to solve that silent bug (or Cogglix feature), you will need to add specific code to your TI processes to test against empty sources. Here follows: initialise counter PROLOG TAB increment counter DATA TAB check counter value at the end and take appropriate action EPILOG TAB ItemReject will send the error to the msg log and the execution status box will signal a minor error. the fallacy of blb files TM1 system files with the blb extension, incorrectly referenced as cube formatting files (admin guide p.35) are actually rule formatting files for the standard rules editor (prior to 9.1). The rules editor actually displays the contents of the .blb if there is one, otherwise it defaults to the .rux. Unfortunately things can go wrong, and the .blb file gets desynchronised from the actual .rux or just go blank. As a result, what you see in the rule editor are NOT the rules attached to your cube and it becomes tricky to pinpoint any issue as the rule tracer gets confused too. A simple fix is to delete the associated .blb file in the TM1 Data folder and reopen the rules in the rule editor. Well it works only until the next time it goes desynchronised or blank. From 9.1, you can turn on the new rules editor from the tm1s.cfg: AdvancedRulesEditor T Ultimately if you really cannot do without formatting, consider using an editor with highlighting features and copypaste the rules. Attached below is a very basic lint tool whose task is to flag dangerous TM1 rules. For now it is only checking for aliases in rules. Indeed, if an alias is changed or deleted, any rule based on that alias will stop working without any warning from the system. The values will remain in place until the cube or its rules gets reloaded but you will only get a silent warning in the messages log after reloading the cube. How to proceed: .configure and execute the following TI process (to put in prolog), this will generate a list of all cubes and associated dimensions, and a dictionary of all aliases on the system in .csv format configure and execute the perl script attached below that script will load the csv files generated earlier in hash tables, scan all rules files and finally report any aliases. If the element is ambiguous because it is present in 2 different dimensions then you should write it as dimension:element instead of using aliases (e.g. write Account:71010 instead of 71010). TM1 operators logical operators: amp AND OR strings operators: concatenate string1 equals string2 disabling the DEL key to forbid users from deleting DBRW formulas Private Sub WorkbookActivate() DisableDel End Sub Private Sub WorkbookDeactivate() EnableDel End Sub Private Sub WorkbookBeforeClose(Cancel As Boolean) a MsgBox(Your data have already been saved in Tm1, you dont need to save this Excel slice, vbInformation) EnableDel ActiveWorkbook.Close False End Sub Sub DisableDel() Application.OnKey , SendSpace MsgBox Delete Key Disable End Sub Sub EnableDel() Application.OnKey MsgBox Delete Key Enable End Sub Sub SendSpace() MsgBox Delete key not allowed. Sending a space instead SendKeys TM1RECALC1. same as shift-F9, refreshes only the active worksheet TM1RECALC. same as F9, refreshes ALL open workbooks TM1REFRESH. same as Alt F9, rebuilds (dynamic spreadsheets) and refreshes ALL open workbooks TM1StartOrionWithAutomation. opens Server Explorer CUBESBROWSE. opens Server Explorer SUBDELETE: deletes a subset (if unused) ex: Application.Run(SUBDELETE, myserver:account, MySubset) TM1InsertViewControl. starts In Spreadsheet browser TWHELP. opens TM1 perspectives help TWDEFAULTS. opens TM1 Options menu TWMERUL. opens rules worksheets menu TWMEDIM. opens dimensions worksheets menu to be continued. Excel formulas references When editing a cell formula ( F2 ), you can easily toggle between relative and absolute references with the F4 key: B10 -F4-gt B10 -F4-gt B10 -F4-gt B10 zero out portions In order to zero out data points you can do it either: - from Cube Viewer select all the cells with the pointer right click: Data Spread-gtClear. or select the top left corner cell of the portion to zero out right click: Data Spread-gtRepeat set value box to 0 and tick boxes Extend right and down - from a TI process in the prolog tab: ViewZeroOut(Cube,View) setup the View to zero out then run that process This section is dedicated to various material for admins: monitoring, troubleshooting, optimising. Before running the following script you need to setup a chore with a TI process to execute that line in Prolog: Avoid using the SaveTime setting in tm1s.cfg as it could conflict with other choresprocesses trying to run at the same time here is the DOS backup script that you can schedule to backup your TM1 server Documenting TM1 section dedicated to documenting TM1 with different techniques and tools. a closer look at chores if you ever loaded a .cho file in an editor this is what you would expect: 534,8 530,yyyymmddhhmmss ------ datetime of the first run 531,dddhhmmss ------ frequency 532,p ------ number p of processes to run 13,16 6,process name 560,0 13,16 533,x ------ x1 active x0 inactive In the 9.1 series it is possible to see from the Server Explorer which chores are active from the chores menu. However this is not the case in the 9.0 series, also it is not possible to see when and how often the chores are running unless you deactivate them first and edit them. Not quite convenient to say the least. From the specs above, it is easy to set rules for a parser and deliver all that information in a simple report. So the perl script attached below is doing just that: listing all chores on your server, their datetime of execution, frequency and activity status. Procedure to follow: 1. install perl 2. save chores.pl in a folder 3. doubleclick on chores.pl 4. a window opens, enter the path to your TM1 server data folder there 5. open resulting file chores.txt created in the same folder as chores.pl a closer look at subsets if you ever loaded a .sub file (subset) in an editor this is the format you would expect: 283,2 start 11,yyyymmddhhmmss creation date 274,string name of the alias to display 18,0 275,d d number of characters of the MDX expression stored on the next line 278,0 281,b b 0 or 1 expand above trigger 270,d d number of elements in the subset followed by the list of these elements, this also represents the set of elements of if you have an MDX expression attached These .sub files are stored in cube subs folders for public subsets or user cube subs for private subsets. Often a source of discrepancy in views and reports is the use of static subsets. For example a view was created a while ago, displaying a bunch of customers, but since then new customers got added in the system and they will not appear in that view unless they are manually added to the static subset. Based on the details above, one could search for all non-MDXstatic subsets (wingrep regexp search 275, in all .sub files) and identify which might actually need to be made dynamic in order to keep up with slowly changing dimensions. Beam me up Scotty: 3D Animated TM1 Data Flow Explore the structure of your TM1 system through the Skyrails 3D interface: If you do not have flash, you can have a look at some screenshots WARNING: your eyeballs may pop out This is basically the same as the previous work with graphviz. except this time it is pushed to 3D, animated and interactive. So the visualisation engine Skyrails is developed by Ph.D. student Yose Widjaja. I only wrote the TM1 parser and associated Skyrails script to port a high level view of the TM1 Data flow into the Skyrails realm. download and unzip skyrails beta 2nd build .download and unzip TM1skyrails.zip (attachment below) in the skyraildist2 folder .in the skyraildist2 folder, doubleclick TM1skyrails.pl (you will need perl installed unless someone wants to provide a compiled .exe of the script with the PAR module) .enter the path to (a copy of) your TM1 Data folder .skyrails window opens, click on the folder icon and click TM1 If you dont want to install perl, you can still enjoy a preview of the Planning Sample that comes out of the box. Just double-click on raex.exe . w,s,a,d keys to move the camera Quick legend : orange -- cube blue -- process light cyan -- file red -- ODBC source green sphere -- probably reference to an object that does not exists (anymore) green edge: intercube rule flow red edge: process (CellGetCellPut) flow Changelog: 1.1 a few mouse gestures added (right click on a node then follow instructions) to get planar (like graphviz) and spherical representations. 1.2 - edges color coded, see legend above - animated arrows - gestures to display different flows (no flowrules onlyprocesses onlyall flow) Dimensions updates mapping When faced with a large undocumented TM1 server, it might become hard to see how dimensions are being updated. The following perlgraphviz script creates a graph to display which processes are updating dimensions. That script dimflow.pl below is looking for functions updating dimensions (DimensionElementInsert, DimensionCreate. ) in .pro files in the TM1 datafolder and maps it all together. Unfortunately it does not take into account manual editing of dimensions. This is the result: The above screenshot is probably a good example of why such map can be useful: you can see immediately that several processes are updating the same dimensions. It might be necessary to have several processes feeding a dimension, though it will be good to review these processes to make sure they are not redundant or damaging each others effects. Procedure to follow: 1. install perl and graphviz 2. download the script below and rename it to .pl extension 3. doubleclick on it 4. enter the path to your TM1 Data folder (servernamedatafolder) 5. This will create 2 files dim.dot and dim.gif in the same folder as the perl script 6. Open dim.gif with any browser picture editor graphing TM1 data flow Attached is the new version of a little parser in perl (free) that will create a text file for graphviz (free too) out of your .pro and .rux files and then generate a graph of the data flow in your TM1 server. (the image has been cropped and scaled down for display, the original image is actually readable) legend ellipses cubes, rectangles processes red cellget, blue cellput, green inter-cube rule Procedure to follow : 1. install perl and graphviz 2. put the genflow perl script in any folder, make sure it has the .pl extension (not txt) 3. doubleclick on it 4. Enter the path to your TM1 Data folder such as: servernamedatafolder where servernamedatafolder is the full file path to your TM1 data folder 5. Hit return and wait until the window disappears This creates 2 files: flow.dot and flow.gif in the same folder as the perl script 6. Open flow.gif in any browser or picture editor Changelog 1.3 : .CellPut parsing fix .cubesprocesses names displayed as is 1.4 : .display import view names along the edges .display zeroout views .sources differentiated by shape This is still quite experimental but this could become useful to view at a glance high-level interactions between your cubes. indexing subsets Maintaining subsets on your server might be problematic. For example you wanted to delete an old subset that you found out to be incorrect and your server replied this: This is not quite helpful, as it does not say which views are affected and need to be corrected. Worse is that, as Admin, you can delete any public subset as long as it is not being used in a public view. If it is used in a users private view, it will be deleted anyway and that private view might become invalid or just wont load. In order to remediate to these issues, I wrote a little perl script, attached below, that will: .index all your subsets, including users subsets. .display all unused subsets (i.e. not attached to any existing views) From the index, you can find out right away in which views a given subset is used. I suppose the same could be achieved through the TM1 API though you would have to log as every user in turn in order to get a full index of all subsets. Run from a DOS shell: perl indexsubset.pl pathtoTM1server gt mysubsets.txt processes history On a large undocumented and mature TM1 server you might find yourself with a lot of processes and you wonder how many of them are still in use or the last time they got run. The following script answers these questions for you. One could take a look at the creationmodification time of the processes in the TM1 Data folder however you would have to sit through pages of the tms1msg.log to get the history of a given process which is what the script below does. Procedure to follow for TM1 9.0 or 8.x 1. install perl (free) 2. save loganalysis.pl.txt in a folder as loganalysis.pl 3. stop your TM1 service (necessary to beat the windows lock on tm1smsg.log) 4. copy the tm1smsg.log into the folder where loganalysis.pl is 5. start your TM1 service 6. double click loganalysis.pl Procedure to follow for TM1 9.1 1. install perl (free) 2. save loganalysis.pl.txt in a folder as loganalysis.pl 3. copy the tm1server.log into the folder where loganalysis.pl is 4. double click loganalysis.pl That should display the newly created processes.txt in notepad and that should look like the following: First, all processes sorted by name and the last run time, user and how many times it ran. Second, all processes sorted by last run time, user and how many times it ran. I do not know what these BrandAnalysisUpdate or LoadDelivery processes do but I guess nobody is going to miss them. The case against single children I came across hierarchies holding single children. While creating a consolidation over only 1 element might make sense in some hierarchies, some people just use consolidations as an alternative to aliases. Either they just dont know they exist or they come from an age when TM1 did not have aliases yet. The following process will help you identify all the single child elements in your system. This effectively loops through all elements of all dimensions of your system, so this could be reused to carry out other checks. TM1 Documenter (a Documenting tool) Just FYR, New Version of TM1 Documenter Version2.5 (A documenting tool) has been released. I believe, it will very useful to TM1 Consultants amp Developers, and the Organisations as well. Myself being a TM1 Consultant, know the pain-areas of a Consultant. Having a Java background I developed this software. Usually documentation task takes about 20-40 days, thereby blocking a valuable resource (TM1 Developer) for such less-important task. By using this software, the documentation task will be completed in just few clicks. Moreover during development or support task, when the model becomes huge or complex sometimes people lose the exact data flow of the model (as Rule sets are difficult to understand). Some times a developer ,by mistake, deletes an object (be it a cube, dimension, element, or a subset) that is providing is data to some other object. TM1 do not disallow to do so, but the model goes on a toss. Here the Object Dependency Checker comes to rescue. Software Summary: It has in all 2 Main Modules. I have introduced one new module in it, i.e. Object Dependency Checker Following are the details: Documenter Module: 1. Detailed information summary of the cubes. 2. Cube Sizing. 3. Views info (optional). 4. Rules info (optional). 5. Detailed information summary of the Dimensions. 6. Subsets info (optional). 7. Export TI Process list. 8. Export Dimension Attributes 9. Export Element Attributes 10.Output html file with index on left side amp Object details in center pane that make easy to navigate the objects. Object Dependency Checker Module: 1. Cube Dependency: Select a cube amp see all the other cubes depending on this. (i.e. view all cubes that are been sourced by this cube) 2. Dimension Dependency: Select a DIM amp see all cubes amp rules using this DIM. It also displays if this Dim is being used as Picklist anywhere. 3. Element Dependency: Select an Element from Dim amp see all cubes using. specifically, this element Data. 4. Subset Dependency: Select a Subset amp see all the views using this subset. It also displays if this Subset is being used as Picklist anywhere. 5. Cube-Element Dependency: This is detailed level element dependency. This module checks if the data of selected element of the selected cube is being used anywhere. 6. Export option available for all above sub-modules. Your suggestions amp queries welcome. Please provide me feedback of this tool. dynamic tm1p.ini and homepages in Excel Pointing all your users to a single TM1 Admin host is convenient but not flexible if you manage several TM1 services. Each TM1 service might need different settings and you do not necessarily want users to be able to see the development or test services for example. Attached below is an addin that logs the users on a predefined server and settings as shown on that graph: With such a setup, you can basculate your users from one server to the other without having to tinker the tm1p.ini files on every single desktop. This solution probably offers the most flexibility and maintainability as you could add conditional statements to point different groups of users to different serverssettings and even manage and retrieve these settings from a cube through the API. This addin also includes: - previous code like the TM1 freeze button - it loads automatically an excel spreadsheet named after the user so each user can customise it with their reportslinks for a faster access to their data. The TM1 macro OPTSET . used in the .xla below, can preconfigure the tm1p.ini with a lot more values. The official TM1 Help does not reference all the available values though. Here is a more complete list, you can actually change all the parameters displayed in the Server Explorer File-gtOptions with OPTSET: AdminHost DataBaseDirectory IntegratedLogin ConnectLocalAtStartup InProcessLocalServer TM1PostScriptPrinter HttpProxyServerHost HttpProxyServerPort UseHttpProxyServer HttpConnectorUrl UseHttpConnector AnsiFiles GenDBRW NoChangeMessage DimensionDownloadMaxSize this also applies to OPTGET WARNING: Make sure that all hosts in the AdminHost line are up and working otherwise ArchitectPerspectives will hang for a couple of seconds while trying to connect to these hosts. free utilities If you are stuck with a Windows operating system, you might need some tools for basic needs, these are all free: for bruteregexp search in .pro and .rux files: WinGrep finding out changes in different versions of files: WinMerge or the PSPad editor Windirstat is quite a useful visualisation tool to clean up your drivesservers. A picture is worth a thousand words, take a look at the screenshot . It was first developed for KDE: kdirstat SnagIt can capture scrolling long web pages, extract text from windows, annotate images and more. Read the detailed SnagIt review . download here free license subscription here (thanks Eric ) A few more tools from lifehacker And finally, not a desktop tool per se, but quite useful to share files quick, easy and secure: drop.io How to monitor TM1 connections using a Java application download --gt TM1Shell.rar . This program is a shell that allows you to connect to a TM1 server running. Then it starts two threads : - One write in a logfile the log activity on the server and save it - The second allows you to have a description of dimensions, cubes, elements. and to export it into an Excel File. Use cmd to draw a list of available commands. The project is under construction, any suggestions welcome. A new version is coming out soon. If you have any questions, you can contact me. 1081179997115461061111051031109711712064103109971051084699111109 Locking and updating locked cubes Locking cubes is a good way to insure your (meta)data is not tampered with. Right click on the cube you wish to lock, then select Security -gt Lock . This now protects the cube contents from TI process and (un)intentional admins changes. However, this makes updating your (meta)data more time consuming, as you need to remove the lock prior to updating the cube. Hopefully, the function CubeLockOverride allows you to automate that step. The following TI code demonstrates this, .lock a cube .copypaste the code in a TI Prolog tab .change the parameters to fit your system .execute: Note: CubeLockOverride is in the reserved words listed in the TM1 manual but its function seems to be only documented in the 8.4.5 releases notes . This works from 8.4.5 to the most recent 9.x series managing the licences limit One day you might face or already faced the problem of too many licences being in use and as a result additional users cannot log in. Also on a default setup, nothing stops users from opening several tm1webperspectives sessions and reach the limit of licenses. So in order to prevent that: open the cube ClientProperties, change all users MaximumPorts to 1 .in your tm1s.cfg add that line, it will timeout all idle connections after 1 hour: IdleConnectionTimeOutSeconds 3600 To see whos logged on : .use tm1top or .open the cube ClientProperties all logged users have the STATUS measure set to ACTIVE or .in server manager (rightclick server icon), click Select clients. to get the list To kick some users without taking the server down: in server explorer right click on your server icon -gt Server Manager select disconnect clients and Select clients. then OK and they are gone. Unfortunately there is still no workaround for the admin to log in when users take all the slots allowed. monitor rules and processes Changing a rule or process in TM1 does not show up in the logs. That is fine as long as you are the only Power User able to tinker with these objects. Unfortunately, it can get out of hand pretty quickly as more power users join the party and make changes that might impact other departments data. So here goes a simple way to report changes. The idea is to compare the current files on the production server with a backup from the previous day. You will need: .access to the live TM1 Data Folder .access to the last daily backup .a VB script to email results you can find one there .diff, egrep and unix2dos, you can extract these from that zip package and efghsoftwareunix2dos.exe or download directly the attachments below (GNU license) Dump these files in D:TM1DATABIN for example, or some path accessible to the TM1 server. In the same folder create a diff.bat file, replace all the TM1DATA paths to your configuration: Now you can set a TM1 process with the following line to run diff.bat and schedule it from a chore. Best is to run the process at close of business, just before creating the backup of the day. And you should start receiving emails like these: In this case we can see that the rules from the Productivity cube have changed today. monitoring chores by email Using the script in the Send Email Attachments article, it is possible to set it up to automatically email the Admin when a process in a chore fails. Here is how to proceed: 1. setup admin email process First we create a process to add an email field to the ClientProperties cube and add an email to forward to the Admin. 1.1 create a new process ---- AdvancedParameters Tab, insert this parameter: AdminEmail String Admin Email --- AdvancedProlog tab if(DIMIX(ClientProperties,Email) 0) DimensionElementInsert(ClientProperties,,Email,S) Endif --- AdvancedEpilog tab CellPutS(AdminEmail,ClientProperties,Admin,Email) 1.2 Save and Run 2. create monitor process ---- AdvancedProlog tab MailServer smtp.mycompany LogDir tm1servereTM1DataLog ScriptDir E:TM1Data If(ProcessReturnCode mind that future TM1 versions might use a different format for .cho files and that might break this script If(Tag 6) MailServer mail.myserver LogDir serverfTM1DatamyTM1Log get the process names from the deactivated chore ProcessMeasure NumericGlobalVariable( ProcessReturnCode) StringGlobalVariable(Status) The code only differs from the first method when the process returns a ChoreQuit exit. Because we will be running the chore Daily Update from another chore, the ChoreQuit will not apply to the later, so we need to specify it e xplicitly to respect the flow and stop at the same point. 2. create chore ProcessCheck just add the process above and set it to the same frequency and time as the Daily Update chore that you want to monitor 3. deactivate Daily Update since the ProcessCheck chore will run the Daily Update chore there is no need to execute Daily Update another time monitoring users logins a quick way to monitor users loginlogout on your system is to log the STATUS value (i.e. ACTIVE or blank) from the ClientProperties cube. View-gtDisplay Control Objects Cubes -rightclick- Security Assignments browse down to the ClientProperties cube and make sure the Logging box is checked tm1server -rightclick- View Transaction Log Select Cubes: ClientProperties All the transactions are stored in the tm1s.log file, however if you are on a TM1 version prior to version 9.1 and hosted on a Windows server, the file will be locked. A Save Data will close the log file and add a timestamp to its name, so you can start playing with it. This trick does not work in TM1 9.1SP3 as it does not update the STATUS value. Oops I did it again OH NOOOEES A l user just ran that hazardous process or spreading on the production server and as a result trashed loads of data on your beloved server. You cannot afford to take the server down to get yesterdays backup and they need the data now. Fear not, the transaction log is here to save the day. in server explorer, right click on server-gtView Transaction Log .narrow the query as much as you can to the timeclientcubemeasures that you are after Mind the date is in north-american format mmddyyyy .Edit-gtSelect All .Edit-gtBack Out will rollback the selected entries Alternatively, you could get the last backup of the corresponding .cub of the damaged cube .in server explorer: right-click-gtunload cube .overwrite the .cub with the backed up .cub .reload the cube from server explorer by opening any view from it Out of Memory You will get the dreaded message Out of Memory if your TM1 server reaches beyond 2 GB of RAM. On top of adding more RAM, you also need to add the 3GB flag in C:boot.ini to extend the available space of the TM1 server from 2 to 3 GB, if you ever need more then you will have to look for a 64bit server. boot loader timeout10 defaultmulti(0)disk(0)rdisk(0)partition(1)WINDOWS operating systems multi(0)disk(0)rdisk(0)partition(1)WINDOWSMicrosoft Windows 2000 Advanced Server fastdetect multi(0)disk(0)rdisk(0)partition(1)WINDOWSrestore mode safeboot:dsrepair sos boot loader timeout10 defaultmulti(0)disk(0)rdisk(0)partition(1)WINDOWS operating systems multi(0)disk(0)rdisk(0)partition(1)WINDOWSMicrosoft Windows 2000 Advanced Server fastdetect 3GB multi(0)disk(0)rdisk(0)partition(1)WINDOWSrestore mode safeboot:dsrepair sos This trick will work only for Windows 2000 Advanced or Datacenter Server and Windows 2003 Enterprise or Datacenter Edition. It is also recommended that you restart your TM1 service daily in order to free up the RAM used from TM1 operations during the day. After a RAM upgrade to 3 GB, you might still get the Out of Memory message when you are importing a lot of data at once although your server itself is actually using only 2.5 GB. A way to circumvent that limit is to breakdown the import of data: use SaveDataAll to commit the changes on disk after some import then use CubeUnload( cube ) of the cube that you just updated. That will free some space that can be used for importing further data for other cubes and that space will be used back to load that cube later once someone opens a view from that cube . pushing data from an iSeries to TM1 TM1 chore scheduling is frequency based, i.e. it will run and try to pull data after a predefined period of time regardless of the availability of the data at the source. Unfortunately it can be a hit or miss and it can even become a maintenance issue when Daylight Saving Time come into play. Ideally you would need to import or get the data pushed to TM1 as soon as it is available. The following article shows one way of achieving that goal with an iSeries as the source. prerequesites on the TM1 server: .Mike Cowie s TIExecute or download it from the attachment below .iSeries Client Access components (iSeries Access for Windows Remote Command service) Procedure to follow 1. drop TM1ChoreExecute, TM1ProcessExecute, associated files and the 32bit TM1 API dlls in a folder on the TM1 server (see readme in the zip for details) 2. start iSeries Access for Windows Remote Command on the TM1 server, set as automatic and select a user that can execute the TM1ChoreExecute 3. in client access setup: set remote incoming command run as system generic security 3. on your iSeries, add the following command after all your queriesextracts: RUNRMTCMD CMD(start D:pathtoTM1ChoreExecute AdminServer TM1Server UserID Password ChoreName) RMTLOCNAME(10.xx.x.xx IP) WAITTIME(10) 10.xx.x.xx IP of your TM1 server D:pathto path where the TM1ChoreExecute is stored AdminServer name of machine running the Admin Server service on your network. TM1Server visible name of your TM1 Server (not the machine name of the machine running TM1. UserID TM1 user ID with credentials to execute the chore. Password TM1 user IDs password to the TM1 Server. ChoreName name of requested chore to be run to load data from the iSeries. You should consider setting a userpass to restrict access to the iSeries remote service and avoid abuse. But ideally an equivalent of TM1ChoreExecute should be compiled and executed directly from the iSeries. store any files in the Applications folder The Applications folder is great but limited to views and xls files, well not anymore ). The following explains how to make available just any file in your Applications folders. 1. create a file called myfile.blob in Applications on your TM1 server it should contain the following 3 lines: ENTRYNAMEtutorial.pdf ENTRYTYPEblob ENTRYREFERENCETM:blobpublic.Externalstutorial.pdf 2. place your file, tutorial.pdf in this case, in Externals or whatever path you defined in ENTRYREFERENCE 3. restart your TM1 service ENTRYNAME is the name that will be displayed in Server Explorer. ENTRYREFERENCE is the path to your actual file. The file does not need to be in the folder Externals but the server must be able to access it avoid large files, there is no sign to tell you to wait while loading, impatient users might click several times on the file and unvoluntarily flood the server or themselves. add the extension in ENTRYNAME to avoid confusion, although it is not a .xls file, it will be displayed with an XLS icon. TM1 services on the command line removing a TM1 service in a DOS shell: go to the bin folder where TM1 is installed then: tm1sd -remove TM1 Service where TM1 Service is the name of an existing TM1 service or: sc delete TM1 Service removing the TM1 Admin services sc delete tm1admsdx64 sc delete TM1ExcelService installing a TM1 service in a DOS shell: go to the bin folder where TM1 is installed then: tm1sd -install TM1 Service DIRCONFIG where DIRCONFIG is the absolute path where the tm1s.cfg of your TM1 Service is stored manually starting a TM1 service from a DOS shell in the bin folder of the TM1 installation: tm1s -z DIRCONFIG remotely start a TM1 service netsvc start TM1server TM1 service sc TM1server start TM1 service remotely stop a TM1 service netsvc stop TM1server TM1 service sc TM1server stop TM1 service TM1 sudoku Beyond the purely ludic and mathematical aspects of sudoku. this code demonstrates how to set up dimensions, cubes, views, cell formating, security at elements and cells levels all through Turbo Integrator in just one process. Thanks to this application, you can prove your TM1 ROI. none of your company employees will ever need to shell out 1 for their daily sudoku from the Times. Alternatively, you could move your users to a probation group before they start their shift. It is only by completing successfully the sudoku that the users will be moved back to their original group. This way you can insure your company employees are mentally fit to carry out changes to the budget, especially after last evening ethylic abuses down the pub. Of course it exists many sudoku available for Excel, this is one is to be played primarily from the cube viewer, but you could also slice the view and play it from Excel too. How to install: .Save the processes in your TM1 folder and reload your server or copy the code directly to new turbo integrator processes. .Execute Create Sudoku. That creates the cube, default view and new puzzle in less than a second. sudoku The user can input numbers in the input grid only where there are zeroes. The solution grid cannot be read by default. .Execute Check Sudoku to verify your input grid matches the solution. If you are logged under an admin account, you will not see any cells locked, you need to be under the group defined in the process to see the cells properly locked. You might want to change the default group allowed to play and the number of initial pairs that are blanked in order to increase difficulty. The algorithm provided to generate the sudoku could be quickly modified to solve by brute force any sudoku. Provided the sudoku grid is valid, it will find the solution, however some sudokus with too many empty cells will have more than one solution. This post is published on April 1st, but I can assure you the code is not an Aprils fool, it works and it was tested on TM1 9.0.3. realtime monitoring of your TM1 server, pretty much like the GNU top command. It is bundled with TM1 only from version 9.1. You might have to ask your support contact to get it or get Ben Hills TM1Top below. dump the files in a folder . edit tm1top.ini, replace myserver and myadminhost with your setup servername myserver adminhost myadminhost refresh5 logfile C:tm1top.log logperiod0 logappendT run the tm1top.exe Commands: X exit W write display to a file H help V verifylogin to allow cancelling jobs C cancel threads, you must first login to use that command Keep in mind all it does is to insert a ProcessQuit command in the chosen thread. Hence it will not work if the user is calculating a large view or a TI is stuck in a loop where it never reads the next data record, as the quit command is entered for the next data line rather than the next line of code. Then your only option becomes to terminate the users connection with the server manager or API. (thanks Steve Vincent). Ben Kyro Hill did a great job developing a very convenient GUI TM1Top. You can find it attached below. tm1web customizer The tm1web customizer will allow you to change the default logos and color schemes of tm1web from a graphical interface. That is trying to make it more convenient to customize your tm1web without having to dig in the code. It can be found here: ftp:ftp.applixpubGruenesTM1WebAppCustomizer.zip However note that it is configured to work with 9SP1. TM1Web vs TM1 Server Explorer DeathMatch I have a strong dislike for TM1Web and here is why. Quick Traffic Analysis comparison On the recommended practices site from Applix, the following article TM1 Deployment Options and Network Bandwidth Considerations claims that TM1Web is more suited to low bandwidth networks. O RLY So I decided to give it a go with Wireshark. great network analysis tool, used to be known as Ethereal. I do 2 runs, one with Server Explorer (direct over TCPIP no HTTP), the other with TM1Web The analysis takes place between a Windows XP client and Windows 2000 Advanced server hosting TM1. Both are using TM1 9.0SP2, the only customisation brought to TM1Web was to remove the top left TM1 logo so that should have only a neglectable effect on the statistics. In each case: .close all connections to TM1 server .on the client host, Wireshark capture filter set to log only packets to and from the TM1 Server Capture -gt Options set Interface to the ethernet card in use set capture filter to that string: host TM1 server IP if the TM1 server has the IP 192.168.0.10 then the capture filter must be: host 192.168.0.10 .check the capture baseline is flat to be sure there will be no other traffic .start logging packets just before opening the view .open a decent view, 412 rows x 8 columns .scroll through all the rows until bottom is reached .stop logging Results (in Wireshark, Statistics -gt Summary): 978 kBytes went through the network with TM1Web 150 kBytes went through the network with server explorercube viewer So much for saving bandwidth with TM1Web, it is actually consuming at least 5 times more traffic than Server Explorer. If I get more time I will look in the packets to see why there is so much overhead with TM1Web, my initial guess is this is caused by the additional HTTP protocol layer. This time I tried with another view, 7 dimensions, 415 rows by 9 columns similar results: 947 kB for TM1 Web 147 kB for cube viewer And I pushed the analysis a bit further. Wireshark Menu: Statistics -gt Protocol Hierarchy As you can see HTTP takes up only 8.7 of the total traffic, but that is already 47 kBytes just to embed data on the wire, cube viewer would have already transfered 30 of the view in the same amount of bytes Now lets breakdown the conversation between the client and server. From the Wireshark menu: Statistics -gt Conversation List -gt TCP The popup window now displays the TCP conversations by size, the fattest are at the bottom. So lets see what is causing all that traffic. Right click the last one: Apply As Filter-gt Selected -gt A--B then from the Wireshark menu: Analyze -gt Follow TCP Stream You can now see what makes up all that traffic, and the culprit is. OMG ALL THAT JUNK HTML CODE and that is sent every single time you press the little arrows to change the page on a view. You would think TM1Web would somehow send only the actual data and leave the formatting processing to the client (AJAX) to spare the network and boost response times, well it is just not the case. Future Developments attached to this page is a presentation from David Corbett made last October about the roadmap for current and future developments in TM1
Nokia-forex-app
Sistem Investor-rt-trading