Witryna jest obecnie niedostępna Witryna, którą chcesz odwiedzić, jest obecnie niedostępna. Może to wynikać z tego, że strona jest w trakcie opracowywania, że trwają prace konserwacyjne lub że strona internetowa jest zamknięta z innego powodu. Właściciel domeny, sprawdź swój adres kontaktowy (e-mail) lub skontaktuj się z supportloopia, aby uzyskać więcej informacji. Webbplatsen gr ej at n fr tillfllet Webbplatsen du vill beska r fr tillfllet inte tillgnglig. Detta kan bero p att sidan r under utveckling, att underllll grs eller att sidan av annan anledning r stngd. Domngare, se din kontaktadress (e-post) eller kontakta supportloopia. se fr mer information. Nettsiden kan ikke ns dla yeblikket Nettsiden du vil beske er for yeblikket ikke tilgjengelig. Dette kan komme av at siden er under utvikling, vedlikehold utfres eller at siden av en anledning er stengt. Domeneier, se din kontaktadresse (e-post) eller kontakt supportloopia. no dla mer informasjon. Ta wiadomość jest również dostępna w. Część Visma middot Tel: 46 21-12 82 22 middot Faks: 46 21-12 82 33 middot E-mail: infoloopia En del av Vismakoncernen middot Tel: 021-12 82 22 middot Faks: 021-12 82 33 middot E-post: infoloopia. seWięcej informacji na temat gt, jak obliczyć okres do daty suma w cognos 10 Tytuł wyniku wyszukiwania: Cognos: Cognos BI 10: obliczenie między 2 różnymi zapytaniami. Opis wyniku wyszukiwania: 2 października 2017 r. Mam Q1 z tabelą CT1, aby uzyskać obecny okres reveu1 (podsumować). Okres opiera się na 2 pytaniach o wybraną datę (miesiąc), np .: start. Tytuł wyniku wyszukiwania: Cognos: Cognos BI 10: łącznie 12 miesięcy łącznie za każdy miesiąc. Opis wyniku wyszukiwania: 24 lipca 2017 r. Wyświetl: Rozmowy według daty. Muszę obliczyć liczbę toczących się 12 miesięcy dla każdego wymienionego miesiąca. Po pierwsze, warto wiedzieć, jakiej wersji Cognos 10 używasz, jakie są dokładne dane dziesiętne. Jest to tak zwana miara czasu - nie można jej dodawać w wielu okresach. Tytuł wyniku wyszukiwania: użycie funkcji periodsToDate w opisie wyników wyszukiwania raportu IBM Cognos 10: 13 maja 2017 r. Opis, w jaki sposób funkcja dimensionToDate może być używana poza wymiarem czasowym, aby obliczyć sumę bieżącą w stosunku do on. Tytuł wyniku wyszukiwania: Cognos: Cognos BI 10: Rate Variance not aggregate properly in. Opis wyniku wyszukiwania: 27 sierpnia 2017. Wyświetl: Rozmowy według daty. 1-6 z 6. Próbuję obliczyć wariancję ceny, objętości i miksu pomiędzy 2 okresami. Jednak suma niższych poziomów nie jest równa górnemu poziomowi. Moje pytanie brzmi: jak obliczyć tylko poziomy liści i czy agregacja Cognos BI 10.1 górnych poziomów Tytuł wyniku wyszukiwania: Cognos: Cognos 8: Raporty sprzedaży z poprzedniego dnia, ostatnich 5 dni i. Opis wyniku wyszukiwania: 18 marca 2017 r. 10 odpowiedzi Ostatni post - 18 grudnia 2017 przez Arhan. Następnie utworzysz indywidualne obliczenia dla każdej wartości miary okresu. Myślę, że może być obliczenie daty pierwszego miesiąca na stronie Cognos, które. Chciałbym wypowiedzieć się na temat odpowiedzi Philsona, że skoro będziesz SUMA tę kwotę sprzedaży, nie używaj. Tytuł wyniku wyszukiwania: Cognos: Cognos 8: Moving - całkowite średnie ruchome funkcje. Opis wyniku wyszukiwania: 7 stycznia 2009 r. Wyświetl: Rozmowy według daty. Oto jak plik pomocy opisuje funkcję ruchomej sumy. Zaktualizowano 7 stycznia 2009 o 1: 10 PM przez SystemAdmin. Podążyłem za formułą, którą udostępniłeś do przenoszenia - totalnie, ale to, co jest dziwne. Tytuł wyniku wyszukiwania: Cognos: Cognos BI 10: Dynamiczne uzyskiwanie miesięcy w zamknięciu kwartalnym. Opis wyniku wyszukiwania: 20 grudnia 2017 r. Środowisko to studio raportów Cognos 8.4.1 przeciwko kostce MSAS. niektóre miesiące (3,6,9 amp 12) tylko z poziomu Miesiąc w wymiarze daty. W jaki sposób mogę zautomatyzować automatyczne tworzenie obliczeń? Odp: Aby dynamicznie uzyskać miesiące w kwartale zamknięcia okresu. total (currentMeasure within set generate (.)). Tytuł wyniku wyszukiwania: Cognos: Cognos 8: jak grupować pole daty według 1 tygodnia lub 2 tygodni itp. Opis wyniku wyszukiwania: 11 maja 2017 r. ale chcę zgrupować datę kontroli według tygodnia i podsumować ilość jak poniżej. data inspekcji Ilość 2017-01-03 4 (tydzień 1) 2017-01- 10 3 (tydzień 2). próbując obliczyć wszystkie wymagane okresy w oparciu o daty w twoim. Tytuł wyniku wyszukiwania: Cognos: Cognos Express: Reporter. Jak zrobić łączną sumę. Opis wyniku wyszukiwania: 6 czerwca 2017 r. Cognos Express. Wyświetl: Rozmowy według daty. Dobrze działa, aby pokazać dochody według firmy i miesiąca, ale chcę sumy. Tytuł wyniku wyszukiwania: IBM Cognos Proven Practices: Korzystanie z względnych kategorii czasu. Opis wyniku wyszukiwania: 30 sierpnia 2017 r. Możliwość zastosowania. Przewodnik ma zastosowanie do IBM Cognos Report Studio w wersji 8.4 i nowszych. Rysunek 10. Fragment wyrażeń z edytora wyrażeń z wyrażeniem. Rysunek 10. Fragment członka. Dlaczego tak jest? Aby obliczyć 39PreviousMonth39, używana jest funkcja PrevMember. publikuj - date 08302017. Tytuł wyniku wyszukiwania: Cognos: Cognos BI 10: Wstawianie sumy całkowitej z innego zapytania. Opis wyniku wyszukiwania: 21 stycznia 2017 r. Cognos BI 10. i utratę produktu w porównaniu z 12 ruchomym wymiarem daty. Przykładem może być obliczenie wszystkich produktów, które miały wzrost między poprzednimi ostatnimi. Patrzę na każdy produkt, jeśli ma wzrost między tymi dwoma okresami. dodaj to. Jak uzyskać wielką sumę tego zapytania w tabeli krzyżowej Tytuł wyniku wyszukiwania: Cognos: Cognos 8: Pobieranie danych między dwiema datami w raporcie. Opis wyniku wyszukiwania: 5 stycznia 2017 r. Mam okno dialogowe Okres (pole listy), które będzie zawierało datę w formacie Jan-10. Jedna pozycja danych do obliczenia daty rozpoczęcia roku finansowego (kwiecień-RR). Tytuł wyniku wyszukiwania: Cognos: Cognos 8: pomiary CY i PY nie działają poprawnie. Opis wyniku wyszukiwania: 2 sierpnia 2017 r. Cognos 8. 10 10.1.1 10.2 10.2.1 8 8.3 8.4 8.4.1 Aktywny an i. Miarka CY vs PY dodaje lata razem zamiast odejmowania PY od. Całkowity okres do dnia (zgrupowany) lub N-ty okres łączny (zgrupowane). Tytuł wyniku wyszukiwania: IBM Business Analytics Sprawdzone metody: Ustawianie daty dynamicznej. Opis wyniku wyszukiwania: 14 listopada 2017 r. Ustawianie wartości dynamicznych wartości daty w ramach IBM Cognos 10.2 Report Studio. Produkt (y): Obszar zainteresowania IBM Cognos 10: Raportowanie. Dodanie JavaScriptu do ustawienia dynamicznych wartości zapytania. Snook (tsnook) Opher Banarie (OpherB) Witaj - Czy wymyśliliście rozwiązanie problemu z resetowaniem po ponownym uruchomieniu Tytuł wyniku wyszukiwania: Cognos 8 Funkcje wymiarowe - Opis wyniku wyszukiwania w IBM: produkty Cognos i powiązane usługi, które zostały licencjonowane lub zakupione od Cognos. BOTTOMSUM. Funkcje wymiarowe. 10. Informacje zastrzeżone firmy Cognos. miara Przychody jest domyślna z wymiaru Miary. Zwraca członka z innego okresu w tym samym krewnym. Książka kucharska TM1 Jest to praca zespołowa nad pisaniem: użytkownicy mogą współpracować przy pisaniu artykułów książki, pozycjonowaniu artykułów w odpowiedniej kolejności oraz recenzowaniu lub modyfikowaniu wcześniej napisanych artykułów. Kiedy więc masz jakieś informacje do przekazania lub kiedy czytasz artykuł z książki, a ci się to nie podobało lub jeśli uważasz, że dany artykuł mógłby być napisany lepiej, możesz coś z tym zrobić. Artykuły są podzielone na następujące sekcje: perspektywy przeglądarka kostka przeglądarka moduł tm1web menedżer planowania podgląd kostki porady szerokość kolumn, gdy masz widok z dużą ilością widocznych widoków, zdarza się, że większość ekranu nieruchomości jest zużywana przez kolumny wymiarów i ty znajdź się przewijając dużo w lewo i prawo, aby odczytać punkty danych. Aby rozwiązać ten problem: wybierz najkrótsze aliasy dla wszystkich wymiarów z edytora podzbiorów. wybierz nazwy podzbiorów tak krótko, jak to możliwe. Wyłącz opcję-gtExpand Nagłówek wierszy ExcelPerspectives Wskazówki, aby zmniejszyć prawdopodobieństwo awarii programu Excel, wyłącz automatyczne obliczanie. Idź do Narzędzia-gtOptions-gt Zakładka Obliczenia, a następnie kliknij przycisk Ręczny Możesz użyć: F9, aby ręcznie odświeżyć wszystkie otwarte skoroszyty Przesuń Shift F9, aby odświeżyć tylko bieżący arkusz kalkulacyjny F2 wprowadź (edytuj komórkę), aby odświeżyć tylko 1 komórkę w TM1 9.4. 1, arkusze kalkulacyjne zostaną ponownie obliczone automatycznie po otwarciu skoroszytu lub zmiana SUBNM, mimo że automatyczne obliczanie kalc jest wyłączone. Z górnego menu Excela kliknij Wstaw nazwę-Zdefiniuj. W oknie dialogowym Zdefiniuj nazwę wpisz TM1REBUILDOPCJA. Ustaw wartość w polu Odnosi się do 0 i kliknij OK. . unikaj wielu dynamicznych plasterków na kilku arkuszach skoroszytu, jeśli możesz, w przeciwnym razie program Excel staje się dość niestabilny i niektóre odwołania mogą zostać popsute. jeśli otrzymujesz e10) nie znaleziono katalogu danych błędu podczas ładowania Perspectives, musisz zdefiniować katalog danych twojego lokalnego serwera, nawet jeśli nie możesz go uruchomić. Przejdź do Files-gtOptions i wprowadź poprawny folder w polu Data Directory. jeśli to pole jest wyszarzone, musisz ręcznie edytować zmienną w pliku tm1p. ini zapisaną na komputerze. Alternatywnie możesz zmodyfikować ustawienie bezpośrednio z Excela za pomocą następującego kodu VBA: Application. Run (OPTSET, DatabaseDirectory, C: somepath) Którą wersją TM1 jesteś na widokach publikowania Publikowanie widoku użytkowników wciąż jest daleka od szybkiego i prostego procesu w TM1. Po pierwsze, administrator nie widzi widoków innych użytkowników. Po drugie, użytkownicy nie mogą publikować własnych widoków. Więc publikowanie widoków zawsze wymaga bezpośredniej interwencji administratora, no już nie :) jeśli ((ViewExists (Cube, View) 0) ((ViewExists (Cube, View) 1) amp (Overwrite Y))) get UserName If (SubsetExists (Klienci, WhoAmI) 0) SubsetDestroy (Klienci, WhoAmI) Klienci Endif StringMDX), USERNAME) SubsetCreatebyMDX (WhoAmI, StringMDX) UserSubsetGetElementName (Klienci, WhoAmI, 1) SubsetDestroy (Klienci, WhoAmI) kopiowanie widoku do przestrzeni publicznej TM1PathE: TM1DataTM1Server Query cmd c mkdir TM1PathCube vues ExecuteCommand (Query, 0) Zapytanie cmd c kopia TM1PathUser Cube vues Zobacz. vue TM1PathCube vues Zobacz. vue ExecuteCommand (Query, 0) najpierw wyładuj kostkę, aby przeładować vues CubeUnload (Cube) Else ItemReject (Widok widok w Cube Cube już istnieje) Endif 2. Zmień TM1Path i zapisz 3. w Server Explorer, Process-gtSecurity Assignment, ustaw ten proces jako Read dla wszystkich grup, które powinny być dozwolone. Teraz twoi użytkownicy mogą publikować swoje widoki na własną rękę, wykonując ten proces, wystarczy wpisać imię o f kostkę i widok do opublikowania. Kod w powyższej zakładce Prolog można zastąpić tymi 5 liniami: if ((ViewExists (Cube, View) 0) ((ViewExists (Cube, View) 1) amp (Overwrite Y))) PublishView (Cube, View, 1 , 1) Else ItemReject (View view in Cube Cube już istnieje) Endif Thats it :) Dzięki anonimowemu czytnikowi (proszę się zarejestrować, jeśli chcesz otrzymać kredyt) za sugestię. instrukcje edytora podzbiorów dla edytora podzbiorów wyświetlające skonsolidowane elementy poniżej ich elementów podrzędnych: Wyświetl - gt Rozwiń powyżej, aby uzyskać szybszą odpowiedź z edytora podzbiorów, wyłącz okno Właściwości - okno Właściwości lub kliknij okno Właściwości ekranu z paska narzędzi, aby dodać jeden lub więcej elementów w istniejącym podzbiorze bez odtwarzania go: z edytora podzbiorów - Edytuj-gtInsert Podzbiór - Wybierz elementy - Kliknij OK, aby zapisać jako prywatny podzbiór1 teraz Podzestaw1 został dodany do istniejącego podzestawu - Rozwiń podzbiór1 - Kliknij konsolidację podzbioru1 element następnie usuń możesz teraz zapisać swój podzbiór za pomocą nowych elementów, aby widoki kostek wyświetlały się znacznie szybciej w tm1web: podzbiory wymiarów na górze muszą zawierać tylko 1 element, każde kliknięcie ikony w menu rozwijanym Eksport nie będzie miało żadnego efektu , tylko kliknięcie na powiązanym tekście po prawej stronie plasternapshotpdf rozpocznie eksportowanie śledzenia odsyłaczy TM1. Domowe raporty TM1 mogą stać się dość zawiłe i użytkownicy mogą uzyskać ciężko je aktualizować, ponieważ trudno jest wskazać, na co wskazują niektóre formuły TM1. Pasek narzędzi kontroli formuł Excel może być przydatny w takich sytuacjach. kliknij prawym przyciskiem myszy obok górnego paska, aby wyświetlić menu paska. wybierz pasek kontroli Bardzo przydatne, aby uzyskać zasięg odwołania do kostki lub wymiaru w raporcie lub sprawdzić, z których elementów składa się formuła DBRW. Twórcy programu Turbo Integrator zasady kodu VBA bliższe spojrzenie na dynamiczne plasterki Dynamiczne plasterki mogą być bardzo przydatne, gdy elementy wyświetlane w raportach ewoluują z czasem, automatycznie aktualizują się o nowe elementy. Poniższy artykuł spróbuje zagłębić się w parametry, które definiują te plasterki i pokazać niektóre z możliwości interakcji z nimi. Pomysł został pierwotnie przedstawiony przez Philipa Bicharda. Dynamiczne parametry przekroju przechowywane na liście nazw arkusza roboczego do wyświetlenia w Excelu: insert - gt nazwa - gt paste - gt lista wklejania większość parametrów definiuje się jako SL xx C yy xx jest numerem referencyjnym 01, 02, 03.dla tak wielu plasterków jak w raporcie yy jest odniesieniem do ułożonego wymiaru ex: SL01 C 01 odnosi się do pierwszego ustawionego w stos wymiaru na szczycie kolumn SL01C02 odnosi się do drugiego ustawionego w stos wymiaru od góry SL02R01 odnosi się do pierwszego skumulowanego wymiaru dla wiersze po lewej stronie drugiego fragmentu CUBE01RNG lokalizacji komórki przechowującej nazwę odwołanego do kostki zestawu SL01C01DIMNM lub nazwy wymiaru, jeśli nie jest używana lista elementów SL01C01EXPANDUP 1 0 zapisanych w zestawie SL01C01ELEMS01 jako zwinięte lub rozwinięte nazwa SL01C01FMTNM formatu elementów do użycia SL01C01HND. SL01C01IDXS01. Zakres SL01C01RNG dla ułożonego wymiaru SL01CPRX01. Zakres SL01DATARNG dla komórek DBRW Ustawienia filtru SL01FILT SL01R01ALIAS nazwa użytego aliasu SL01R01DIMNM SL01R01ELEMS01 lista wyświetlanych elementów SL01R01ELEMSxx. SL01R01EXPANDUP SL01R01HND SL01R01IDXS01 SL01R01NM nazwa podzestawu SL01R01RNG zakres granicy sekcji SL01RPRX01 SL01TIDXS01. SL01TPRX01. SL01VIEWHND. SL01VIEWIDX. SL01ZEROSUPCOL zera tłumić kolumn wywołać SL01ZEROSUPROW zera pominięte w rzędach wywołać SX01C01ENABLE wyzwalania SX01C01IDX SX01C01WD SX01R01ENABLE spust SX01R01HT SX01R01IDX wyzwalania SX01RNG granicy sekcji Zakres SXBNDDSP brzegowy odcinek wzór wyświetlacz wyzwalania TITLE1 subnm do TITLE1NM podzbiór lokalizacja komórki TITLE1RNG 1. wymiarze TITLE2 TITLE2NM TITLE2RNG TITLE3 TITLE3ALIAS alias być wyświetlany Wymiar TITLE3RNG przedefiniuje następującą nazwę SL01FILT z SL01FILT FUNCTIONPARAM0.000000SORTORDERdescTUPLESTRSales Mierzy jednostki sprzedaży do SL01FILT FUNCTIONPARAM0.000000SORTORDERascTUPLESTRSales Measures. Sales Cost zmieniłaby kolumnę, w której sortowanie jest dokonywane od jednostek sprzedaży do kosztów sprzedaży, a także kolejność od malejącej do rosnąco. Można również uzyskać podobny wynik z wyrażeniem MDX. Poniższy kod zmieni podzbiór z wymiaru stosu wierszy na zdefiniowany wcześniej podzbiór Zero Dynamic Level, a więc wszystkie elementy zostaną wyświetlone. Zauważ, że musisz użyć narzędzia TM1REFRESH lub Alt-F9, aby przekonwertować plaster, aby się odbudować, program TM1RECALC (F9) zaktualizowałby tylko formuły DBRW. Dynamiczne plasterki zostaną zerwane z następującymi wyskakującymi okienkami Brak dostępnych wartości, ponieważ jakiś element już nie istnieje lub nie ma żadnych wartości dla tego konkretnego plasterka. Łatwa naprawa polega na rozłączeniu się z serwerem TM1, załadowaniu raportu, usunięciu elementu powodującego problemy z tabeli wycinków i nazw, a następnie ponownemu połączeniu z serwerem TM1, dynamiczny plasterek odświeży się po ponownym nawiązaniu połączenia. Praca w toku. atrybuty Edytuj atrybuty. jeśli pojawi się następujący komunikat Ta operacja uzyskuje dostęp do wymiaru zawierającego dużą liczbę elementów. Przesyłanie tych elementów z serwera może potrwać kilka minut. Zamiast tego edytuj bezpośrednio kostkę atrybutów, jest ona znacznie szybsza: - Widok - g Wyświetl elementy sterujące - Otwórz wymiar ElementAttributes kostki - a następnie zmodyfikuj wymagane pola, tak jak w dowolnej kostce Dodaj nowy atrybut dla dużych wymiarów, szybciej jest po prostu utworzyć tymczasowy proces TI z następującym kodem w Prolog ATTRINSERT (Model: InteriorColor, S) Ten przykład tworzy atrybut łańcucha InteriorColor dla wymiaru Model. sprawdzenie, czy atrybut istnieje już, powiedzmy, że chcemy utworzyć kod aliasu dla wymiaru Klient. W zakładce Zaawansowane-gtProlog: Jeśli (DIMIX (ElementAttributesCustomer, Kod) 0) AttrInsert (Klient, Kod, A) Endif Więc nie musisz się martwić o AttrInsert generując błąd, jeśli będziesz musiał ponownie uruchomić proces. Jeśli aktualizujesz aliasy w TM1, wyczyść pamięć podręczną programu Excel, aby zobaczyć zmiany, uruchamiając makro mclear lub po prostu zrestartuj program Excel. Zbiorcze raportowanie Funkcja raportu TM1-gtPrint z Perspectives służy do generowania zbiorczych raportów statycznych dla danego zestawu elementów. Poniższy kod naśladuje i rozszerza tę funkcję w celu bardziej elastycznego raportowania zbiorczego dla raportu TM1. Na przykład można uzyskać raport oparty na oddziałach firmy, który ma zostać zapisany w każdym odpowiednim folderze dokumentów branżowych, zamiast umieszczać je wszystkie w jednym folderze lub można również przesłać raport każdego oddziału pocztą elektroniczną do własnego menedżera oddziału. komentowanie fragmentów kodu w TI Chciałbyś skomentować fragmenty kodu dla starszego lub przyszłego użycia zamiast go usuwać. Występowanie z przodu każdej linii jest brzydkie i powoduje pomieszanie wcięć. Oto szybka, łatwa i prosta naprawa. To jest to. Przydaje się także do wyłączenia generowanego automatycznie kodu w STARTU GENEROWANYCH URUCHOMIENIA. Gorąco polecam dodanie wyraźnych znaczników wokół kodu, skomentowanego, w przeciwnym razie bardzo łatwo jest przeoczyć krótkie stwierdzenie i zastanowić się, dlaczego proces nie działa. Dzięki Paul Simon za napiwek. tworząc widoki (tymczasowe) podczas tworzenia widoków z przeglądarki kostek, istnieje twardy limit rozmiaru wyświetlanego widoku. Domyślnie jest to 100 MB (32 bit) lub 500 MB (64 bity), można go zmienić za pomocą parametru MaximumViewSize w pliku tm1s. cfg Jednak generowanie dużych widoków nie jest praktyczne. Alternatywą jest zrobienie tego z Turbo Integrator: utwórz nowy proces TI wybierz TM1 Cube view import click browse wybierz widok tworzenia kostki click stwórz z niego możesz utworzyć dowolny widok, ale może być bardziej korzystne tworzenie i usuwanie widoków na lataj, aby serwer był czystszy i użytkownicy będą mniej zdezorientowani. Poniższy kod generuje widok z kostki MyCube zawierającej wszystkie elementy kostki. Musisz dodać SubsetCreateSubsetElementInsert w celu ograniczenia widoku, aby usunąć wszystkie konsolidacje. w większości przypadków zakłócają import, a otrzymasz tylko częściowy import lub nic. -------- prolog CubeName MyCube ViewName TIImport SubsetName TIImport i 1 przechodzą przez wszystkie wymiary kostki podczas (tabdim (CubeName, i) ltgt) Thisdim tabdim (CubeName, i) If (SubSetExists (ThisDim, SubsetName) 0 ) StringMDX, 0) utwórz podzbiór filtrujący wszystkie hierarchie SubsetCreatebyMDX (SubsetName, StringMDX) EndIf ii 1 end If (ViewExists (CubeName, ViewName) 0) ViewCreate (CubeName, ViewName) Endif i 1 przechodzi przez wszystkie wymiary kostki while ( tabdim (CubeName, i) ltgt) ViewSubsetAssign (CubeName, ViewName, tabdim (CubeName, i), SubsetName) ii 1 end ViewExtractSkipCalcsSet (CubeName, ViewName, 1) ViewExtractSkipZeroesSet (CubeName, ViewName, 1) -------- ------ Widok czyszczenia epilogu ViewDestroy (CubeName, ViewName) i 1 przechodzi przez wszystkie wymiary kostki podczas (tabdim (CubeName, i) ltgt) SubsetDestroy (tabdim (CubeName, i), SubsetName) ii 1 koniec Tworzenie Dynamic Podzbiory w Applix TM1 z MDX - A Primer O tym dokumencie Ten MDX Primer ma służyć jako prosty Wprowadzenie do tworzenia podzbiorów dynamicznych przy użyciu MDX w TM1. Koncentruje się na podawaniu przykładów roboczych, zamiast próbować wyjaśnić całą teorię MDX i upewnia się, że obejmuje funkcje najbardziej użyteczne dla użytkowników TM1. Obecnie TM1 (począwszy od wersji 9.0 SP3) umożliwia użytkownikom używanie MDX do tworzenia podzbiorów wymiarów, a nie do definiowania widoków kostki. Oznacza to, że korzystanie z MDX w TM1 często różni się zasadniczo pod względem składni i intencji od przykładów znalezionych w książkach i Internecie. Ponieważ MDX (Multi-Dimensional eXpressions) jest standardowym językiem zapytań dla baz danych OLAP firmy Microsoft, istnieje wiele odniesień i przykładów, które można znaleźć w Internecie, ale pamiętaj, że TM1 nie obsługuje każdego aspektu języka i dodaje kilka unikalne cechy własne. Może to utrudnić korzystanie z przykładów znalezionych w Internecie, podczas gdy wszystkie przykłady w tym dokumencie mogą być po prostu skopiowane i wklejone do TM1 i będą wykonywane bez modyfikacji, zakładając, że masz przykład minizestawu, który został udokumentowany później. Pełny dokument jako tylko jedna strona HTML tutaj. Co to jest dynamiczny podzbiór oparty na MDX w TM1 Dynamiczny podzbiór to taki, który nie jest stałą, statyczną listą, ale zamiast tego jest oparty na zapytaniu, które jest ponownie oceniane za każdym razem, gdy podzbiór jest używany. W rzeczywistości MDX może zostać użyty do stworzenia statycznego podzbioru, a przykład jest pokazany poniżej, ale jest mało prawdopodobne, aby był użyteczny lub powszechny. Przykładami użytecznych podzbiorów dynamicznych może być lista wszystkich produktów na poziomie podstawowym lista naszych 10 najlepszych klientów według marży brutto lista zaległych przesyłek z dostawą wszystkie miejsca powstawania kosztów, które jeszcze nie przekazały swojego budżetu. Chodzi o to, że te listy (podzbiory) mogą się różnić od drugiej do drugiej w oparciu o strukturę lub dane w TM1. Na przykład, gdy tylko nowy oddział zostanie dodany do Europy, podzestaw European Branch będzie natychmiast zawierał nowy oddział, bez konieczności ręcznej interwencji. MDX jest językiem zapytania używanym do definiowania tych podzbiorów. MDX jest standardowym językiem zapytań dla wielowymiarowych baz danych, takich jak TM1, chociaż TM1 obsługuje tylko pewien podzbiór (usprawiedliwia kalambur) całego języka i dodaje również kilka unikalnych cech. Kiedy definiujesz podzbiór przy użyciu MDX zamiast standardowego podzbioru, TM1 przechowuje tę definicję, a nie wynikowy zestaw. Oznacza to, że definicja lub zapytanie jest ponownie uruchamiane za każdym razem, gdy użytkownik patrzy na niego, bez konieczności podejmowania jakichkolwiek działań przez użytkownika lub administratora. Jeśli baza danych zmieniła się w jakiś sposób, możesz uzyskać różne wyniki od ostatniego użycia. Na przykład, jeśli podzbiór definiuje się jako dzieci z Oddziału Zachodniego Wybrzeża i początkowo zwraca Oakland, San Francisco, San Diego, gdy jest on zdefiniowany po raz pierwszy, może później wrócić do Oakland, San Francisco, San Diego, Los Angeles, gdy LA został dodany do wymiaru jako dziecko Oddziałów Zachodniego Wybrzeża. To właśnie rozumiemy przez dynamiczne zmiany wyników. Innym powodem, który może spowodować zmianę podzestawu, jest sytuacja, w której opiera się na wartościach w kostce lub atrybucie. Każdego dnia w gazecie wymieniane są największe giełdy, takie jak 10 największych pod względem wzrostu cen akcji. W modelu TM1 byłby to podzbiór analizujący miarę zmiany ceny akcji i wyraźnie prawdopodobnie zwróciłby inny zestaw 10 członków każdego dnia. Najlepsze jest to, że podzestaw automatycznie zaktualizuje wyniki bez żadnej pracy ze strony użytkownika. Jak utworzyć podzbiór bazujący na MDX w TM1 Te same podstawowe kroki można wykonać ze wszystkimi przykładami w tym dokumencie. Zwykle przykłady można skopiować i wkleić w Oknie wyrażeń Edytora podzbiorów danego wymiaru często jako Produktu. Zauważ, że nie ma znaczenia, która kostka wymiaru jest używana, otrzymasz te same wyniki, niezależnie od tego, czy otworzysz edytor wymiarów w widoku z widoku kostki, drzewa kostek w Eksploratorze serwera, czy w drzewie wymiarów w Eksploratorze serwera. Aby wyświetlić i edytować zapytanie MDX, musisz mieć możliwość wyświetlenia okna Wyrażenie w edytorze podzbiorów. Aby włączyć lub wyłączyć to okno, wybierz opcję Wyświetl okno wyrażeń. Możesz teraz po prostu wpisać (lub wkleić) zapytanie do tego okna wyrażeń i nacisnąć przycisk Aktualizuj, aby zobaczyć wyniki. Jak utworzyć statyczny podzestaw z MDX Podzbiór statyczny to taki, który nigdy nie różni się pod względem zawartości. To zapytanie zwróci za każdym razem tych samych 3 członków (Pożyczka rabatowa, Pożyczka terminowa i Handel detaliczny). Nie przejmuj się, tutaj jest bardziej ekscytująco. Jak utworzyć dynamiczny podzbiór za pomocą MDX TM1 obsługuje tylko pewną liczbę funkcji z kompletnej specyfikacji MDX. Różne wersje TM1 będą obsługiwać różne funkcje (i potencjalnie wspierać je na różne sposoby). Prawidłowy zestaw funkcji dla używanej wersji TM1 można znaleźć w głównym pliku Pomocy, w części Obsługa materiału MDX Pomoc odniesienia. Zanim spróbujesz napisać nowe zapytanie, upewnij się, że jest ono obsługiwane i chociaż niektóre funkcje niewymienione na pewno działają, muszą być używane na własne ryzyko. Standardowy komunikat o błędzie, który oznacza, że funkcja nie jest faktycznie obsługiwana przez twoją wersję TM1, nie powiodła się podczas kompilacji wyrażenia. Jedno słowo ostrzeżenia: z samej swojej natury wyniki dynamicznego podzbioru mogą się zmienić. Uwzględniając dynamiczne podzbiory w widokach, procesach, funkcjach SUBNM i tak dalej, należy dokładnie zastanowić się, jakie mogą być potencjalne przyszłe wyniki, zwłaszcza jeśli podzbiór mógłby być pewnego dnia pusty. Dwie najczęstsze metody, które należy wykonać, aby utworzyć podzestaw dynamiczny, to utworzyć je ręcznie lub przy pomocy TurboIntegratora. Ręcznie . Możesz wpisać (lub wkleić) zapytanie w oknie wyrażeń, jak wyjaśniono wcześniej, lub możesz wybrać opcję Narzędzia Nagraj wyrażenie (a następnie Zatrzymaj nagrywanie po zakończeniu), aby włączyć rodzaj magnetowidu. Następnie możesz użyć normalnych funkcji edytora podzbiorów (np. Wybierz według poziomu, sortuj malejąco itd.), A ta nagrywarka zmieni twoje działania w poprawne wyrażenie MDX. Jest to świetny sposób, aby zobaczyć przykłady poprawnej składni, szczególnie w przypadku bardziej złożonych zapytań. Kiedy nagrywasz wyrażenie i wybierasz Stop Recording TM1 poprosi Cię o potwierdzenie, czy chcesz dołączyć wyrażenie z podzestawem - pamiętaj, aby powiedzieć Tak i zaznaczyć pole wyboru Save Expression podczas zapisywania wynikowego podzbioru, w przeciwnym razie tylko statyczną listę wyniku jest zapisywany, a nie samego zapytania dynamicznego. Korzystanie z TurboIntegrator. Do utworzenia i zdefiniowania podzbioru potrzebna jest tylko jedna linia z wykorzystaniem SubsetCreateByMDX. Będziesz musiał wiedzieć, jakie zapytanie chcesz już zdefiniować. Zauważ, że kwerendę można zbudować w skrypcie TI za pomocą konkatenacji tekstu, dzięki czemu można uwzględnić zmienne ze skryptu i umożliwić tworzenie długich zapytań w etapach, które są łatwiejsze do odczytania i konserwacji. SubsetCreatebyMDX (Produkty podstawowe,, 0)) Wszystkie podzbiory MDX utworzone przez TI są automatycznie zapisywane jako dynamiczne zapytania MDX, a nie jako lista statyczna. Zauważ, że przynajmniej do TM1 v9.0 SP3 podzbiory oparte na MDX nie mogą zostać zniszczone (SubsetDestroy), jeśli są używane przez publiczny widok i nie można ich odtworzyć za pomocą drugiego polecenia SubsetCreateByMDX. Dlatego trudno jest zmienić podzbiory oparte na MDX przy użyciu TI. Chociaż dynamiczna natura definicji podzbioru może sprawić, że będzie ona mało prawdopodobna, będziesz chciał to zrobić, ważne jest, aby pamiętać o tym. Jeśli chcesz zmienić jakiś aspekt zapytania (np. TM1FilterByPattern od 2006-12 do 2007-01, być może będziesz musiał zdefiniować zapytanie, aby użyć zewnętrznych parametrów, jak udokumentowano w tym dokumencie. Będzie to miało mały wpływ na wydajność w porównaniu z prostszym Wersja z twardym kodowaniem Ponadto, filtrowanie wartości kostki z SubsetCreateByMDX w zakładce Epilog np. 0), Test. (Posting Measures. Amount) gt 0) nie zadziała, jeśli wartości zostały załadowane na zakładkę Dane. Musisz wykonać komendę SubsetCreateByMDX w kolejnym procesie TI. Zauważ, że TI ma limit 256 znaków do definiowania podzbiorów MDX, co najmniej do wersji 9.1 SP3, co może być dość ograniczające. Składnia i układ Zapytanie można złożyć na wiele linii, aby było bardziej czytelne. Na przykład: FILTER (, 0), Test2. (Rate Measures. Rate) 19) jest bardziej czytelny niż posiadanie całego zapytania w jednym wierszu. Rzeczywista sekcja filtru jest teraz łatwiejsza do odczytania i zmodyfikowana przez samo jej umieszczenie w linii. Zauważ, że odniesienia do członków zwykle mają nazwę wymiaru jako przedrostek. Na przykład nazwa wymiaru jest opcjonalna, ale tylko wtedy, gdy nazwa elementu (w tym przypadku handel detaliczny) jest całkowicie unikalna w obrębie całego serwera - tj. Nie ma żadnych kostek, wymiarów ani członków o tej dokładnej nazwie. Na przykład jest to to samo zapytanie z pominiętą nazwą wymiaru: które działałoby w kontekście przykładowej aplikacji używanej przez ten dokument, ale byłoby ryzykowne w aplikacji w świecie rzeczywistym. Komunikat o błędzie otrzymany, gdy zapomnisz podać prefiks, może być podobny do: Poziom lub nazwa elementu Detaliczny niejednoznaczny: znaleziony w wymiarach, a następnie wyświetla listę różnych wymiarów, w których można znaleźć nieunikalną nazwę elementu, co jest bardzo pomocny. Z tego powodu najbezpieczniej i najskuteczniej jest zawsze używać prefiksu wymiaru. Używanie nawiasów kwadratowych może czasami wydawać się nieco arbitralne podczas czytania przykładów zapytań MDX. Faktem jest, że nazwa obiektu OLAP (np. Nazwa kostki, nazwa wymiaru, nazwa elementu) musi być ujęta w nawiasy kwadratowe tylko wtedy, gdy zawiera spację, zaczyna się od liczby lub jest słowem zarezerwowanym MDX (np. Wybierz). Czasami jednak łatwiej jest zdecydować, aby zawsze używać nawiasów, aby podobne zapytania były łatwiej porównywane obok siebie. Dokładna definicja członka TM1 jest prawie zawsze wyrażana jako nazwa wymiaru. Nazwa użytkownika i nic więcej. W innych produktach, które również używają MDX jako języka zapytań (takiego jak Microsoft Analysis Services), możesz zauważyć, że zapytania określają pełną ścieżkę od nazwy wymiaru przez hierarchię do nazwy elementu, na przykład: Date.2009.Q1.Feb. Week 06 To może być również zapisane jako Date.2009Q1FebWeek 06 Powodem tego jest to, że inne produkty mogą nie wymagać, aby każda nazwa członka była unikalna, ponieważ każdy członek ma kontekst (swoją rodzinę), aby umożliwić jego jednoznaczną identyfikację, dlatego muszą dokładnie wiedzieć, który tydzień 06 jest wymagany, ponieważ mogą istnieć inne (w 2008 r. na przykład w powyższym przykładzie). TM1 wymaga, aby wszystkie nazwy członków na dowolnym poziomie (w obrębie aliasów) były całkowicie unikalne w tym wymiarze. TM1 wymagałoby od Ciebie wyraźniejszego zadeklarowania pierwszego kwartału, lutego i tygodnia 06 (tj. Pierwszego kwartału 2009, lutego 2009, tygodnia 06 2009), ale możesz odnieść się do Date. Week 06 2009. Wreszcie, sprawa (np. Duże litery w przeciwieństwie do małych liter) nie jest ważne przy poleceniach MDX (np. Filter lub FILTER, TOPCOUNT lub TopCount są w porządku), ale znowu możesz preferować przyjęcie tylko jednego stylu w standardzie, aby ułatwić czytanie. Przykład zastosowanego modelu W tym dokumencie zostanie podanych wiele przykładów zapytań dynamicznych. Wszystkie działają (dokładnie tak, jak zapisano, wystarczy skopiować i wkleić je do okna wyrażeń w edytorze podzbiorów o odpowiednim wymiarze, aby ich użyć) na prostym zestawie kostek i wymiarach pokazanych poniżej. Model jest celowo prosty i nie ma specjalnych cech, więc powinieneś łatwo przenieść pracę do swojego własnego modelu. Zastosowany model obejmował 1 główny wymiar, produkt, na którym działa ogromna większość zapytań oraz 3 moduły: test, test2 i test3. Wartości danych w modułach będą się różnić podczas testowania (będziesz chciał poprawić wartości i ponownie uruchomić zapytanie, aby upewnić się, że wyniki się zmieniają i są poprawne), ale poniższe zrzuty pokazują struktury kostki i wymiaru wystarczająco dobrze, aby szybko odtworzyć lub zamiast tego używać własnego modelu. 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. Your mileage may vary. A particularly clever use of Except is to check a TM1 dimension for a valid structure. A simple query can return a list of members that do not eventually roll up into a particular consolidated member. This could be included in a TI process to automate the consistency checking of dimensions after an update. This example returns all the members in the dimension that do not roll up into All Products: EXCEPT ( TM1SUBSETALL( Product ), TM1DRILLDOWNMEMBER( , ALL, RECURSIVE )) Modifying this slightly makes it return base-level members that do not roll up into All Products: EXCEPT ( TM1FILTERBYLEVEL(TM1SUBSETALL( Product ), 0), TM1FILTERBYLEVEL(TM1DRILLDOWNMEMBER( , ALL, RECURSIVE ), 0)) This query returns members that have been consolidated twice or more at some point under the given consolidated member this will often mean there has been an accidental double-count. EXCEPT ( TM1DRILLDOWNMEMBER( , ALL, RECURSIVE ), TM1SUBSETALL( Product ), ALL) It will return one instance of the multi-consolidated member for each time it is consolidated greater than once i. e. if it has been consolidated 4 times then it will return 3 instances. This is due to the fact that TM1SUBSETALL( Product ) will only return one instance of a member that has been consolidated multiple times while the TM1DrilldownMember function will return all the instances. You are reminded that Dimension. Member is actually a shortcut that usually works in TM1 but because the MDX specification allows for member names to be non-unique within a dimension the full address of a member is actually Dimension. Parent1.Parent2Member. Therefore more specific references to duplicate members may be needed, for example Product. Demand Loan. MidasHCBK will address a different instance of MidasHCBK than would Product. Discount Loan. MidasHCBK. In this case, with the Except function, they are treated as if they are different member names altogether. ToggleDrillState ToggleDrillState changes the default drill state from a returned set so if the first query returns a member in a hierarchy rolled up then it will drill it down, or vice versa. Using TM1 Subsets, TM1Member and TM1SubsetToSet One of the special features of using MDX with TM1 dimensions is that existing subsets can be used within the query for defining a new subset. This can be useful in allowing a simpler building block approach and for not having to repeat the same code over and over again and having to maintain it. Used throughout this section, Report Date is an existing subset in the Date dimension containing one leaf date member and test2 is an existing 20-member subset. Note that private subsets are used in preference to public subsets when there is one of each with the same name. This can allow a public subset to return different results based on the contents of different users private subsets, though inevitably with some issues with reliability of results. To simply return the member(s) of pre-existing Date subsets: Date. Report Date Or TM1SubsetToSet(Date, Report Date) The first syntax may be shorter and more convenient but bear in mind, as per the TM1 help file, Since the same syntax (.IDENTIFIER ) is used for members and levels, a subset with the same name of a member or a level will never be instantiated. The second syntax on the other hand will happily work with any subset names even if they are named the same as a cube or dimension. To return the first member of the test2 subset: To return a valid cube reference within a more complex query: TM1Member(Date. Current Date. Item(0), 0) For example: , 1), Reconciliation.(Entity. All Entities, TM1Member(Date. Current Date. Item(0),0),Reconciliation Measures. Transaction Balance) To start with the fourth item (.Item counts from zero) in the test2 subset and then return the preceding 14 members from the whole dimension, including the fourth item: This example returns the one date in Report Date and the next 13 periods, sorted with the earliest date first a moving 2-week reporting window which just needs the Report Date subset to be maintained. This query uses another subset, Strategic Products, as a building block and finds the Top 5 members within it, even though this ranking may well have been based on different values than the original subset was built on. For example, a subset that is already defined may list the 10 highest spending customer segments in terms of year to date actuals, and you then build a new subset that works with these 10 only to find the top 5 in terms of planned marketing spend next quarter. , 5, Test.(Posting Measures. Count)), Test.(Posting Measures. Count), BDESC) Heres a bigger example using TM1member and TM1SubsetToSet functions, in addition to various others. It takes the single period in the Current Date subset and returns the last day of the two preceding months. There would be several different ways of achieving the same result. union( tm1member(tm1subsettoset(Date, Current Date).item(0),0),1) )))), tm1member(tm1subsettoset(Date, Current Date).item(0),0),1) )))) ) Username and StrToMember It returns the TM1 username (or Windows domain username depending on the security system being used for example, GERJEREMY) of the user who runs the query. Note that you may need to give all users Read access to the Clients dimension and all its elements. It is not documented in the help file as being officially supported by TM1 but it is a standard MDX feature that appears to work in v8.3. However, since 8.4.3 until 9.1.2.49 it is reported as failing to automatically update when a new user uses the subset. This can be circumvented by running a frequent TI process that uses the subset as its datasource and the following line in Prolog (Workaround reported by Steve Vincent on the Applix Forum, 2nd August 2006): DIMENSIONSORTORDER(CLIENTS, BYNAME, ASCENDING,,) With this micro-process workaround set to run every few minutes a pseudo-dynamic result is possible. An actual solution to the problem should be tested for in your version if it is 9.1 or later. To save a dynamic subset it needs to be set up on the Clients dimension choose View Control Objects in Server Explorer to see this dimension. Once you have saved the public subset (e. g. as Current User) you can turn this option off again. Clients ), USERNAME ) As an alternative to the above method, and as a way of including the current username directly in queries use the StrToMember function which converts a plain string into a valid MDX member reference. Clients. USERNAME) Either way the subset can then be referred to on Excel spreadsheets, VBA processes and, as it is simply a standard TM1 subset, in TM1 Websheets. As a non-MDX alternative v9.1.2.49 introduced a TM1User(servername) worksheet function which could be used in some circumstances. Data-based queries, Filter, Sum, Avg and Stdev Sometimes it is not adequate to simply use a single value in a query you need to consider a combination of values. It might be that this combination is only needed for one or two queries, though, so it is not desirable to calculate and store the result in the cube for all to see. Therefore it is more logical to quickly calculate the result on the fly and although this is then repeated every time the subset is used, it is still the preferred choice. The function Sum, Avg and Stdev are therefore useful for things that are only needed occasionally or by a limited number of users and means that the actual cube is thus smaller and more efficient. SUM, as it might appear, sums up a set of numbers. This allow the aggregation of members not already consolidated in the model. This example checks the Test3 cube for products whose Amounts in the on-the-fly-consolidation of 2 entities are greater than 50. , 0), SUM( , Test3.(Posting Measures. Amount) ) gt 50 ) AVG calculates the average value of a set. Note how empty (zero) cells are not included by the AVG function so the resulting average value might be higher than you expected. This example returns a list of leaf products that have an Amount value in the Test cube higher than the average Amount value of all leaf products (or rather all non-zero leaf products). , 0), (Test. Posting Measures. Amount gt AVG( , 0), Test.(Posting Measures. Amount)) ) ) The set of members that AVG works on here (AVG , 0)) can be changed to something that doesnt match the list of members being filtered earlier in the query. For example, return a list of all leaf products that are higher than the average of the leaf descendants of the Customer Lending consolidation only. , 0), (Test. Posting Measures. Amount gt AVG(,Test.(Posting Measures. Amount))) ) STDEV is the standard deviation function. It returns the average distance from each value in a set to the average of the set as a whole. In this way you can calculate how consistent or unpredictable a set of data is if all the values lie tightly around the average, or if the values vary to be extremely high and low. This example returns the outlying products whose Amount value in the Test cube is greater than the average value plus the standard deviation i. e. those products who have values that are above averagely above the average. , 0), ( Test. Posting Measures. Amount gt ( AVG( , 0), Test.(Posting Measures. Amount)) STDEV( , 0), Test.(Posting Measures. Amount) gt 0 ) ) ) ) ) Note that the AVG function automatically drops empty cells from the filtering set but STDEV does not so we have to apply our own filter. The above queries could be INTERSECTed for both sets of outliers in one subset, if required. Further reading: The MEDIAN function is also supported by TM1 and might be more appropriate than AVG (mean) in some circumstances. Using parameters in queries TM1Member will allow you to use parameterized references by using cube values as part of the query itself. For example if a UserParams cube was created with the Clients dimension (thus allowing concurrent usage by all users) which would hold various choices made by users as they used your application, then dynamic subsets could use those choices as part of their syntax, thus altering not just the thresholds for comparisons (we can see elsewhere in this document how to check if something is, say, above a certain threshold which is actually a value in another cube) but the actual thing that is queried in the first place. For example, this shows the descendants of a parent member, the name of which is held in the 2D UserParams cube at the intersection of the current username and SelectedParentDimix. TM1SUBSETALL( Product ).Item(UserParams.(StrToMember(Clients. USERNAME), UserParamMeasures. SelectedParentDimix)-1) . 0)) Below are screenshots showing the parameter cube which can be extended to hold various user-specific selections and then link them into dynamic subsets plus the other relevant screens. The Generate function applies a second set to each member of a first set, performing a union of the results. Duplicates are dropped by default but can be retained with, ALL. Although Generate doesnt really do anything unique in itself it is a very useful way of shortening what would otherwise be long, laborious and error-prone queries. In the following example the top performing child product is returned for each member of Level 1 of the hierarchy: GENERATE( , 1), TopCount(Descendants(Product. CurrentMember, 1),1,Test.(Posting Measures. Amount))),Test.(Posting Measures. Amount) gt 0 ) Count and IIF Caveat: Note that IIF is not listed in the TM1 v9.0 SP2 help file as being supported so use at your own risk. Count returns the number of items in a set but this set can be a set of members or a set of data values. The result is, obviously, a number and is often returned in reports when used in MDX queries outside of TM1. When trying to use it do define a TM1 subset it can only be used as part of the query logic and not as a result itself. Count can be wrapped around a lot of the other MDX functions and so can be used in many different scenarios. One example is to count how many children a month has and, if there are 28, doing something that is unique to February. Although dimension subsets are usually a list of meaningful items in a business model and are included within application cubes, it is actually possible to have dimensions for administrator purposes only (that are never used to build cubes) which might indicate the state of something e. g. All Passwords Set, or Reconciliation Failed and the Count function could be used to define a subset that contains one of these members, which is information for the administrator only. IIF allows you to introduce some branching logic in your queries i. e. do one thing if this is true, otherwise do something else. You could use it to apply different statistical functions to members that have certain attributes. It works quite commonly with Count to allow one thing to happen if the count of something falls below a threshold, or do something else if not. This example performs either a Top 5 or a Top 10 on all base products Amounts in the Test cube, depending on whether the number of base level Products is 10 or less at the time the query is run. , 0), IIF(Count( , 0)) lt 10, 5, 10), Test.(Posting Measures. Amount) ) This example does a TopCount of the base products based on their Amount value in the Test cube where the number of items displayed is equal to the number of cells in the Test cube whose Amount value is anything other than zero. , 0), Count( Filter( , 0), Test.(Posting Measures. Amount) These are fairly pointless examples, practically speaking, but they show the syntax. Comments allow you to explain, to yourself andor to your users, what the query is trying to achieve, how it works, who wrote it or amended, etc. Use or (without the double quotes) to end a line with a comment or to have the comment on its own line. You can also use COMMENT (again without the quotes) to insert a comment in the middle of a line. You are also able to type anything after the command. This heavily-commented example returns all the products beginning MidasJ: Comment number 1 this is another comment -- and another comment , this is yet another comment MidasJ) You seem to be able to type what you like here, but treat with caution This does not work in version 8.2.7 but does in at least 9.0 and 9.1.1. AsciiOutput can help tracking what values are being used during execution of your TI processes. Keep in mind Asciioutput limitations: . it is limited to 1024 characters per line . it can deal only with strings . so you need to apply the NumberToString() function to all numeric variables that you would like to display like var3 in the example above. . it will openclose the file at every step of the TI. PrologMetadataDataEpilog that means if you use the same filename to dump your variables in any of these, it will be overwritten by the previous tab process. Hence you should use different filenames in each tab. . use DataSourceASCIIQuoteCharacter in prolog if you want to get rid of the quotes in output. . use DatasourceASCIIThousandSeparator to remove thousand separators. Alternatively you can use ItemReject if the record you step through is rejected, it will then be dumped to the error message ItemReject(var1var2) Dynamic Formatting It is possible to preformat dynamic slices by using the Edit Element Formats button in the subset editor. However that formatting is static and will not apply to new elements of a slowly changing dimension. Also it takes a long time to loadsave when you try to apply it to more than a few dozen elements. As an example, we will demonstrate how to dynamically alternate row colors in a TM1 report for a Customer dimension. Open subset editor for the Customer dimension. Select some elements and click Edit Elements Format. the Edit Element Formats worksheet opens, just click Save as colored row this creates the DimensionFormatStyles Customer dimension and the DimensionFormats Customer cube. Now we can modify this cube with our rules. open the rules editor for the DimensionFormatsCustomer cube, add this: alternate row colors for all elements colored row, Cond1Type S: 2 colored row, Cond1Formula1 S: MOD(ROW(),2)1 colored row, Cond1InteriorColorIndex S: 34 colored row, Cond2Type S: 2 colored row, Cond2Formula1 S: MOD(ROW(),2)0 colored row, Cond2InteriorColorIndex S: 2 To create a different style:.edit one element from the Edit element format, apply the desired formatting and save. note the new values of the measures in the DimensionFormatsCustomer cube for that element. reflect these changes in the rules to apply to all elements for that style There is also another rule-based formatting article on the Applix recommended practices website. dynamic SQL queries with TI parameters It is possible to use parameters in the SQL statement of Turbo Integrator to produce dynamic ODBC queries Here is how to proceed: 1. create your TI process 2. Advanced-gtParameters Tab, insert parameter p0 3. Advanced-gtProlog Tab add the processing code to define parameter p0 example: p0 CellGetS(cube, dim1,dim2. ) 4. save the TI process 5. open Data Source, add parameter p0 in WHERE clause example select from lib. table where name p0 DO NOT CLICK ON THE VARIABLES TAB AT ANY TIME 6. run, answer keep all variables when prompted If you need numeric parameters, there is a twist numeric parameters do not work (at least for TM1 9.x) example: select from lib. customer where purchase gt p0 will fail although p0 is defined as a numeric and quotes have been removed accordingly. But fear not, there is a simple workaround 1. proceed as above up to step 5 2. Advanced-gtProlog tab, at the bottom: strp0 NumberToString(p0) 3. Data Source tab, in the SQL statement replace p0 with CAST(strp0 as int) example: select from lib. customer where purchase gt p0 becomes select from lib. customer where purchase gt CAST(strp0 as int) clicking the preview button will not show anything but the process will work as you can verify by placing an asciioutput in the Advanced-gtData tab. The CAST function is standard SQL so that should be working for any type of SQL server. renaming elements renaming elements without activating aliases Yes we can The dimension editor and dimension worksheets cannot rename elements directly, so let me introduce you to SwapAliasWithPrincipalName . create a new alias new in the Dimension, by default the new elements are identical. change all required elements to their new names in that alias. Below we pad a zero in front of all elements create a new TI with the following line in the Prolog: The third parameter needs to be zero to execute the swap. If it has any other purpose, please editleave a comment. Make sure the associated dimension worksheet is updated if there is any. This was tested successfully under v. 9.0.3 and v. 9.4. This TI function is listed in the documentation. however there is no description of its function and syntax. Click on the RTFM FAIL tag to find out some other poorly documented or simply undocumented TM1 functions. dimension elements with a quote character () require a double quote to be interpreted correctly by the rules engine example: department s - gt department s after modifying cells through rules, the consolidations of these cells wont match the new values. To reconciliate consolidations add in your rules: Total ConsolidateChildren( dimension ) scheduling chores on calendar events Scheduling chores in TM1 can be frustrating as it does not offer to run on specific dates or other types of events. The following article explains how to create chores schedules as flexible as you need them to be. From the Server Explorer . create a new process . go directly in Advanced-gtProlog . add this code: run chore every 1st day of the month If(SUBST(TODAY,7,2) Another example to run on specific days of the week: DayOfWeek Mod ( DayNo( TODAY ) 21915, 7) 0 Sunday, 1 Monday to 6 Saturday. If( DayOfWeek 0 DayOfWeek gt 4 ) ChoreQuit send emailattachments It is possible to send email alerts or reports as attachments from Turbo Integrator. This can be achieved by executing a VB script. 1. save the attached VB script on your TM1 server 2. create a TI process 3. in Epilog add the following code: SRuncmd c D:pathtoSendMail. vbs smtp. mycompany 25 116109496410912199111109112971101214699111109 1091016410912199111109112971101214699111109 Today report check it out E:TM1Reportstodaysreport. xls ExecuteCommand(SRun,0) The syntax is: SendMail. vbs server port sender destination subject body attachment so replace the fields as required to suit your setup The DOS command line is limited to 255 characters so avoid putting too much information in the body. If a field contains a blank sp ace you must enclose that field in quotes so the script gets the correct parameters code from kckang (applix forum) and rondebruin. nlcdo. htm Applix is developing a tool called Report Connect currently in the beta stage, which will integrate with TM1 and allow users to schedule automated reports emailing from the TM1 server silence is fool039s gold TM1 processes will not complain when their input source is empty. So although the process successful or chore successful message will popup, your cube will remain desperately empty. In order to solve that silent bug (or Cogglix feature), you will need to add specific code to your TI processes to test against empty sources. Here follows: initialise counter PROLOG TAB increment counter DATA TAB check counter value at the end and take appropriate action EPILOG TAB ItemReject will send the error to the msg log and the execution status box will signal a minor error. the fallacy of blb files TM1 system files with the blb extension, incorrectly referenced as cube formatting files (admin guide p.35) are actually rule formatting files for the standard rules editor (prior to 9.1). The rules editor actually displays the contents of the. blb if there is one, otherwise it defaults to the. rux. Unfortunately things can go wrong, and the. blb file gets desynchronised from the actual. rux or just go blank. As a result, what you see in the rule editor are NOT the rules attached to your cube and it becomes tricky to pinpoint any issue as the rule tracer gets confused too. A simple fix is to delete the associated. blb file in the TM1 Data folder and reopen the rules in the rule editor. Well it works only until the next time it goes desynchronised or blank. From 9.1, you can turn on the new rules editor from the tm1s. cfg: AdvancedRulesEditor T Ultimately if you really cannot do without formatting, consider using an editor with highlighting features and copypaste the rules. Attached below is a very basic lint tool whose task is to flag dangerous TM1 rules. For now it is only checking for aliases in rules. Indeed, if an alias is changed or deleted, any rule based on that alias will stop working without any warning from the system. The values will remain in place until the cube or its rules gets reloaded but you will only get a silent warning in the messages log after reloading the cube. How to proceed:.configure and execute the following TI process (to put in prolog), this will generate a list of all cubes and associated dimensions, and a dictionary of all aliases on the system in. csv format configure and execute the perl script attached below that script will load the csv files generated earlier in hash tables, scan all rules files and finally report any aliases. If the element is ambiguous because it is present in 2 different dimensions then you should write it as dimension:element instead of using aliases (e. g. write Account:71010 instead of 71010). TM1 operators logical operators: amp AND OR strings operators: concatenate string1 equals string2 disabling the DEL key to forbid users from deleting DBRW formulas Private Sub WorkbookActivate() DisableDel End Sub Private Sub WorkbookDeactivate() EnableDel End Sub Private Sub WorkbookBeforeClose(Cancel As Boolean) a MsgBox(Your data have already been saved in Tm1, you dont need to save this Excel slice, vbInformation) EnableDel ActiveWorkbook. Close False End Sub Sub DisableDel() Application. OnKey , SendSpace MsgBox Delete Key Disable End Sub Sub EnableDel() Application. OnKey MsgBox Delete Key Enable End Sub Sub SendSpace() MsgBox Delete key not allowed. Sending a space instead SendKeys TM1RECALC1. same as shift-F9, refreshes only the active worksheet TM1RECALC. same as F9, refreshes ALL open workbooks TM1REFRESH. same as Alt F9, rebuilds (dynamic spreadsheets) and refreshes ALL open workbooks TM1StartOrionWithAutomation. opens Server Explorer CUBESBROWSE. opens Server Explorer SUBDELETE: deletes a subset (if unused) ex: Application. Run(SUBDELETE, myserver:account, MySubset) TM1InsertViewControl. starts In Spreadsheet browser TWHELP. opens TM1 perspectives help TWDEFAULTS. opens TM1 Options menu TWMERUL. opens rules worksheets menu TWMEDIM. opens dimensions worksheets menu to be continued. Excel formulas references When editing a cell formula ( F2 ), you can easily toggle between relative and absolute references with the F4 key: B10 - F4-gt B10 - F4-gt B10 - F4-gt B10 zero out portions In order to zero out data points you can do it either: - from Cube Viewer select all the cells with the pointer right click: Data Spread-gtClear. or select the top left corner cell of the portion to zero out right click: Data Spread-gtRepeat set value box to 0 and tick boxes Extend right and down - from a TI process in the prolog tab: ViewZeroOut(Cube, View) setup the View to zero out then run that process This section is dedicated to various material for admins: monitoring, troubleshooting, optimising. Before running the following script you need to setup a chore with a TI process to execute that line in Prolog: Avoid using the SaveTime setting in tm1s. cfg as it could conflict with other choresprocesses trying to run at the same time here is the DOS backup script that you can schedule to backup your TM1 server Documenting TM1 section dedicated to documenting TM1 with different techniques and tools. a closer look at chores if you ever loaded a. cho file in an editor this is what you would expect: 534,8 530,yyyymmddhhmmss ------ datetime of the first run 531,dddhhmmss ------ frequency 532,p ------ number p of processes to run 13,16 6,process name 560,0 13,16 533,x ------ x1 active x0 inactive In the 9.1 series it is possible to see from the Server Explorer which chores are active from the chores menu. However this is not the case in the 9.0 series, also it is not possible to see when and how often the chores are running unless you deactivate them first and edit them. Not quite convenient to say the least. From the specs above, it is easy to set rules for a parser and deliver all that information in a simple report. So the perl script attached below is doing just that: listing all chores on your server, their datetime of execution, frequency and activity status. Procedure to follow: 1. install perl 2. save chores. pl in a folder 3. doubleclick on chores. pl 4. a window opens, enter the path to your TM1 server data folder there 5. open resulting file chores. txt created in the same folder as chores. pl a closer look at subsets if you ever loaded a. sub file (subset) in an editor this is the format you would expect: 283,2 start 11,yyyymmddhhmmss creation date 274,string name of the alias to display 18,0 275,d d number of characters of the MDX expression stored on the next line 278,0 281,b b 0 or 1 expand above trigger 270,d d number of elements in the subset followed by the list of these elements, this also represents the set of elements of if you have an MDX expression attached These. sub files are stored in cube subs folders for public subsets or user cube subs for private subsets. Often a source of discrepancy in views and reports is the use of static subsets. For example a view was created a while ago, displaying a bunch of customers, but since then new customers got added in the system and they will not appear in that view unless they are manually added to the static subset. Based on the details above, one could search for all non-MDXstatic subsets (wingrep regexp search 275, in all. sub files) and identify which might actually need to be made dynamic in order to keep up with slowly changing dimensions. Beam me up Scotty: 3D Animated TM1 Data Flow Explore the structure of your TM1 system through the Skyrails 3D interface: If you do not have flash, you can have a look at some screenshots WARNING: your eyeballs may pop out This is basically the same as the previous work with graphviz. except this time it is pushed to 3D, animated and interactive. So the visualisation engine Skyrails is developed by Ph. D. student Yose Widjaja. I only wrote the TM1 parser and associated Skyrails script to port a high level view of the TM1 Data flow into the Skyrails realm. download and unzip skyrails beta 2nd build. download and unzip TM1skyrails. zip (attachment below) in the skyraildist2 folder. in the skyraildist2 folder, doubleclick TM1skyrails. pl (you will need perl installed unless someone wants to provide a compiled. exe of the script with the PAR module).enter the path to (a copy of) your TM1 Data folder. skyrails window opens, click on the folder icon and click TM1 If you dont want to install perl, you can still enjoy a preview of the Planning Sample that comes out of the box. Just double-click on raex. exe . w, s,a, d keys to move the camera Quick legend : orange -- cube blue -- process light cyan -- file red -- ODBC source green sphere -- probably reference to an object that does not exists (anymore) green edge: intercube rule flow red edge: process (CellGetCellPut) flow Changelog: 1.1 a few mouse gestures added (right click on a node then follow instructions) to get planar (like graphviz) and spherical representations. 1.2 - edges color coded, see legend above - animated arrows - gestures to display different flows (no flowrules onlyprocesses onlyall flow) Dimensions updates mapping When faced with a large undocumented TM1 server, it might become hard to see how dimensions are being updated. The following perlgraphviz script creates a graph to display which processes are updating dimensions. That script dimflow. pl below is looking for functions updating dimensions (DimensionElementInsert, DimensionCreate. ) in. pro files in the TM1 datafolder and maps it all together. Unfortunately it does not take into account manual editing of dimensions. This is the result: The above screenshot is probably a good example of why such map can be useful: you can see immediately that several processes are updating the same dimensions. It might be necessary to have several processes feeding a dimension, though it will be good to review these processes to make sure they are not redundant or damaging each others effects. Procedure to follow: 1. install perl and graphviz 2. download the script below and rename it to. pl extension 3. doubleclick on it 4. enter the path to your TM1 Data folder (servernamedatafolder) 5. This will create 2 files dim. dot and dim. gif in the same folder as the perl script 6. Open dim. gif with any browser picture editor graphing TM1 data flow Attached is the new version of a little parser in perl (free) that will create a text file for graphviz (free too) out of your. pro and. rux files and then generate a graph of the data flow in your TM1 server. (the image has been cropped and scaled down for display, the original image is actually readable) legend ellipses cubes, rectangles processes red cellget, blue cellput, green inter-cube rule Procedure to follow : 1. install perl and graphviz 2. put the genflow perl script in any folder, make sure it has the. pl extension (not txt) 3. doubleclick on it 4. Enter the path to your TM1 Data folder such as: servernamedatafolder where servernamedatafolder is the full file path to your TM1 data folder 5. Hit return and wait until the window disappears This creates 2 files: flow. dot and flow. gif in the same folder as the perl script 6. Open flow. gif in any browser or picture editor Changelog 1.3 :.CellPut parsing fix. cubesprocesses names displayed as is 1.4 :.display import view names along the edges. display zeroout views. sources differentiated by shape This is still quite experimental but this could become useful to view at a glance high-level interactions between your cubes. indexing subsets Maintaining subsets on your server might be problematic. For example you wanted to delete an old subset that you found out to be incorrect and your server replied this: This is not quite helpful, as it does not say which views are affected and need to be corrected. Worse is that, as Admin, you can delete any public subset as long as it is not being used in a public view. If it is used in a users private view, it will be deleted anyway and that private view might become invalid or just wont load. In order to remediate to these issues, I wrote a little perl script, attached below, that will:.index all your subsets, including users subsets..display all unused subsets (i. e. not attached to any existing views) From the index, you can find out right away in which views a given subset is used. I suppose the same could be achieved through the TM1 API though you would have to log as every user in turn in order to get a full index of all subsets. Run from a DOS shell: perl indexsubset. pl pathtoTM1server gt mysubsets. txt processes history On a large undocumented and mature TM1 server you might find yourself with a lot of processes and you wonder how many of them are still in use or the last time they got run. The following script answers these questions for you. One could take a look at the creationmodification time of the processes in the TM1 Data folder however you would have to sit through pages of the tms1msg. log to get the history of a given process which is what the script below does. Procedure to follow for TM1 9.0 or 8.x 1. install perl (free) 2. save loganalysis. pl. txt in a folder as loganalysis. pl 3. stop your TM1 service (necessary to beat the windows lock on tm1smsg. log) 4. copy the tm1smsg. log into the folder where loganalysis. pl is 5. start your TM1 service 6. double click loganalysis. pl Procedure to follow for TM1 9.1 1. install perl (free) 2. save loganalysis. pl. txt in a folder as loganalysis. pl 3. copy the tm1server. log into the folder where loganalysis. pl is 4. double click loganalysis. pl That should display the newly created processes. txt in notepad and that should look like the following: First, all processes sorted by name and the last run time, user and how many times it ran. Second, all processes sorted by last run time, user and how many times it ran. I do not know what these BrandAnalysisUpdate or LoadDelivery processes do but I guess nobody is going to miss them. The case against single children I came across hierarchies holding single children. While creating a consolidation over only 1 element might make sense in some hierarchies, some people just use consolidations as an alternative to aliases. Either they just dont know they exist or they come from an age when TM1 did not have aliases yet. The following process will help you identify all the single child elements in your system. This effectively loops through all elements of all dimensions of your system, so this could be reused to carry out other checks. TM1 Documenter (a Documenting tool) Just FYR, New Version of TM1 Documenter Version2.5 (A documenting tool) has been released. I believe, it will very useful to TM1 Consultants amp Developers, and the Organisations as well. Myself being a TM1 Consultant, know the pain-areas of a Consultant. Having a Java background I developed this software. Usually documentation task takes about 20-40 days, thereby blocking a valuable resource (TM1 Developer) for such less-important task. By using this software, the documentation task will be completed in just few clicks. Moreover during development or support task, when the model becomes huge or complex sometimes people lose the exact data flow of the model (as Rule sets are difficult to understand). Some times a developer, by mistake, deletes an object (be it a cube, dimension, element, or a subset) that is providing is data to some other object. TM1 do not disallow to do so, but the model goes on a toss. Here the Object Dependency Checker comes to rescue. Software Summary: It has in all 2 Main Modules. I have introduced one new module in it, i. e. Object Dependency Checker Following are the details: Documenter Module: 1. Detailed information summary of the cubes. 2. Cube Sizing. 3. Views info (optional). 4. Rules info (optional). 5. Detailed information summary of the Dimensions. 6. Subsets info (optional). 7. Export TI Process list. 8. Export Dimension Attributes 9. Export Element Attributes 10.Output html file with index on left side amp Object details in center pane that make easy to navigate the objects. Object Dependency Checker Module: 1. Cube Dependency: Select a cube amp see all the other cubes depending on this. (i. e. view all cubes that are been sourced by this cube) 2. Dimension Dependency: Select a DIM amp see all cubes amp rules using this DIM. It also displays if this Dim is being used as Picklist anywhere. 3. Element Dependency: Select an Element from Dim amp see all cubes using. specifically, this element Data. 4. Subset Dependency: Select a Subset amp see all the views using this subset. It also displays if this Subset is being used as Picklist anywhere. 5. Cube-Element Dependency: This is detailed level element dependency. This module checks if the data of selected element of the selected cube is being used anywhere. 6. Export option available for all above sub-modules. Your suggestions amp queries welcome. Please provide me feedback of this tool. dynamic tm1p. ini and homepages in Excel Pointing all your users to a single TM1 Admin host is convenient but not flexible if you manage several TM1 services. Each TM1 service might need different settings and you do not necessarily want users to be able to see the development or test services for example. Attached below is an addin that logs the users on a predefined server and settings as shown on that graph: With such a setup, you can basculate your users from one server to the other without having to tinker the tm1p. ini files on every single desktop. This solution probably offers the most flexibility and maintainability as you could add conditional statements to point different groups of users to different serverssettings and even manage and retrieve these settings from a cube through the API. This addin also includes: - previous code like the TM1 freeze button - it loads automatically an excel spreadsheet named after the user so each user can customise it with their reportslinks for a faster access to their data. The TM1 macro OPTSET . used in the. xla below, can preconfigure the tm1p. ini with a lot more values. The official TM1 Help does not reference all the available values though. Here is a more complete list, you can actually change all the parameters displayed in the Server Explorer File-gtOptions with OPTSET: AdminHost DataBaseDirectory IntegratedLogin ConnectLocalAtStartup InProcessLocalServer TM1PostScriptPrinter HttpProxyServerHost HttpProxyServerPort UseHttpProxyServer HttpConnectorUrl UseHttpConnector AnsiFiles GenDBRW NoChangeMessage DimensionDownloadMaxSize this also applies to OPTGET WARNING: Make sure that all hosts in the AdminHost line are up and working otherwise ArchitectPerspectives will hang for a couple of seconds while trying to connect to these hosts. free utilities If you are stuck with a Windows operating system, you might need some tools for basic needs, these are all free: for bruteregexp search in. pro and. rux files: WinGrep finding out changes in different versions of files: WinMerge or the PSPad editor Windirstat is quite a useful visualisation tool to clean up your drivesservers. A picture is worth a thousand words, take a look at the screenshot . It was first developed for KDE: kdirstat SnagIt can capture scrolling long web pages, extract text from windows, annotate images and more. Read the detailed SnagIt review . download here free license subscription here (thanks Eric ) A few more tools from lifehacker And finally, not a desktop tool per se, but quite useful to share files quick, easy and secure: drop. io How to monitor TM1 connections using a Java application download --gt TM1Shell. rar . This program is a shell that allows you to connect to a TM1 server running. Then it starts two threads : - One write in a logfile the log activity on the server and save it - The second allows you to have a description of dimensions, cubes, elements. and to export it into an Excel File. Use cmd to draw a list of available commands. The project is under construction, any suggestions welcome. A new version is coming out soon. If you have any questions, you can contact me. 1081179997115461061111051031109711712064103109971051084699111109 Locking and updating locked cubes Locking cubes is a good way to insure your (meta)data is not tampered with. Right click on the cube you wish to lock, then select Security - gt Lock . This now protects the cube contents from TI process and (un)intentional admins changes. However, this makes updating your (meta)data more time consuming, as you need to remove the lock prior to updating the cube. Hopefully, the function CubeLockOverride allows you to automate that step. The following TI code demonstrates this,.lock a cube. copypaste the code in a TI Prolog tab. change the parameters to fit your system. execute: Note: CubeLockOverride is in the reserved words listed in the TM1 manual but its function seems to be only documented in the 8.4.5 releases notes . This works from 8.4.5 to the most recent 9.x series managing the licences limit One day you might face or already faced the problem of too many licences being in use and as a result additional users cannot log in. Also on a default setup, nothing stops users from opening several tm1webperspectives sessions and reach the limit of licenses. So in order to prevent that: open the cube ClientProperties, change all users MaximumPorts to 1.in your tm1s. cfg add that line, it will timeout all idle connections after 1 hour: IdleConnectionTimeOutSeconds 3600 To see whos logged on :.use tm1top or. open the cube ClientProperties all logged users have the STATUS measure set to ACTIVE or. in server manager (rightclick server icon), click Select clients. to get the list To kick some users without taking the server down: in server explorer right click on your server icon - gt Server Manager select disconnect clients and Select clients. then OK and they are gone. Unfortunately there is still no workaround for the admin to log in when users take all the slots allowed. monitor rules and processes Changing a rule or process in TM1 does not show up in the logs. That is fine as long as you are the only Power User able to tinker with these objects. Unfortunately, it can get out of hand pretty quickly as more power users join the party and make changes that might impact other departments data. So here goes a simple way to report changes. The idea is to compare the current files on the production server with a backup from the previous day. You will need:.access to the live TM1 Data Folder. access to the last daily backup. a VB script to email results you can find one there. diff, egrep and unix2dos, you can extract these from that zip package and efghsoftwareunix2dos. exe or download directly the attachments below (GNU license) Dump these files in D:TM1DATABIN for example, or some path accessible to the TM1 server. In the same folder create a diff. bat file, replace all the TM1DATA paths to your configuration: Now you can set a TM1 process with the following line to run diff. bat and schedule it from a chore. Best is to run the process at close of business, just before creating the backup of the day. And you should start receiving emails like these: In this case we can see that the rules from the Productivity cube have changed today. monitoring chores by email Using the script in the Send Email Attachments article, it is possible to set it up to automatically email the Admin when a process in a chore fails. Here is how to proceed: 1. setup admin email process First we create a process to add an email field to the ClientProperties cube and add an email to forward to the Admin. 1.1 create a new process ---- AdvancedParameters Tab, insert this parameter: AdminEmail String Admin Email --- AdvancedProlog tab if(DIMIX(ClientProperties, Email) 0) DimensionElementInsert(ClientProperties,,Email, S) Endif --- AdvancedEpilog tab CellPutS(AdminEmail, ClientProperties, Admin, Email) 1.2 Save and Run 2. create monitor process ---- AdvancedProlog tab MailServer smtp. mycompany LogDir tm1servereTM1DataLog ScriptDir E:TM1Data If(ProcessReturnCode mind that future TM1 versions might use a different format for. cho files and that might break this script If(Tag 6) MailServer mail. myserver LogDir serverfTM1DatamyTM1Log get the process names from the deactivated chore ProcessMeasure NumericGlobalVariable( ProcessReturnCode) StringGlobalVariable(Status) The code only differs from the first method when the process returns a ChoreQuit exit. Because we will be running the chore Daily Update from another chore, the ChoreQuit will not apply to the later, so we need to specify it e xplicitly to respect the flow and stop at the same point. 2. create chore ProcessCheck just add the process above and set it to the same frequency and time as the Daily Update chore that you want to monitor 3. deactivate Daily Update since the ProcessCheck chore will run the Daily Update chore there is no need to execute Daily Update another time monitoring users logins a quick way to monitor users loginlogout on your system is to log the STATUS value (i. e. ACTIVE or blank) from the ClientProperties cube. View-gtDisplay Control Objects Cubes - rightclick - Security Assignments browse down to the ClientProperties cube and make sure the Logging box is checked tm1server - rightclick - View Transaction Log Select Cubes: ClientProperties All the transactions are stored in the tm1s. log file, however if you are on a TM1 version prior to version 9.1 and hosted on a Windows server, the file will be locked. A Save Data will close the log file and add a timestamp to its name, so you can start playing with it. This trick does not work in TM1 9.1SP3 as it does not update the STATUS value. Oops I did it again OH NOOOEES A l user just ran that hazardous process or spreading on the production server and as a result trashed loads of data on your beloved server. You cannot afford to take the server down to get yesterdays backup and they need the data now. Fear not, the transaction log is here to save the day. in server explorer, right click on server-gtView Transaction Log. narrow the query as much as you can to the timeclientcubemeasures that you are after Mind the date is in north-american format mmddyyyy. Edit-gtSelect All. Edit-gtBack Out will rollback the selected entries Alternatively, you could get the last backup of the corresponding. cub of the damaged cube. in server explorer: right-click-gtunload cube. overwrite the. cub with the backed up. cub. reload the cube from server explorer by opening any view from it Out of Memory You will get the dreaded message Out of Memory if your TM1 server reaches beyond 2 GB of RAM. On top of adding more RAM, you also need to add the 3GB flag in C:boot. ini to extend the available space of the TM1 server from 2 to 3 GB, if you ever need more then you will have to look for a 64bit server. boot loader timeout10 defaultmulti(0)disk(0)rdisk(0)partition(1)WINDOWS operating systems multi(0)disk(0)rdisk(0)partition(1)WINDOWSMicrosoft Windows 2000 Advanced Server fastdetect multi(0)disk(0)rdisk(0)partition(1)WINDOWSrestore mode safeboot:dsrepair sos boot loader timeout10 defaultmulti(0)disk(0)rdisk(0)partition(1)WINDOWS operating systems multi(0)disk(0)rdisk(0)partition(1)WINDOWSMicrosoft Windows 2000 Advanced Server fastdetect 3GB multi(0)disk(0)rdisk(0)partition(1)WINDOWSrestore mode safeboot:dsrepair sos This trick will work only for Windows 2000 Advanced or Datacenter Server and Windows 2003 Enterprise or Datacenter Edition. It is also recommended that you restart your TM1 service daily in order to free up the RAM used from TM1 operations during the day. After a RAM upgrade to 3 GB, you might still get the Out of Memory message when you are importing a lot of data at once although your server itself is actually using only 2.5 GB. A way to circumvent that limit is to breakdown the import of data: use SaveDataAll to commit the changes on disk after some import then use CubeUnload( cube ) of the cube that you just updated. That will free some space that can be used for importing further data for other cubes and that space will be used back to load that cube later once someone opens a view from that cube . pushing data from an iSeries to TM1 TM1 chore scheduling is frequency based, i. e. it will run and try to pull data after a predefined period of time regardless of the availability of the data at the source. Unfortunately it can be a hit or miss and it can even become a maintenance issue when Daylight Saving Time come into play. Ideally you would need to import or get the data pushed to TM1 as soon as it is available. The following article shows one way of achieving that goal with an iSeries as the source. prerequesites on the TM1 server:.Mike Cowie s TIExecute or download it from the attachment below. iSeries Client Access components (iSeries Access for Windows Remote Command service) Procedure to follow 1. drop TM1ChoreExecute, TM1ProcessExecute, associated files and the 32bit TM1 API dlls in a folder on the TM1 server (see readme in the zip for details) 2. start iSeries Access for Windows Remote Command on the TM1 server, set as automatic and select a user that can execute the TM1ChoreExecute 3. in client access setup: set remote incoming command run as system generic security 3. on your iSeries, add the following command after all your queriesextracts: RUNRMTCMD CMD(start D:pathtoTM1ChoreExecute AdminServer TM1Server UserID Password ChoreName) RMTLOCNAME(10.xx. x.xx IP) WAITTIME(10) 10.xx. x.xx IP of your TM1 server D:pathto path where the TM1ChoreExecute is stored AdminServer name of machine running the Admin Server service on your network. TM1Server visible name of your TM1 Server (not the machine name of the machine running TM1. UserID TM1 user ID with credentials to execute the chore. Password TM1 user IDs password to the TM1 Server. ChoreName name of requested chore to be run to load data from the iSeries. You should consider setting a userpass to restrict access to the iSeries remote service and avoid abuse. But ideally an equivalent of TM1ChoreExecute should be compiled and executed directly from the iSeries. store any files in the Applications folder The Applications folder is great but limited to views and xls files, well not anymore ). The following explains how to make available just any file in your Applications folders. 1. create a file called myfile. blob in Applications on your TM1 server it should contain the following 3 lines: ENTRYNAMEtutorial. pdf ENTRYTYPEblob ENTRYREFERENCETM:blobpublic. Externalstutorial. pdf 2. place your file, tutorial. pdf in this case, in Externals or whatever path you defined in ENTRYREFERENCE 3. restart your TM1 service ENTRYNAME is the name that will be displayed in Server Explorer. ENTRYREFERENCE is the path to your actual file. The file does not need to be in the folder Externals but the server must be able to access it avoid large files, there is no sign to tell you to wait while loading, impatient users might click several times on the file and unvoluntarily flood the server or themselves. add the extension in ENTRYNAME to avoid confusion, although it is not a. xls file, it will be displayed with an XLS icon. TM1 services on the command line removing a TM1 service in a DOS shell: go to the bin folder where TM1 is installed then: tm1sd - remove TM1 Service where TM1 Service is the name of an existing TM1 service or: sc delete TM1 Service removing the TM1 Admin services sc delete tm1admsdx64 sc delete TM1ExcelService installing a TM1 service in a DOS shell: go to the bin folder where TM1 is installed then: tm1sd - install TM1 Service DIRCONFIG where DIRCONFIG is the absolute path where the tm1s. cfg of your TM1 Service is stored manually starting a TM1 service from a DOS shell in the bin folder of the TM1 installation: tm1s - z DIRCONFIG remotely start a TM1 service netsvc start TM1server TM1 service sc TM1server start TM1 service remotely stop a TM1 service netsvc stop TM1server TM1 service sc TM1server stop TM1 service TM1 sudoku Beyond the purely ludic and mathematical aspects of sudoku. this code demonstrates how to set up dimensions, cubes, views, cell formating, security at elements and cells levels all through Turbo Integrator in just one process. Thanks to this application, you can prove your TM1 ROI. none of your company employees will ever need to shell out 1 for their daily sudoku from the Times. Alternatively, you could move your users to a probation group before they start their shift. It is only by completing successfully the sudoku that the users will be moved back to their original group. This way you can insure your company employees are mentally fit to carry out changes to the budget, especially after last evening ethylic abuses down the pub. Of course it exists many sudoku available for Excel, this is one is to be played primarily from the cube viewer, but you could also slice the view and play it from Excel too. How to install:.Save the processes in your TM1 folder and reload your server or copy the code directly to new turbo integrator processes..Execute Create Sudoku. That creates the cube, default view and new puzzle in less than a second. sudoku The user can input numbers in the input grid only where there are zeroes. The solution grid cannot be read by default..Execute Check Sudoku to verify your input grid matches the solution. If you are logged under an admin account, you will not see any cells locked, you need to be under the group defined in the process to see the cells properly locked. You might want to change the default group allowed to play and the number of initial pairs that are blanked in order to increase difficulty. The algorithm provided to generate the sudoku could be quickly modified to solve by brute force any sudoku. Provided the sudoku grid is valid, it will find the solution, however some sudokus with too many empty cells will have more than one solution. This post is published on April 1st, but I can assure you the code is not an Aprils fool, it works and it was tested on TM1 9.0.3. realtime monitoring of your TM1 server, pretty much like the GNU top command. It is bundled with TM1 only from version 9.1. You might have to ask your support contact to get it or get Ben Hills TM1Top below. dump the files in a folder . edit tm1top. ini, replace myserver and myadminhost with your setup servername myserver adminhost myadminhost refresh5 logfile C:tm1top. log logperiod0 logappendT run the tm1top. exe Commands: X exit W write display to a file H help V verifylogin to allow cancelling jobs C cancel threads, you must first login to use that command Keep in mind all it does is to insert a ProcessQuit command in the chosen thread. Hence it will not work if the user is calculating a large view or a TI is stuck in a loop where it never reads the next data record, as the quit command is entered for the next data line rather than the next line of code. Then your only option becomes to terminate the users connection with the server manager or API. (thanks Steve Vincent). Ben Kyro Hill did a great job developing a very convenient GUI TM1Top. You can find it attached below. tm1web customizer The tm1web customizer will allow you to change the default logos and color schemes of tm1web from a graphical interface. That is trying to make it more convenient to customize your tm1web without having to dig in the code. It can be found here: ftp:ftp. applixpubGruenesTM1WebAppCustomizer. zip However note that it is configured to work with 9SP1. TM1Web vs TM1 Server Explorer DeathMatch I have a strong dislike for TM1Web and here is why. Quick Traffic Analysis comparison On the recommended practices site from Applix, the following article TM1 Deployment Options and Network Bandwidth Considerations claims that TM1Web is more suited to low bandwidth networks. O RLY So I decided to give it a go with Wireshark. great network analysis tool, used to be known as Ethereal. I do 2 runs, one with Server Explorer (direct over TCPIP no HTTP), the other with TM1Web The analysis takes place between a Windows XP client and Windows 2000 Advanced server hosting TM1. Both are using TM1 9.0SP2, the only customisation brought to TM1Web was to remove the top left TM1 logo so that should have only a neglectable effect on the statistics. In each case:.close all connections to TM1 server. on the client host, Wireshark capture filter set to log only packets to and from the TM1 Server Capture - gt Options set Interface to the ethernet card in use set capture filter to that string: host TM1 server IP if the TM1 server has the IP 192.168.0.10 then the capture filter must be: host 192.168.0.10.check the capture baseline is flat to be sure there will be no other traffic. start logging packets just before opening the view. open a decent view, 412 rows x 8 columns. scroll through all the rows until bottom is reached. stop logging Results (in Wireshark, Statistics - gt Summary): 978 kBytes went through the network with TM1Web 150 kBytes went through the network with server explorercube viewer So much for saving bandwidth with TM1Web, it is actually consuming at least 5 times more traffic than Server Explorer. If I get more time I will look in the packets to see why there is so much overhead with TM1Web, my initial guess is this is caused by the additional HTTP protocol layer. This time I tried with another view, 7 dimensions, 415 rows by 9 columns similar results: 947 kB for TM1 Web 147 kB for cube viewer And I pushed the analysis a bit further. Wireshark Menu: Statistics - gt Protocol Hierarchy As you can see HTTP takes up only 8.7 of the total traffic, but that is already 47 kBytes just to embed data on the wire, cube viewer would have already transfered 30 of the view in the same amount of bytes Now lets breakdown the conversation between the client and server. From the Wireshark menu: Statistics - gt Conversation List - gt TCP The popup window now displays the TCP conversations by size, the fattest are at the bottom. So lets see what is causing all that traffic. Right click the last one: Apply As Filter-gt Selected - gt A--B then from the Wireshark menu: Analyze - gt Follow TCP Stream You can now see what makes up all that traffic, and the culprit is. OMG ALL THAT JUNK HTML CODE and that is sent every single time you press the little arrows to change the page on a view. You would think TM1Web would somehow send only the actual data and leave the formatting processing to the client (AJAX) to spare the network and boost response times, well it is just not the case. Future Developments attached to this page is a presentation from David Corbett made last October about the roadmap for current and future developments in TM1
No comments:
Post a Comment