August 2011 - Posts
Download the sample file
You
can find the sample code here.
Download the file and extract the folder AdventureWorks DW PowerPivot CTP3
Creating a Tabular Database from XSLX (PowerPivot) File
SSAS
"Denali" allows building BI solutions quickly with minimum coding.
One of the ways to implement that is creating an Excel 2010 PowerPivot file and
importing it to the SSAS (in Tabular Mode). The SSAS Server creates a database
automatically.
More
information about PowerPivot, including demos can be found at www.powerpivot.com. This short video PowerPivot Retail Sales
Analysis Example might be useful too.
Let's
see how it works.
Normal
0
false
false
false
EN-US
X-NONE
HE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:Arial;
mso-bidi-theme-font:minor-bidi;}
Name
the new Database
Normal
0
false
false
false
EN-US
X-NONE
HE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:Arial;
mso-bidi-theme-font:minor-bidi;}
Right
click on the new database (refresh the db list if necessary) , select Browse.
This
is it. The db is ready for analysis.
Add a new
calculated Member
Users
can easily add Calculated Members
Normal
0
false
false
false
EN-US
X-NONE
HE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:Arial;
mso-bidi-theme-font:minor-bidi;}
Please
notice this is a temporary Calculated Member which doesn't saved
anywhere. To create a new measure you'll have to add it to model using Visual
Studio.
Enjoy
Itay Braun
itay@twingo.co.il
www.twingo.co.il
שלום לכולם
בחודש אוגוסט לא יתקיים מפגש של ה BI User Group וזאת לבקשת רבים אשר בחופש. גם קבוצות משתתפים אחרות בטלו את המפגש באוגוסט.
בחודש ספטמבר לצערנו גם לא יתקיים מפגש בגלל שיום רביעי האחרון בחודש (24.9) הוא למעשה ראש השנה.
ולכן המפגש הבא יתקיים ביום רביעי 26.10.
מה מתוכנן במפגש הבא:
Itay Braun, CTO, Twingo:
What's new in Analysis Services "Denali" - first look on the new tabular mode and when to use it.
אם מישהו נוסף רוצה להרצות על נושא, קטן או גדול, בשמחה. נא לפנות בהקדם אל רונן או איתי.
בברכה,
רונן חן ואיתי בראון.
The SQL Server code name 'Denali' Community Technical Preview 3 (CTP3) Product Guide download contains the latest datasheets, white papers, click-through and auto-running demonstrations, hands-on lab previews, technical presentations, and other useful links to help you evaluate the SQL Server code name 'Denali' CTP3 release. The Product Guide organizes the content for easier viewing.
14 Product Datasheets-
8 PowerPoint Presentations
5 Technical White Papers
13 Hands-On Lab Preview Documents- I played with that today. No labs yet, much to wait for, though.
6 Click-Through Demonstrations-
13 Self-Running Demonstrations-
26 Links to On-Line References-
44 Links to On-Line Videos including
26 Presentations from North America TechEd 2011
Enjoy
Itay Braun, CTO, Twingo
itay@twingo.co.il
http://www.twingo.co.il/
Installation:
The installation is very similar to the SQL Server 2008 R2. However, while installing SSAS you should choose either the good old SSAS or the new Tabular Mode. BTW, there is another new configuration window regarding Distributed Replay Clients. About this subject in another post. Meanwhile you can read a little about it here: http://msdn.microsoft.com/en-us/library/ff878183(v=sql.110).aspx
Installing the sample data
You can download sample Tabular database from codeplex.
The new db should look like this:
Installing PowerPivot for Denali CTP
The PowerPivot is used to create the Tabular data which later be uploaded to the SSAS Server. There are other ways to create a tabular model; this is the simplest one. I'm working on another post to demonstrate creating a Tabular Mode Database based on AdventureWorksDW2008.
If you download the PowerPivot add-in from http://www.powerpivot.com/, you might get an error.
Go to this great post to solve the problem: http://blogs.msdn.com/b/oneclickbi/archive/2011/07/22/installing-and-using-powerpivot-for-excel-in-denali-ctp.aspx .
Browsing the “cube”
As you can see there are no cubes. Browsing the data is done by right clicking on the database. Tip: if you can’t see the dimensions, click twice on the Design Mode button.
As you can see, even if the dimension has a hierarchy, the Management Studio Browser flattens the information, exactly as the SSRS 2008 Wizard for SSAS Cubes. For ex. I could not place the Calendar attribute on the "Columns" axis. Click on the Excel icon to open the database using Excel 2010 (regular Excel, no PowerPivot is needed). I guess Microsoft is telling us to use this as a simple browsing tool instead of Management Studio.
Right Click on the Database, New MDX Query. The MDX function works well (so far so good). I also checked some hierarchy oriented functions such as Parent and Children for the hierarchies in the database.
There are few new functions which worth another post too.