ItayBraun

Big Brother - What kind of data is your cell phone company collecting?

2012-08-02T19:19:00Z

I read a very interesting book called "The Numerati" by Stephan Baker (in Hebrew "אשפי המספרים"). The book describes some of the real-world implementation of
Big Data. I enjoyed reading it and found it very interesting.

Link to Amazon: http://www.amazon.com/The-Numerati-Stephen-Baker/dp/0618784608
Link to the blog: http://thenumerati.net/

I also saw recently a very interesting lecture at TED about the information collected by the cellular company and how much can they tell
about you (and me) just because they know where exactly you are every day and
night. Where is your home, how long you travel to work, how often you go to
movies and how much time you spend at home / pubs / shopping malls.... How many days you were abroad and in which country. etc. etc.

The video: http://www.ted.com/talks/malte_spitz_your_phone_company_is_watching.html . This is the story of a German guy who got 34,000 rows from the cellular
company, telling him what he did in the last 6 month. He analyzed it, published the results and was invited to talk in TED.

What can be done about it? Not much. The data is stored by those
companies for a reason. However, after short time they should delete it
(usually they don't) and not analyze it (unless the police ask you too).

The BI Architect in me says: this is amazing! I can run crazy queries on this data and enrich it easily to get a very precise insights
about one's life. The civilian in me (which is about 99%) says: this is bad! They must delete this info immediately.

Will they? I seriously doubt it.

Will the government step in and do something about it?

(Rhetoric question).

Next BI User Group - Aug 29th - BI using Office 2013

2012-08-01T16:12:00Z

Hi all I hope you're enjoying the summer. Those of you who are in Israel on Aug 29th (the last Wed of the month, as usual) are welcome to join us at another very interesting BI User Group Session. This time Ronen will talk about Excel 2013, focusing on its new BI capabilities and how can you do more with Excel. Please register here: http://biug32.eventbrite.com/ See you all Itay

Breakthrough Insights using Microsoft SQL Server 2012 - Analysis Services, a new Free Training at Microsoft Virtual Academy

2012-04-18T18:14:00Z

What are you waiting for?

http://www.microsoftvirtualacademy.com/tracks/breakthrough-insights-using-microsoft-sql-server-2012-analysis-services

MicroStrategy Installation

2012-04-10T01:38:00Z

I know this is a blog mainly about MS products. But recently I had to use MicroStrategy ( http://www.microstrategy.com/ ) at some of my customers. Apparently MicroStrategy offers full BI platform with many impressive capabilities.

The way I see it, Microsoft and MicroStrategy don't really compete. I love the SQL Server Relational engine, SSIS for ETL, SSAS or 2012 Tabular and Reporting Services. But, when a customer wants to query the SQL Server directly, well, in this case sometimes the SSRS is just not good enough.

Unfortunately, even the new PowerVIew is not a good answer. It requires installation of SQL 2012 and many customers are just not ready to implement this new technology. It also requires SharePoint Enterprise Edition (or SQL 2012 BI Server) and can only query Tabular Mode. Sorry, too many prerequisites for a simple answer - MicroStrategy.

Just to make things clear, I still believe an SSAS cube provides great functionality and performances and a better choice.

So, you have decided to download absolutely FREE MicroStrategy for up to 100 users from here: http://www.microstrategy.com/freereportingsoftware/index.asp

(100 FREE users? What is the catch, you ask. There is none. You are only limited to use one CPU by the MicroStrategy Server. Good bargain I say)

You download the installation file (about 3 GB. As I said, it is a full platform with many capabilities).

They'll send you a key by mail. You'll need it when installation begins.

Should be installed on Windows Servers only. Works on Win 7 but not supported on this OS.

Prerequisites when installing everything:

(all configured from Control Panel, Programs, Turn Windows Features on and off)

IIS 6 or above
ASP
ASP.NET
Internet Information Services\World Wide Web Services\Security\Windows Authentication
Internet Information Services\World Wide Web Services\Application Development Features\ASP

If you forgot to enable any of these, you'll have to stop the installation and start over.

New SQL Server 2012 Exams:

2012-04-09T19:24:00Z

Exam 71-461: Querying Microsoft SQL Server 2012
Exam 71-462: Administering Microsoft SQL Server 2012 Databases
Exam 71-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
Exam 71-464: Developing Microsoft SQL Server 2012 Databases
Exam 71-465: Designing Database Solutions for Microsoft SQL Server 2012
Exam 71-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
Exam 71-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

For information about the certification paths, visit the SQL Server certification page.

Count dimension members using SSAS Dynamic Management Views (DMV)

2012-04-03T01:58:00Z

As you know, while running a query which counts dimension members the Management Studio do not return how many rows were returned. (for ex. select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Red’ )

To solve this problem you can write a query like this:

WIth MEMBER a

([Dim name].[Attribute].members).count

SELECT a on 0

from [$dim name]

===========================================

WITH MEMBER a

([Dates].[Date ID].members).count

SELECT a on 0

from [$Dates]

My Presentation at SQLBits X

2012-04-03T00:04:00Z

Last weekend I was in London at SQLBits X. I lectured about SSAS 2012 Tabular Mode.

Tabular models are in-memory databases in Analysis Services which delivers fast access to tabular model objects and data by reporting client applications.
In this session we'll understand the best practices of using this new technology. What exactly is it good for? When to use it and when NOT to? Does it replaces the good old MOLAP or should only be used on special scenarios?
How to migrate my MOLAP cubes and applications to Tabular Mode?
Security, administration other best practices if time permits.
The session is based on SSAS 2012 PoC at some large DB

Here you can download the PPT.

Feel free to contact me about SSAS (Multi Dimensional or Tabular)

itay@twingo.co.il

See you all in SQL Bits XI

Itay

Reading XML Configuration Files using TSQL

2012-02-24T05:23:00Z

Sometimes the result of a query depends on a parameter in a configuration file. Today, for example, I needed to write an SSRS report with a URL to another IIS. The dynamically generated URL should look like this http://devserver/folder1?CampainID=123 or http://prodserver/folder2?CampaignID=123 . The destination server name (DevServer or ProdServer) saved in an XML configuration file.

The XML file:

<?xml version="1.0" ?>

<Name>devserver</Name>

</Servers>

Obviously I could write .NET code in the RDL to retrieve data from the XML file. However, I used a simpler option using TSQL:

SELECT XMLSource.value('(Servers/Name)[1]', 'VARCHAR(50)') as servername

FROM (

SELECT bulkColumn,

cast(bulkcolumn as XML) as XMLSource

FROM OPENROWSET(BULK 'D:\ServerConfig2.xml',SINGLE_BLOB)

AS servers

) as T1

SSAS DMV - Getting the MDX of Calculated Members

2012-02-12T02:59:00Z

While Analyzing cube (performances or data accuracy) I run many MDX queries. It is important to know whether the Measures in the query are calculated and if so, what is the MDX expression of the calculated measure.

Option one - open Management Studio (import the cube schema, if you can't find the source code) and look at the Calculation Tab.

A simpler option: run the following query

SELECT Measure_name, expression, Measuregroup_name, *
FROM $system.mdschema_measures
--where Measure_name = 'Growth in Customer Base'
ORDER BY Measure_name

The SQL 2012 BOL now contains valuable information about DMV. Finally.

http://msdn.microsoft.com/en-us/library/hh230820(v=sql.110).aspx

MDX - Handling Non Unique Member Names

2012-02-05T02:47:00Z

The Problem: I needed to write a query using non unique member name. For ex. there are few cities called "Berlin". While using the name only one record retured.

Next BIUG - Feb 29th - Next Generation BI. Special Meeting with the SharePoint and SQL User Groups

2012-01-30T04:56:00Z

Group מארח את SharePoint User Groups ו - SQL למפגש מיוחד בנושא:

בינה עסקית - הדור הבא

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

מרצים: ערן שגיא ויוסי אלקיים, קבוצת היועצים (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).

מקווים לראותכם!

BIUG - The PowerPoint Slide Decks

2012-01-30T04:07:00Z

Here you can find the Slide Deck (PDF actually ) used by Yossi Elkayam.

https://skydrive.live.com/?cid=451de325453aee9f&id=451DE325453AEE9F%21235

BI User Group

2012-01-25T16:22:00Z

שלום לכולם,

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

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

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

לו"ז ל 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

BIUG #26 - Wed, December 28th

2011-12-22T14:30:00Z

שלום לכולם,

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

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

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

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

17:30 התכנסות

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

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

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

19:15 הפסקה

19:30 המשך

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

להרשמה:

http://www.eventbrite.com/event/2674911733

Small Tip - GROUP BY ALL

2011-11-23T16:37:00Z

As you know, in many BI reports, what's missing is sometimes more interesting than what in the report. Therefore, in many cases I need to use OUTER JOINS to retrieve all data from the "dimension" table, even if there is no matching row.

I noticed not many are familiar with GROUP BY ALL which might be helpful.

As you can see in this simple example, the product 344 has only one color (silver). But I want to see all other colors in the table (red, yellow ...).

SELECT P.Color, sum(unitprice) as SumUnitPrice

FROM dbo.FactInternetSales as F

JOIN dbo.DimProduct as P

ON P.ProductKey = F.ProductKey

WHERE P.ProductKey = 353

GROUP BY ALL P.Color