Business Intelligence,Technology, Thoughts, Thinking

Time Dimension Default member ( ssas 2012)

Ronen Chenn — Sat, 19 Oct 2013 15:57:00 GMT

SSAS allow you to define a default member for each attribute in each dimension:

The most poplar dimension is the TIME dimension...

To define the default last non empty date, Click on the dimension, double click on the attribute in which you want to defint the defaulte value:

look for the Default value property, click on Custom MDX, Type the following into the MDX expression box, using the MDX Builder features as you find them helpful:

Tail(Filter([Time].[Month].Members,
NOT IsEmpty(Time.CurrentMember)),1).Item(0)

An explanation of the components of the above expression appears in the following table:

Expression	Meaning
[Time].[Month].Members,	Selecting all months ...
Filter([Time].[Month].Members, NOT IsEmpty	Filter out those months that are "not unpopulated", and ...
Filter([Time].[Month].Members, NOT IsEmpty(Time.CurrentMember))	Out of the filtered subset, select theCurrent Member
Tail(Filter([Time].[Month].Members, NOT IsEmpty(Time.CurrentMember)),1)	Wrap the expression in a Tail() function to return the "last" (most recent in time) member of the returned subset
Tail(Filter([Time].[Month].Members, NOT IsEmpty( Time.CurrentMember)),1).Item(0)	Convert the subset resulting from theTail() function to a member with the.Item() function, which means the first (as indicated by the zero-based index) member in the Month level (the members of the subset) of the Time dimension.

Update 30/10/2013

Tail(EXCEPT( Filter([DIM TIME].[Date].Members,NOT IsEmpty(Time.CurrentMember)), { [DIM TIME].[Date].UNKNOWNMEMBER } ),1).Item(0)

If you have an UknownMember, It will exclude that member.

Update or delete in small packet size to by pass log limmitation

Ronen Chenn — Fri, 04 Oct 2013 10:44:00 GMT

DECLARE @RowsDeleted INTEGER SET @RowsDeleted = 1 WHILE (@RowsDeleted > 0) BEGIN UPDATE top (10000) {TABLENAME} SET {COLUMN}= XXXX...XXXX SET @RowsDeleted = @@ROWCOUNT END

Activity viewer for SQL SSAS 2012

Ronen Chenn — Sat, 28 Sep 2013 04:02:00 GMT

New version of Activity viewer for SQL SSAS 2012 is ready:

https://activityviewer2012.codeplex.com/releases/view/112680

SQL Server 2012 SP1 Cumulative Update 6 Released (Build 11.0.3381.0)

Ronen Chenn — Wed, 18 Sep 2013 05:30:00 GMT

cumulative update release for SQL Server 2012 SP1 is now available for download at the Microsoft Support site. Cumulative Update 6 contains all the SQL Server 2012 SP1 hotfixes which have been available since the initial release of SQL Server 2012 SP1.

To learn more about the release or servicing model, please visit:

CU#6 KB Article: http://support.microsoft.com/kb/2874879
Understanding IncrementalServicingModel for SQL Server
SQL Server Support Information: http://support.microsoft.com/ph/2855

Kerberos Configuration Manager for SQL Server

Ronen Chenn — Mon, 15 Jul 2013 16:13:00 GMT

Gather information on OS and Microsoft SQL Server instances installed on a server.
Report on all SPN and delegation configurations on the server.
Identify potential problems in SPNs and delegations.
Fix potential SPN problems.

It can be download here:

Break distinct count measures into separate measure groups

Ronen Chenn — Wed, 10 Jul 2013 07:18:00 GMT

The easier method to to this if you already have the measure group built is:

Right click on the cube on the Cube Structure tab and select new measure.

Then select DistinctCount as the usage, then your measure to count and it will automatically be created in a new Measure Group.

Optimized Bulk Loading of Data into Oracle

Ronen Chenn — Fri, 21 Jun 2013 05:57:00 GMT

SQL Server 2008 and SQL Server 2008 R2 (Enterprise and Developer editions) support bulk loading Oracle data using Integration Services packages with the Microsoft Connector for Oracle by Attunity. For SQL Server 2005 and the non-Enterprise and non-Developer editions of SQL Server 2008 and 2008 R2, there are alternatives for achieving optimal performance when loading Oracle data. This paper discusses these alternatives.

The Enterprise and Developer editions of SQL Server 2012 also support bulk loading Oracle data using Integration Services packages with the Microsoft Connector for Oracle by Attunity.

To review the document, please download the Optimized Bulk Loading of Data into Oracle Word document.

CDC for Oracle in SQL Server 2012

Ronen Chenn — Fri, 21 Jun 2013 05:55:00 GMT

One of the new features introduced in SQL Server 2012 is the ability to use SQL Server CDC for tracking changes on an Oracle table. In the following post you will understand how to setup such a service for tracking changes on your Oracle tables.

Configuring your Oracle server

Before creating the service, you must ensure that the following configuration steps have been performed on your source Oracle database:

1. The database is set to ARCHIVELOG and is OPEN

2. The user that is going to connect to the Oracle database has DBA privileges on the database (since you will look up the log miner data from the database)

You should also install the Oracle client and configure the service names on the machine where you will be installing the CDC services on.

Installing the CDC Service Configuration and Designer MMCs

The MMCs are not installed by default when you install your SQL Server instance. You must install them separately by installing them using their MSIs.

The MSI installer for CDC Service Configuration MMC is named AttunityOracleCdcService.msi and the installer for CDC Designer is named AttunityOracleCdcDesigner.msi. They can be found in your installation media under the folder\Tools\AttunityCDCOracle. In the folder you would find x64 or x86 folders which correspond to the different processor architectures. Within the x86 or x64 folder you would find folders for various locales including 1033 which corresponds to English (US).

Thus, to install the English (US) x64 version you must go to \Tools\AttunityCDCOracle\x64\1033 folder.

Once installed, you would find a shortcut created under the start menu at Start>Program>Attunity  Change Data Capture for Oracle>CDC Service Configuration

Note You must install the same bit version of the MSI as the Oracle client installation you have on the local system. Thus, if you have installed the x86 version of Oracle client then you must also install x86 version of the Oracle CDC service.

Setting up your service

You can set up a new CDC Service using the CDC Service configuration MMC. But before you create a new service, you will have to prepare a local SQL Server instance. You can do so by using the actions pane in the MMC as shown below.

Once the local SQL server is prepared you can use the New Service action to create a new CDC service as shown below.

Couple of things to note here:

1. The service account you use should have logon as service policy assigned to it

2. The master password will be used to create a asymmetric key which will be used to encrypt the Oracle credentials which will be provided later

Once the service is created you can see it under the services node.

Creating a CDC instance

You can create a CDC instance using the CDC designer configuration MMC which you installed earlier. A CDC instance is defined as an object consisting of:

· The source Oracle database connection information

· Credentials and properties to connect to the source Oracle databases

· Source tables for which you need to capture CDC. You can even configure the instance to capture only a subset of the columns of each table.

· Mirror SQL Server database name which will contain the mirror tables for the source tables/columns defined in the previous step

You can create the instance as shown in the following steps

1. First you should connect to the SQL Server instance on which you created the service

2. Now you can see all the services available for the instance

3. Now you can create a new instance using the right click context menu for a service

4. The Oracle CDC Instance wizard opens up and the first pane allows you to define a service name. It will also ask you the name for a mirror database that would be created on the same SQL Server instance as step 1. You can rename the mirror database as well.

5. The next steps will ask you to input the Oracle connection string as well as the user credentials for connecting to the Oracle database. Note that it takes the Oracle connection string in the following format: host[:port][/service name]

Note that you should have installed the Oracle client and setup the service naming before this step.

6. Now you can select the tables you want to include in your CDC instance.

7. You can also edit the instance and select specific columns as well as specify a CDC role to be used when creating the CDC instance on the local SQL server database

8. In the next steps you would asked to run a script on your source Oracle database which essentially sets up Supplemental logging

9. You can click through the wizard to Generate the Oracle CDC instance

Operating a CDC instance

Once you have created the CDC instance you can now start and monitor the CDC instance using the designer MMC. By default the Service would be in INITIAL (stopped) state. You can start it as shown in the figure below.

The service will be started and the status would inform you of the current state of the service, transactions’ SCN for Oracle and the number of transactions being processed at that instance as shown below.

You would notice that a database would be created on the SQL Server instance you specified and the mirrored table would be setup. CDC would have also been enabled on the table and a corresponding _CT table would have been created capturing the changes from the mirrored table as shown below.

This post has been an introduction on how to get up and running with the new CDC for Oracle features introduced in SQL Server 2012.

SSAS distinct count wrong results

Ronen Chenn — Fri, 14 Jun 2013 20:38:00 GMT

I have added a connect item today.

please vote for it:

https://connect.microsoft.com/SQLServer/feedback/details/790216/ssas-distinct-count-wrong-results

Issue:

ssas distinct count results wrong number.
While querying the table as follow:
SELECT count (distinct USER_ID ) FROM Table
I get this results -- 1033612

while querying SSAS I get 257.

sample data for USER_ID:
42348-3648-1368306010878-D41D8CD98F00B204E9800998ECF8427E
42348-3648-1368074987484-B26463A0EE1A5F27718A14533D020E60
42348-3648-1368011144064-65DD703A82FFA13A98305295317C86AD
42348-3648-1368049679418-D41D8CD98F00B204E9800998ECF8427E

I have also tried to convert this Varchar(100) into checksum()
sample data -235875173
535581043
287814888
-1894147417

still getting same results.

the measure is located in stand alone measure group. and configured with default settings.

Solution:

No Solution so far.

מרקו רוסו , המומחה מספר 1 בעולם לתחום ,SSAS

Ronen Chenn — Sun, 09 Jun 2013 14:05:00 GMT

7/7/13 מרקו רוסו , המומחה מספר 1 בעולם לתחום ,SSAS מגיע לישראל!

מספר מקומות מוגבל ל-15 משתתפים בלבד. מהרו להרשם!!!

"GeoFlow" for Excel 2013

Ronen Chenn — Fri, 07 Jun 2013 12:11:00 GMT

Project codename “GeoFlow” Preview for Excel 2013 allows you to plot geographic and temporal data visually, analyze that data in 3D, and create interactive tours to share with others. This preview gives you an early look into the new features that provide 3D data visualization for Excel and a powerful method for people to look at information in new ways, enabling discoveries in data that might never be seen in traditional 2D tables and charts.

download it from here->

Cumulative update package 4 for SQL Server 2012 SP1

Ronen Chenn — Sun, 02 Jun 2013 15:18:00 GMT

This post describes cumulative update package 4 for Microsoft SQL Server 2012 Service Pack 1 (SP1). This update contains hotfixes for issues that were fixed after the release of SQL Server 2012 SP1.

Note The build number of the cumulative update package is 11.0.3368.0.

download here:

מפגש BI # 43

Ronen Chenn — Sun, 26 May 2013 17:19:00 GMT

שלום לכולם,

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

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

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

לו"ז ל Bi User Group Meeting #43(29.5.2013)

להרשמה: http://biug43.eventbrite.com/#

17:30 התכנסות

18:00 Hadoop and BI: a match made in heaven?

Hadoop is the stellar Big Data software framework these days. In the session we will provide context to the rise and rise of this platform, explore various vendors' Hadoop offerings, specifically relating to the DWH and BI ecosystem, and try to understand how relevant Hadoop really is for the BI community.

19:30 הפסקה

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

20:30 סיום

המרצה:

יניב מור, מנכ"ל חברת XPLENTY, עוסק בתחום ה – DATA וה – BI כ – 13 שנים.

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

להרשמה: http://biug43.eventbrite.com/#

Cumulative update package 12 for SQL Server 2008 R2 SP1

Ronen Chenn — Tue, 16 Apr 2013 20:58:10 GMT

This article describes cumulative update package 12 for Microsoft SQL Server 2008 R2 Service Pack 1 (SP1). This update contains hotfixes for issues that were fixed after the release of SQL Server 2008 R2 SP1.
Note The build number of the cumulative update package is 10.50.2874.0.

For more info:

http://support.microsoft.com/kb/2828727/en-us

SQL Server Business Intelligence in Windows Azure Virtual Machines

Ronen Chenn — Tue, 16 Apr 2013 20:57:06 GMT

The Windows Azure Virtual Machine gallery includes images that contain SQL Server installations. The SQL Server editions supported in the gallery images are the same installation files you can install to on-premises computers and virtual machines. This topic summarizes the SQL Server Business Intelligence (BI) Features installed on the images and configuration steps required after a virtual machine is provisioned. This topic also describes supported deployment topologies for BI features and best practices.

For more info:

http://msdn.microsoft.com/en-us/library/jj992719.aspx