Business Intelligence,Technology, Thoughts, Thinking

BI USER Group meeting (#28) - Wednesday, FEB 29th /2012 17:30

Ronen Chenn — Wed, 08 Feb 2012 08:21:00 GMT

יוזר גרופ BI מארח את יוזר גרופ SharePoint וSQL- למפגש מיוחד בנושא:

יום ד, 29 בפברואר, 17:30, בית מיקרוסופט רעננה

מרצים: ערן שגיא ויוסי אלקיים, קבוצת היועצים (MCS): מיקרוסופט ישראל

גרסת SQL Server 2012 אוטוטו כאן ומביאה איתה התחדשות והעצמה של פלטפורמה הבינה העסקית.

היום, יותר מתמיד, חוצה הבינה העסקית גם את עולמות ה-SQL וה-SharePoint ורלוונטית לבעלי תפקידים רבים בארגון: מנהליDBA ,IT ,מנתחי מערכות/פרויקטים ומפתחים.

הנכם מוזמנים למפגש מיוחד בו תוכלו להכיר את מפת הדרכים וללמוד כיצד להיערך לשלב הבא של הבינה העסקית בארגון.

במפגש נציג את פלטפורמת הבינה העסקית מקצה לקצה, משכבת התשתיות (SSIS , SSAS , SSRS)ועד לכלי הקצהSharePoint BI , Excel Power Pivot V2 , Power View ,Crescent... עוד נדגים את כלל הרכיבים והשירותים במחזור החיים של מתודולוגיית הECMהארגונית , מהן המשמעויות של בינה עסקית בשירות עצמי.

הסקירה המקיפה תסביר את תפיסת הSelf Service וכן את המודל בדור הבא של In Memory BI המבוסס על טכנולוגיית Vertipaq ותדגים את השכבה הסמנטית בפעולה (BISM - BI Semantic Model).

להרשמה והבטחת מקום חניה, יש להירשם ב- http://biug28.eventbrite.com/

לו"ז מתוכנן:

17:30 התכנסות

18:00 הצגת פלטפורמת הבינה העסקית מקצה לקצה, משכבת התשתיות (SSIS , SSAS , SSRS) ועד לכלי הקצה SharePoint BI , Excel Power Pivot V2 , Power View, Crescent

בערב זה, נתרכז ביקר בהדגמות חיות לגרסת הבטא (RC0) ששוחררה זה מכבר להתנסות ראשונית.

19:15 הפסקה

19:30 המשך

20:30 שאלות ותשובות

21:00 סיום משוער

המרצים:

יוסי אלקיים וערן שגיא- יועצים בכירים בחטיבת הייעוץ של מיקרוסופט ישראל. בעלי ניסיון רב במערכות ה-BI של מיקרוסופט.

להרשמה והבטחת מקום חניה, יש להירשם ב- http://biug28.eventbrite.com/

אשמח לראותכם!

BI User Group Meeting #27 (25.01.2012)

Ronen Chenn — Tue, 24 Jan 2012 07:00:00 GMT

שלום לכולם,

ברצוננו להזמין אתכם למפגש קהילת ה - BI.

המפגש הקרוב יתקיים ביום רביעי - 25.01.2012 במשרדי מיקרוסופט רעננה, אולם דקל.

מצורף לו"ז לקראת המפגש.

להרשמה והבטחת מקום חניה, יש להירשם באתר : http://www.eventbrite.com/event/2824806071

לו"ז ל Bi User Group Meeting #27(25.01.2012)

17:30 התכנסות

18:00 Microsoft BI Ene2End Part 2 - starting to drill down.

יוסי אלקיים יועץ בכיר במיקרוסופט ישראל יציג בסדרת מפגשים את יכולות גרסת SQL2012 ואופן התאמתה לשוק הישראלי.

19:15 הפסקה

19:30 המשך

20:00 שאלות ותשובות

המרצה:

יוסי אלקיים- יועץ בכיר בחטיבת הייעוץ של מיקרוסופט ישראל. בעל ניסיון רב במערכות ה-BI של מיקרוסופט.

להרשמה והבטחת מקום חניה, יש להירשם באתר : http://www.eventbrite.com/event/2824806071

מידע נוסף בבלוגים שלנו:

רונן : http://blogs.microsoft.co.il/blogs/bilive/

איתי: http://blogs.microsoft.co.il/blogs/itaybraun/

נשמח לראותכם!

רונן חן ואיתי בראון

External users to connect the cube

Ronen Chenn — Wed, 28 Dec 2011 11:42:14 GMT

The demand for external users to connect the cube is being heard very often.

For example : Domain A users will connect to domain B cubes. After testing it with my colleges, this is the possibilities I have summarized:

1. using Threat Management Gateway (TMG) Server as a Reverse Proxy. We will also need a solid Kerberos implementation.
The process is described very well in this whitepaper; http://technet.microsoft.com/en-us/library/gg128954.aspx

2. We can use the SSAS access via http. The technique is described in this document: http://technet.microsoft.com/en-us/library/gg492140.aspx.

For any other ideas, Please comment here and elaborate !!!

Thanks.

Ronen

Upgrade Assistant Tool for SQL Server 2012

Ronen Chenn — Sat, 24 Dec 2011 06:46:00 GMT

new Upgrade Assistant for SQL Server 2012 (UAFS) has RTW’ed. UAFS is shipped as a free web-downloadable tool. It performs application compatibility testing and detects potential functional and performance issues that may impact a database upgrade from an earlier version of SQL Server (SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2) to SQL Server 2012. Comparing to the early version of Upgrade Assistant tool, UAFS significantly enhances replay scalability and performance by building on top of SQL Server 2012 Distributed Replay (D-Replay) feature, which is the first released application that demonstrates the value of D-Replay in a practical application. In addition, UAFS provide a user-friendly configuration interface as well as enhanced reporting and analysis features.

Report Highlights

· Statistic Info: Summary info in the report provides user an intuitive knowledge of the playback success rate and performance diff between Baseline Server & Test Server

· Enhanced filter capability: Filter & error category enable user to narrow down the playback resultset and improve analysis efficiency

· View events: Locate the same event sequence to allow user to do 1:1 comparison and analysis.

BI Group meeting (#26) - Wednesday, DEC 28th 2011 17:30

Ronen Chenn — Thu, 22 Dec 2011 04:50:00 GMT

שלום לכולם,

אני שמח להזמין אתכם למפגש קהילת ה - BI.

המפגש הקרוב יתקיים ביום רביעי - 28.12.2011 במשרדי מיקרוסופט רעננה, אולם דקל.

מצורף לו"ז לקראת המפגש.

לו"ז ל Bi User Group Meeting #26(28.12.2011)

17:30 התכנסות

18:00 הדור הבא ותפיסת הפתרון של הבינה העסקית של מיקרוסופט.

מטרת המצגת לקיים פנל לשאלות ותשובות בגישות המידע ותפיסות של בינה עסקית בשירות עצמי.

יוסי אלקיים יועץ בכיר במיקרוסופט ישראל יציג את מפת הדרכים של מיקרוסופט לגרסה הקרובה של SQLSERVER .

19:15 הפסקה

19:30 המשך

20:00 שאלות ותשובות

המרצה:

יוסי אלקיים- יועץ בכיר בחטיבת הייעוץ של מיקרוסופט ישראל. בעל ניסיון רב במערכות ה-BI של מיקרוסופט.

להרשמה והבטחת מקום חניה, יש להירשם באתר : http://biug26.eventbrite.com/

SQLbits2011- 29th - 31st March 2012, Novotel London West, London

Ronen Chenn — Sun, 11 Dec 2011 17:48:00 GMT

I have submitted a session for SQLBits.

Click to see, vote for it and come to meet me there!

What is impacting the dashboard from inside?

Ronen Chenn — Wed, 07 Dec 2011 16:27:00 GMT

Well, in order to understand what makes the dashboard work and how can we understand what the hell we see, I decided to take a flight course. Perhaps pilots understand it better.

So, no doubt that external “things” impact what you want to see or what you want to show.

Either way, at the first lesson, I understood that to understand dashboards, it take time and the biggest lesson is that you have to trust your self, your knowledge and your organization

BI Group meeting (#25) - Wednesday, Nov 30th 2011 17:30

Ronen Chenn — Wed, 23 Nov 2011 06:39:00 GMT

שלום לכולם,

ברצוננו להזמין אתכם למפגש קהילת ה - BI.

המפגש הקרוב יתקיים ב - 30.11 במשרדי מיקרוסופט רעננה, אולם דקל.

מצורף לו"ז לקראת המפגש.

לו"ז ל Bi User Group Meeting #25(30.11.2011)

17:30 התכנסות

18:00 איך לסיים פרויקט DWH איכותי ועוד לפני הזמן?
אילן זייתון מחברת Vision.BI יציג את תורת ה-ETL עם הרבה טיפים וטריקים מהשטח.

סיפור לקוח מי-עדן, שימוש בטכנולוגיות לפיתוח איכותי ומהיר.

19:15 הפסקה

19:30 שאלות ותשובות

19:45 חשיפה ראשונה - www.leganto.com.

שירות ענן ליצירת מערכות BI בארגון.

החל משכבת ה-ODS, טבלאות מחסן נתונים ותהליכי SSIS.

המרצה:

אילן זייתון- מנכ"ל משותף בחברת Vision.BI. בעל ניסיון רב במערכות ה-BI של מיקרוסופט.

נשמח לראותכם!

MDX vs. SQL

Ronen Chenn — Sun, 20 Nov 2011 15:32:17 GMT

A while ago I posted about SQL and MDX in separated posts.

This time I will walk you through MDX and SQL queries to illustrate the similarities and differences between the two query languages.

All the queries are against AdventureWorksDW.

I invite you to add comments to make this idea list, even bigger!

SQL		MDX
To retrieve a single value from a table, you need to use the Sum() function to add up the values of the selected measure for all rows and optionally assign it an alias as shown in this example. Here, Channel Sales is the view that you created in an earlier step.	SELECT Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales]	In MDX, measures are automatically pre-summarized, so there is no need for a Sum() function. However, you do need to identify the axis, and then the cube or perspective. The Channel Sales perspective has already been created within the standard Adventure Works database. Notice that the value returned is the same for both queries. In the MDX query, the All member of all other dimensions is not specified (unless a default member has been created in the cube). In SQL, when you do not specify a filter (using the WHERE clause), the query operates against the entire rowset.	SELECT [Reseller Order Quantity] on Columns FROM [Channel Sales]
In SQL, you can Group By items. When you do, you must put the item in both the SELECT clause and the GROUP BY clause.	SELECT [Country] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] GROUP BY [Country]	In MDX, the Group By is done in the cube because the cube already handles bucketing measures by dimension members. Instead of using a GROUP BY clause, you specify a set of members on an axis, such as rows. The query below has all members of the Country level of the Country dimension and places them on rows. Then the query retrieves values for each row and column intersection. The results returned are the same values, but they are returned in a different order because the cube automatically includes a sort defined in the UDM. Also, notice the SQL Group By is part of the data set and not a pre-defined row heading like you see in the MDX query results. There is no structure implied by the groupings in the SQL results; the values in the Country column are just like values in the Reseller Order Quantity column in that they make up a rowset of data returned from the queried view. In MDX, the group is part of the report structure. Values are retrieved logically one at a time from the cube.	SELECT [Reseller Order Quantity] on Columns , [Country].[Country].Members on Rows FROM [Channel Sales]
To sort by country, you need to add an ORDER BY clause to the query. Then the result set will be more like the MDX query results. In SQL, if you don’t explicitly sort, you get whatever order the data happens to arrive in. An ORDER BY is a separate clause and can be used only to sort rows.	SELECT [Country] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] GROUP BY [Country] ORDER BY [Country]	In the SQL query, you have to specify Country in three places in order to get the data returned in the rowset, to perform the grouping, and to get the ordering correct. In the MDX, you only reference Country once. This feature is particularly useful for time series, where the sort order is not the same as the alphabetical order of the displayed captions.
Rather than sort by name, you can alternatively sort by the sum of the order quantity in descending order in SQL. In SQL, sorting by any arbitrary value is handled the same as sorting the row values by a string value (like Country) —by using an ORDER BY clause.	SELECT [Country] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] GROUP BY [Country] ORDER BY Sum([Reseller Order Quantity]) DESC	In MDX, Order is a Set function which sorts the members before placing them on the axis. All the information needed for the sort is included in one Order function. In this example, the order quantity is retrieved twice. Once to get the result from the cube and store in cache, then again to retrieve the values from the cache and sort in descending order. Note that in both SQL and MDX it is possible to sort the rows by something other than what is displayed. For example, you could sort by Sales Amount but display Order Quantity.	SELECT [Reseller Order Quantity] on Columns , Order( [Country].[Country].Members , [Reseller Order Quantity] , DESC ) on Rows FROM [Channel Sales]
When filtering data values, WHERE clauses in both MDX and SQL have much the same effect. The way they get there is different. In SQL, the WHERE clause filters the incoming data rows, which are later grouped according to the aggregation function used, such as Sum().	SELECT [Country] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] WHERE [Calendar Year] = 2002 GROUP BY [Country] ORDER BY [Country]	In MDX, the WHERE clause is applied only when the data value for each cell is retrieved, which is after the axis sets were resolved and placed. In this example, the non-2002 data is eliminated at the end of the process of gathering values for each intersection, whereas SQL filters out any non-2002 data before summarizing values. Notice that you lose some countries in SQL where there is no 2002 data – Germany and Australia. In the MDX query, all countries are still included even if there is no 2002 data because they are defined as a set independently of the values returned from the cube.	SELECT [Reseller Order Quantity] on Columns , [Country].[Country].Members on Rows FROM [Channel Sales] WHERE [Date].[Calendar Year].&[2002]
		In MDX, the axis keyword NON EMPTY eliminates rows (or columns) that are completely filled with empty cells. Client tools often allow this setting as a user option, because users sometimes want to hide and sometimes want to show the empty rows. NON EMPTY is not a function, but is a keyword. Since the values in the column won’t be known until after you place the countries on the rows, the query must finish before any empty members can be removed.	SELECT [Reseller Order Quantity] on Columns , NON EMPTY [Country].[Country].Members on Rows FROM [Channel Sales] WHERE [Date].[Calendar Year].&[2002]
Rather than filter the rows of a query by values in the same column that is displayed, you sometimes want to filter by a related column. For example, when displaying states, you may want to filter by country. In SQL, there is no relationship between different columns. There is nothing in the query that indicates how these states are related to the country of Germany.	SELECT [State] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] WHERE [Country] = 'Germany' GROUP BY [State] ORDER BY [State]	In MDX, if you have defined a user hierarchy, you can use family relationships to filter members. The Children function shows clearly the relationship you intend between the country and the associated states.	SELECT [Reseller Order Quantity] on Columns , [Geography].[Germany].Children on Rows FROM [Channel Sales]
In SQL, the WHERE clause can affect what appears on the rows. If filtering away data from the source happens to filters away group-by values, the rows that would have contained those headings disappear. In the SQL query, the WHERE clause prefilters the row set to return only those states where the first letter is A. Then the query groups the results.	SELECT [State] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] WHERE [State] Like 'A%' GROUP BY [State] ORDER BY [State]	In MDX, you create arbitrary filters by changing the set on the axis. One way to do that is to use the Filter function. The filter is applied to each member in this set. There is no LIKE operator in MDX, but it may be included in the future. Meanwhile, you can get the same effect with the Filter function. In the MDX query, the full set of states is retrieved, then the members that aren’t part of the desired set are eliminated. Then the values associated with the remaining members are retrieved.	SELECT [Reseller Order Quantity] on Columns , Filter( [Geography].[Geography].[State- Province].Members , Left( [Geography].[Geography]. CurrentMember.Name , 1 ) = "A" -- 'Like operator' promised ) on Rows FROM [Channel Sales]
In SQL, you can filter rows by explicit items—still in the WHERE clause. One way to do that is by listing items in an IN clause. In SQL, the WHERE clause still prefilters the row set to return only the named countries, and then groups the results.	SELECT [Country] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] WHERE [Country] IN ('Australia','France') GROUP BY [Country] ORDER BY [Country]	In MDX, you can create sets of explicit members. To do that, enclose the list of members in braces { }. The set of explicit members still goes on the axis. Use the ampersand (&) with a key value, but not the member name. In this example, the member key and member name are the same.	SELECT [Reseller Order Quantity] on Columns , { [Geography].&[Australia] , [Geography].&[France] } on Rows FROM [Channel Sales]
In SQL, in order to select the top few groups, you have to add code in separate places – the TOP n portion goes in the SELECT clause, and the ORDER BY is a separate clause. This means that you can use it only on the rows axis.	SELECT TOP 3 [Country] , Sum([Reseller Order Quantity]) AS [Reseller Order Quantity] FROM [Channel Sales] GROUP BY [Country] ORDER BY Sum([Reseller Order Quantity]) DESC	In MDX, TopCount is a set function that encapsulates the entire operation. It is common in MDX to have a single function that represents multiple operations. The TopCount function is equivalent to using the MDX Order function to sort the members in descending order (ignoring the hierarchies), and then using the Head function to extract the first n members. In the SQL query, you have to do the ORDER BY to define the sort order of rows that are to be returned by the query, then the GROUP BY to aggregate the rows, and then finally use TOP 3 to limit the result set to 3 rows. In the MDX query, you do all of these actions with one set function – TopCount. As function arguments, you tell it which members to consider, how many to return, and how the results should be sorted when determining which members are at the top.	SELECT [Reseller Order Quantity] on Columns , TopCount( Country.Country.Members , 3 , [Reseller Order Quantity] ) on Rows FROM [Channel Sales]

PowerPivot Change the file Max Size

Ronen Chenn — Sun, 20 Nov 2011 14:03:37 GMT

The default file size of PowerPivot file is 200M.

you can change it in the trusted file location:

For that, you need to login to the central administration site-> Excel Services Application->Edit Trusted File Location.

change the the maximum file size to 2000 –> 2G.

Replace the Sum(…) prefix in PowerPivot

Ronen Chenn — Sun, 20 Nov 2011 13:04:26 GMT

Problem:

when playing with PowerPivot and adding ∑Values you get the prefix Sum(…) before the value.

Solution:

1.Click on the dropdown combo on the right hand.

2. Click on Edit Measure.

3.Copy the Formula in the “Measure will use this formula” …

4.Click on the New Measure icon in the Power Pivot Ribbon

5.paste the formula to the measure settings and change the measure name.

6. That’s it. now you have a new calculated member with your own caption ( Alias or what ever)

Enjoy it!!!

Microsoft® SQL Server® 2012 Release Candidate 0 (RC0)

Ronen Chenn — Thu, 17 Nov 2011 16:23:31 GMT

What is New?

Deliver required uptime and data protection with AlwaysOn
Gain breakthrough & predictable performance with ColumnStore Index
Help enable security and compliance with new User-defined Roles and Default Schema for Groups
Enable rapid data discovery for deeper insights across the organization with ColumnStore Index
Ensure more credible, consistent data with SSIS improvements, a Master Data Services add-in for Excel, and new Data Quality Services
Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Data Tools for a unified dev experience across database, BI, and cloud functions

very important!! Pyramid Analytics ( www.PyramidAnalytics.com ) has completed the competency against Sql 2012 Cubes, and Tabulator Model.

So, Now PA can connect them safe and sound.

download link to the RC0 Page : http://www.microsoft.com/download/en/details.aspx?id=28145

Consume PowerPivot Data from Pyramid analytics

Ronen Chenn — Sun, 30 Oct 2011 12:31:00 GMT

Pyramid Analytics can consume PowerPivot Data.

It can happen with several options:

1. Directly from the XLS file : ex: http://spstst01/PowerPivot%20Gallery/pyramidtest.xlsx Syntax= http://{ServerName/DocumantLibrary/FileName.XLSX)

2. Connect to the SSAS PowerPivot instance.

Both of the option works great.

Choose one and start today!

Enjoy it!

Pyramid Analytics Launch event in Israel 1/11/11

Ronen Chenn — Tue, 18 Oct 2011 13:24:00 GMT

אירוע ההשקה יתקיים ב 1.11.11

BI User Group meeting 26/10/2011 (#24)

Ronen Chenn — Tue, 18 Oct 2011 13:13:00 GMT

שלום לכולם!

אנו שמחים להזמינכם למפגש קהילת ה-BI שיתקיים ביום רביעי 26.10.2011 בשעה 17:30 במיקרוסופט רעננה.

ברחוב הפנינה 2, קומה 0.

בתוכנית הפעם -> Get the best out of sql 2008 R2 with MDX and Performance Extreme

17:30 - 18:00 התכנסות

18:00 - 19:00 (Part 1)

* The top 10 rules to design a best practice Analysis Services solution.

* New insights about improving performance issues within Analysis Services 2008R2.

* How to avoid the negative performance impact of referencing named sets inside calculated members.

19:00 - 19:15 הפסקה וכיבוד קל

19:15 - 20:15 (Part 2)

* Continue.

20:15 - 20:45 . and Job Reference Q&A

המרצה: מר איתי בראון, טווינגו.

להרשמה: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032497980&Culture=he-IL

מידע נוסף בבלוגים שלנו:

רונן : http://blogs.microsoft.co.il/blogs/bilive/

איתי : http://blogs.microsoft.co.il/blogs/itaybraun/

נשמח לראותכם,

איתי בראון ורונן חן